When to create indexes on custom tables?

[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.