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

    Join Date
    Feb 2012
    Posts
    3
    Rep Power
    0

    GROUP BY with WHERE not using INDEX


    I have an `advertisements` tables with around 1 million records. Table has around 30 fields and 700MiB in size.
    Overview of table as follows:
    id[int(11)] | year[smallint(4)] | status[tinyint(1)]
    All these fields are indexed. id = PK

    I want to find advertisement count by year.
    Following query executes in 0.15 seconds, and when I use explain, it says "Using index"
    SELECT
    `advertisements`.`year`,
    COUNT(*) AS count
    FROM `advertisements`
    GROUP BY `advertisements`.`year`
    However, when I add condition(WHERE) to it. It takes around 6-9 seconds. And under explain it says "Using where; Using temporary; Using filesort"
    SELECT
    `advertisements`.`year`,
    COUNT(*) AS count
    FROM `advertisements`
    WHERE `advertisements`.`status` = 1
    GROUP BY `advertisements`.`year`
    How can I make this query use INDEX? Similarly, query is slow when joining other tables for additional checks(WHERE)

    server version:5.5.8

    Thanks for help
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    3
    Rep Power
    0

    GROUP BY with WHERE not using INDEX


    I have an `advertisements` table with around 1 million records. Table has 30 fields and 700MiB in size.
    Please consider following table layout
    id[int(11)], year[mediumint(4)], status[tinyint(1)]

    When I run following query
    SELECT `advertisements`.`year`, COUNT(*) AS COUNT FROM `advertisements` GROUP BY `advertisements`.`year`
    It gets executed in 0.2 seconds. EXPLAIN says, this query is "Using index" (`id`= PK, `year` and `status` is indexed)

    Howerver, when I add condition(WHERE), it takes around 6-9 seconds
    SELECT `advertisements`.`year`, COUNT(*) AS COUNT FROM `advertisements` WHERE `advertisements`.`status` = 1 GROUP BY `advertisements`.`year`
    And EXPLAIN query says, "Using where; Using temporary; Using filesort"
    And idead how to make this query use INDEX? or make it execute fast?

    Thanks for help
  4. #3
  5. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    1.
    How many percent of the records in the table has Status=1?

    If it's less than 20% I suggest:
    Code:
    ALTER TABLE advertisements ADD INDEX adv_ix_status_year( status, year);
    Else I suggest:
    Code:
    ALTER TABLE advertisements ADD INDEX adv_ix_year_status( year, status);
    Either way when you try one of these indexes you can remove the corresponding index that only has the first column in it.

    The reason why these indexes should speed up things for you is that these composite indexes will solve both the WHERE status=1 and retrieving the year column values in order which is a necessity to group the values.
    /Stefan
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    3
    Rep Power
    0
    Thanks for the reply
    This is just one case, I am also fetching count by manufacturer, category etc. And I feel using composit index (multiple fields + status) is not an efficient solution(your views?).
    Also, it won't work when I join multiple tables.

    I am using memcached, however after update, first page load takes long time.
    Will use sphinx or 'proactive cache'. Any experience with 'proactive cache' using libmemcached + UDF + mysql?
    sphinx installing was easy and performs very good. Not sure about libmemcached; and how to use UDF's to populate cache, and how to sync keys between mysql and PHP.

    Thanks for your time.

IMN logo majestic logo threadwatch logo seochat tools logo