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:
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;
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??
The primary key for a PAGE_LINK is page_id,category_id BUT I also needed a separate index on BOTH the page_id and the category_id. The still think the combined index should have worked properly though.... is this issue particular to MySQL or is this an issue in all relational databases?
- Feeling much better now. Searching 1.4 million rows was definately slowing things down a bit