#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    6
    Rep Power
    0

    Optimize query with 3 joins


    I have 5 tables.

    Code:
    +------------+ +--------------+ +---------------+ +-------------+ +-------------+
    |    Blog    | |     Feed     | |   Category    | | SubCategory | | Blog_Subcat |
    +------------+ +--------------+ +---------------+ +-------------+ +-------------+
    | blog_id    | | feed_id      | | category_id   | | subcat_id   | | blog_id     |
    | blog_title | | blog_id      | | category_name | | subcat_name | | subcat_id   |
    | blog_blog  | | date_created | +---------------+ | category_id | +-------------+
    +------------+ +--------------+                   +-------------+
    I want to be able to get some feed by a category name. This is the query that I have right now:

    Code:
    SELECT   f.feed_id
    FROM     Feeds       AS f
        JOIN Blog_Subcat AS bs ON bs.blog_id    = f.blog_id
        JOIN SubCategory AS s  ON s.subcat_id   = bs.subcat_id
        JOIN Category    AS c  ON c.category_id = s.category_id
    WHERE    c.category_name = "art"
         AND f.date_created <= NOW()
    ORDER BY f.date_created DESC
    When I do a EXPLAIN EXTENDED I get this:

    Code:
    +--------+-------------+-------+--------+-------------------------------------------------+-------------------------+---------+-----------------+------+----------+-------------------------------------------------------+
    |     id | select_type | table |  type  |                  possible_keys                  |           key           | key_len |       ref       | rows | filtered |                         Extra                         |
    +--------+-------------+-------+--------+-------------------------------------------------+-------------------------+---------+-----------------+------+----------+-------------------------------------------------------+
    |      1 | SIMPLE      | c     | ref    | PRIMARY,ati_categories_index01                  | ati_categories_index01  |     153 | const           |    1 | 100.00   | Using where; Using index; Using temporary; Using f... |
    |      1 | SIMPLE      | f     | range  | ati_feeds_index01,ati_feeds_index02             | ati_feeds_index02       |       9 | NULL            | 8216 | 100.00   | Using where; Using join buffer                        |
    |      1 | SIMPLE      | bs    | ref    | ati_blog_subcat_index01,ati_blog_subcat_index02 | ati_blog_subcat_index01 |       5 | ati.f.blog_id   |    1 | 100.00   | Using where                                           |
    |      1 | SIMPLE      | s     | eq_ref | PRIMARY,ati_subcat_index01                      | PRIMARY                 |       4 | ati.bs.category |    1 | 100.00   | Using where                                           |
    +--------+-------------+-------+--------+-------------------------------------------------+-------------------------+---------+-----------------+------+----------+-------------------------------------------------------+
    When I run this query it takes sooo long! I have tried many things and have gotten this far. Is there any way for me to optimize it better? Thanks!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    157
    Rep Power
    89
    I can't see your full explain output on my phone but have you created indexes for your foreign keys?
  4. #3
  5. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1945
    Looking at Extra colument, it looks like it is the category table which is slowing the query down.
    How is the speed if you don't include the category table in the query?

    What if you try to add the condition for the category directly on the join:
    Code:
    ...
    JOIN Category AS c  ON c.category_id = s.category_id AND c.category_name = "art"
    WHERE f.date_created <= NOW()
    ...
    To continue on the question by TASB, please post which index you have on all tables and fields, not only the foreign key.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    6
    Rep Power
    0
    Hi! It didnt help. I have an index in every single field that I use in this query.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    I'm afraid my approach is rather unscientific, but anyway...

    Drop all those indexes. Add a compound index on (blog_id,date_created).

    Help any?

IMN logo majestic logo threadwatch logo seochat tools logo