Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: Why isn't MySQL using my INDEX?

    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
    Last edited by jvanv8; 02-21-04 at 11:11.

  2. #2
    Join Date
    Feb 2004
    Posts
    7
    GOT 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •