Looking at my slow log here's an example:

Query_time: 651 Lock_time: 0 Rows_sent: 22 Rows_examined: 12562564
SELECT distinct(poster_data.apnumber), poster_data.aptitle, poster_data.apfilename, poster_data.apdirectory, poster_data.apheight, poster_data.apwidth, poster_data.apth_height, poster_data.apth_width, poster_data.appost_height, poster_data.appost_width, poster_data.aptype, poster_data.apframe, poster_data.apmount, poster_data.apquick, IF (poster_data.aptitle LIKE 'The %',SUBSTRING(poster_data.aptitle,5), IF (poster_data.aptitle LIKE 'A %',SUBSTRING(poster_data.aptitle,3), IF (poster_data.aptitle LIKE 'An %',SUBSTRING(poster_data.aptitle,4), poster_data.aptitle))) AS sort_title FROM poster_prodcat,poster_categories,poster_data WHERE poster_categories.apcatname LIKE '%Tom Hanks%' AND poster_categories.apcatnum=poster_prodcat.apcatnum and poster_prodcat.apnumber=poster_data.apnumber ORDER BY sort_title ASC

poster_categories has indexes on both apcatnum and apcatname
poster_data has an index on apnumber
poster_prodcat has indexes on apcatnum and apnumber

It says it's examining 12,562,564 rows...the largest table, poster_prodcat has 6,594,523 rows, so why's it almost double?

We're also getting delays caused by "Copying to tmp table" Would that be because of the IF statements creating the sort_title?

Sorry if the answer to this is abundantly clear. I've been rebuilding the site since a big server crash and the sleep is rare right now.