MySQL efficient query for large table statistics

Question

The situation I am working on a site that records views of a user profile with that data being used in various custom ways later using only SELECT statements.

If I have a table profile_views with the fields view_id, view_date, and profile_id, I propose to have the view_id as INT(25) with auto increment to allow for a large number of IDs. The profile_id corresponds with profile_id from a table profiles which contains general fields of the user's profile.

A basic select statement would be "SELECT v.view_id FROM profile_views v WHERE v.profile_id='125'"

I need the speed for the SELECT statements even if the table gets into 100s of thousands of rows.

My questions are: -> Is it wise to add an index to profile_id as well? Does that help speed up the 'where' clause? -> If I would like to have details of a number of profiles on one page, should I index profile_id and create a foreign key constraint to the table profiles.profile_id

(using mySQL 5.* INNODB)

Thanks for any suggestions! Not sure what is best for scalability (or perhaps I am approaching it all wrong).

Answer 1

Short answer: Yes, adding an index to any field you intend to do regular searches on will improve select performance.

But on a side note beware that adding to many indexes can affect SELECT performance as well as the engine will have to figure out which combination of indexes are the most suitable for your query (this usually only becomes a real problem with a ridiculous amount of indexes).

Foreign keys are always a good way to keep your database clean but will slightly reduce the insert / update performance. There are several threads about this pros and cons about foreign keys checks already.

If you intend to regularly show the number of times a profiles has been viewed it can, for large scale applications, be better to use a counter in the profiles table. Frequent use of group by statements (such as count(*)) on large datasets can easily bring a db to its' knees.

For index optimization the EXPLAIN operators is your best friend. http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

Answer 2

You can add covering index for the best performance:

SELECT v.view_id 
FROM profile_views v 
WHERE v.profile_id='125';

e.g. for above query you can add covering index KEY(profile_id, view_id)

have a look here