Welcome to WebmasterWorld Guest from 34.204.189.171

Forum Moderators: open

Message Too Old, No Replies

Am I missing an index for this statement

Select taking over 600 seconds

     
7:21 pm on Aug 23, 2006 (gmt 0)

Full Member

10+ Year Member

joined:Aug 5, 2003
posts:250
votes: 6


I've got a few selects grabbing movie poster information that are just killing my server.

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.

7:36 pm on Aug 23, 2006 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts:393
votes: 0


I suspect that by using the word DISTINCT you are causing the data to be written to a temp table for sorting and removal of duplicates.
9:31 am on Aug 28, 2006 (gmt 0)

New User

10+ Year Member

joined:Feb 2, 2005
posts:34
votes: 0


I guess one of the things that is slowing your query down is the IF() statement with LIKE/SUBSTRING functions.

A good idea is:

a) when inserting data into table cut the title according the algorythm, so you dont have to use LIKE/SUBSTRING when querying each time

b) add fulltext index on title column, and database engine will get rid of stopwords ("a", "the", etc) automaticaly