I am having a problem with a query that's taking eons because it is not using the Primary index (although it is listing it as a possible key).
Heres a simplification of the situation:
Tables
CATEGORY - primary key = category_id
PAGE - primary key = page_id
PAGE_LINK - primary key = page_id, category_id
PAGE_AUTHOR - primary key = page_id, author_id
AUTHOR_RATING - prim key = author_id
(Think of page as something tangable, not an html page
The Query attempts to display 20 'pages' associated with a selected category. Since a page can be written by more than 1 author, I grouped by page_id. The query looks like this:
SELECT count(PA.page_id) as num_authors, MAX(AR.rating_val) as rating, P.name FROM category AS C, page_link AS PL, page AS P, page_author AS PA, author_rating AS AR
WHERE C.category_id = '2' AND C.category_id = PL.category_id AND PL.page_id = P.page_id AND PL.page_id = PA.page_id
GROUP BY PL.page_id ORDER BY 'rating' DESC LIMIT 0,20;
Code:
table type possible_keys key key_len ref rows Extra
C const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort
P ALL PRIMARY NULL NULL NULL 1112220
PL eq_ref PRIMARY PRIMARY 8 P.page_id,cnst. 1 Using index
PA ref PRI.,PG_ID PRIMARY 4 PL.page_id 1
AR eq_ref PRIMARY PRIMARY 4 PA.author_id 1
What's going wrong here??
Just a note, that isn't a typo or a bug, there really are 1112220 rows in the 'page' table (and this site is still a baby!).
Shouldn't the P (page) table be using the primary index?? It uses the PK on other queries... but that 1112220 rows searched should be more like 20 shouldn't it??
Thanks for any help on this!!
- John
dBforums
>
Why isn't MySQL using my INDEX?
02-21-04, 09:42
Linear Mode
