When to create indexes on custom tables?

[Antivirus]

I have a few sites that use a lot of custom pages, scripts, and data. There's lots of new tables added. Obviously each table has a primary key (usually the id column) but how do i know when I should create an index on other columns? I have also seen (looking at vbulletin's default tables) that some indexes are for multiple columns. What's the difference? Are there any guidelines to follow for determining when to create a new index and what type (how many columns to include) to create?

[Eikinskjaldi]

Originally Posted by Antivirus

I have a few sites that use a lot of custom pages, scripts, and data. There's lots of new tables added. Obviously each table has a primary key (usually the id column) but how do i know when I should create an index on other columns? I have also seen (looking at vbulletin's default tables) that some indexes are for multiple columns. What's the difference? Are there any guidelines to follow for determining when to create a new index and what type (how many columns to include) to create?

Any columns used in a where, join or sort or having or just about any clause should be indexed. Indexing on multiple columns can save space but requires all columns in the index be used.

The single best way to determine which should be indexed is to profile your sql with explain, which is easily done by just putting the keyword expain in front of select.

For example if you have the query

select f1 from table1 join table 2 using (f2) where f2 = 'foo' and f3 > 40

then run

explain select f1 from table1 join table 2 using (f2) where f2 = 'foo' and f3 > 40

What they output of explain does is best looked up in the mysql manual, but basically you want every row to be using an index and you rarely want an all table scan.

[Antivirus]

thanks, that's useful info i will see how it works out. I suyppose if i am already running the query in vbulletin, if debug mode is enables, i can just click on the (explain) thingie and will give me same info, correct?

[Adrian Schneider]

Using EXPLAIN really helps.

But a really simple thing to remember is if you are using a column primarily in joins / where clauses, such as a 'userid' column in another table, then it needs one. Well, doesn't need - but it will speed up the query significantly. Just like in a book, finding something using an index is FAR faster than reading every page looking for that word.

SELECT * FROM jokes WHERE userid = 1;

SELECT *
FROM jokes 
LEFT JOIN users on (jokes.userid = users.userid);

Both of them are essentially using the 'userid' column to narrow down the results, so it is much faster if it can do so using the index (and not actually go into the data, which is slower).

As eik... said, the same thing goes for sorting. If you are often sorting by title (select * from jokes order by title asc) then you may want to consider indexing it. This is assuming the table has several other columns. The larger an index becomes, the less useful it is (relative to actual row size).

If you have some time, watch this MySQL tuning vid,
http://video.google.com/videoplay?do...24540025172110

I remember it being quite helpful when I watched it last spring.

Edit,

Yes clicking on the Explain link will show you the EXPLAIN info for each query.

[Antivirus]

thanks - very interesting video. A bit long (40 mins) but really starts getting interesting and focusing on indexing about 9-10 minutes in. Learned a lot from it, gonna watch it a few more times to allow some stuff to sink in better as well.

[Adrian Schneider]

No problem.

I really wish they would post more stuff like that... especially for PHP/MySQL.

[Antivirus]

So for instance, i have the query shown in query.gif (attached) which I am trying to optimize. I ran explain on it (also attached as explain.gif) and it's telling me that I need to create an index on table 1`c` correct?

I created an index on table scst_fb for fbid and when i run the query, it's still saying filesort in the extra column for explain. Shouldn't it be using the index now?

[Eikinskjaldi]

the file sort means that the system has to do an extra pass on the table to retrieve the rows in sorted order. Nested queries are tricky, you can get some optimisation by setting up a query cache, but if it were me I would use mysql v 5+ and set up a view.

First run the nested query in explain to make sure it is properly indexed.

[Antivirus]

I see. For anyone else interested, here's a direct link to the Mysql manual explaining the use of the explain command:

http://dev.mysql.com/doc/refman/4.1/en/explain.html

I'll be reading it today, i am sure it will help explain things - thanks.