Bad news: I have a slow query that doesn't appear to be using an index
even if I force it.
Good news: the forehead shaped dent in my desk is really progressing well.
Here's the query:
SELECT DISTINCT poster_data.*
FROM poster_data, poster_prodcat, poster_categories
WHERE poster_categories.apcatname LIKE '%Celebrity Name%'
AND poster_prodcat.apcatnum = poster_categories.apcatnum
AND poster_data.apnumber = poster_prodcat.apnumber
ORDER BY poster_data.aptitle
poster_data has 61,343 rows of unique data for each apnumber (poster)
poster_categories has 26,716 rows...a category id and a category name
poster_prodcat has 993,410 rows...a category id and the apnumber (poster)
My query is supposed to find all the posters that are from categories
that contain a celebrity name.
Here's the slow entry:
# Query_time: 10 Lock_time: 0 Rows_sent: 30 Rows_examined: 1271071
So it's examining over a million rows to send 30.
When I do an explain:
Table: poster_data
Type: ALL
Possible Keys: posterid
Key: NULL
Key_len: NULL
Rows: 61479
Extra: Using temporary; Using filesort
Table: poster_prodcat
Type: ref
Possible Keys: prodcat_ind,apcatnum,apnumber
Key: apnumber
Key_Len: 3
ref: poster_data.apnumber
Rows: 12
Extra: Distinct
Table: poster_categories
Type: eq_ref
Possible Keys: PRIMARY
Key: PRIMARY
Key_len: 4
Ref: poster_prodcat.apcatnum
Rows: 1
Extra: Using where; Distinct
Even if I force the use of posterid in the poster_data table, it still
doesn't use it.
Essentially the query's asking to find category numbers for categories
that contain the name, use those numbers to find the poster id's that
match those category numbers and then return the poster info.
Thanks for any insight.
--
Ian Evans
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
