Ian M. Evans wrote:
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
It's having to check each poster_data row to make sure it's distinct.
If you remove the distinct is it fast?
If the data in that table is unique, why do you need the distinct?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
