#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171

    Is covering index the solution where intersect (multiple indexes) is slow?


    Hello;

    As I make the paginations, I am gonna need the total rows for the users search. I am not going to use limit. Some search criterias are compulsary to limit the results but taht can't guarantee the number of reults are gonna be small.

    For example I am not happy that performance here: It takes 5 seconds.

    EXPLAIN
    Code:
    SELECT `id`,
           `code`,
           `class`,
           `price`
    FROM   products_table
    WHERE  `class` = '2'
           AND `category` = 'R'
    Code:
     CREATE TABLE `products_table`
      (
         `id`                INT(11) NOT NULL auto_increment,
         `code`              VARCHAR(4) NOT NULL,
         `class`             INT(11) NOT NULL,
         `category`          VARCHAR(5) NOT NULL,
         `price`             DECIMAL(6, 2) NOT NULL,
         `production_date`   DATE NOT NULL,
         `quick_description` VARCHAR(255) NOT NULL,
         `description`       TEXT NOT NULL,
         `status`            INT(11) NOT NULL,
         PRIMARY KEY (`id`),
         KEY `production_date` (`production_date`),
         KEY `index_price` (`price`),
         KEY `class_index` (`class`),
         KEY `category_index` (`category`),
         KEY `status_index` (`status`)
      )
    engine=innodb
    auto_increment=6222518
    DEFAULT charset=latin1
    Considering the scenario above, would it be a good idea to run this query?
    Code:
    ALTER TABLE products_table
      ADD INDEX covering_index (id, code, class, category, price, production_date,
      quick_description, description, status);
    I am going to have quite few different search queries that include these coulmns along with other tables with foreign key.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,232
    Rep Power
    4279
    the answer to your question(s) is...

    indexes are only used from the left

    an index with `id` as the leftmost column will be totally ignored if there isn't a WHERE condition for that column (and, since id is unique, any other columns to the right of it will be useless)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171
    Originally Posted by r937
    the answer to your question(s) is...

    indexes are only used from the left

    an index with `id` as the leftmost column will be totally ignored if there isn't a WHERE condition for that column (and, since id is unique, any other columns to the right of it will be useless)
    Thanks. Understandable,,,partially! Let me ask a better question:

    The search criteria are dynamic and $data can be any combination of the columns. The user could search for specific class, code, price, production date or none! I don't think creating an index per scenario would be a good idea.
    PHP Code:
    public function search_products_number_of_results($data)
        {
            
    $query $this->db->select('id, code, class, price')->from('products_table')->get_where(''$dataNULL0);
            return 
    $query->num_rows();
        } 
    In this case (which I think is very popular) how would you create indexes?
    Thanks
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,232
    Rep Power
    4279
    Originally Posted by zxcvbnm
    I don't think creating an index per scenario would be a good idea.
    why do you think that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171
    This is in my notes:
    Originally Posted by r937
    why do you think that?
    Originally Posted by sr
    Don't go nuts and create an insane amount of indexes now.

    Clustered indexes are great for performance, but as with all indexes you should consider a balance, because if you create a lot of indexes then these compete about disk space, RAM usage, etc, etc so they can actually slow down the entire application.

    So sometimes a single column index might be better than a clustered one since it occupies less RAM and is smaller in size which means an index scan is faster.

    Optimizing an application is about balance, having a lot indexes doesn't mean that the application will be fast, as well as having no indexes at all won't give you performance either.

    So create indexes wisely and with a bit of caution.

    I'm just saying this because it sounded like you where going to start creating indexes yesterday.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,232
    Rep Power
    4279
    "create indexes wisely and with a bit of caution"

    look at each of the combinations of possible search criteria

    also, evaluate the cardinality of each column -- the more unique values in a column, the better it will work as an indexed column

    run some tests with and without complex indexes (i.e. more than one column)

    do EXPLAINs

    you will find the answers you seek not by getting advice on a forum from people who are not familiar with your data, but by hard work on your part
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171
    Originally Posted by r937
    "create indexes wisely and with a bit of caution"

    look at each of the combinations of possible search criteria

    also, evaluate the cardinality of each column -- the more unique values in a column, the better it will work as an indexed column

    run some tests with and without complex indexes (i.e. more than one column)

    do EXPLAINs

    you will find the answers you seek not by getting advice on a forum from people who are not familiar with your data, but by hard work on your part
    Trust me I have been on this for quite sometimes

    After tests and comments I think this is a good idea:

    Drop all indexes (except primary key id) and then:
    Code:
    ALTER TABLE products_table
    ADD INDEX class_index ( code, class, category, price, production_date, status);
    
    ALTER TABLE products_table
    ADD INDEX class_index ( class, category, price, production_date, status);
    
    ALTER TABLE products_table
    ADD INDEX class_index ( category, price, production_date, status);
    
    ALTER TABLE products_table
    ADD INDEX class_index ( price, production_date, status);
    
    ALTER TABLE products_table
    ADD INDEX class_index ( production_date, status);
    
    ALTER TABLE products_table
    ADD INDEX class_index ( status);
    There will be only 1 wondering left: Can this index
    Code:
    ALTER TABLE products_table
    ADD INDEX class_index ( price, production_date, status);
    be used for
    Code:
    SELECT id
    FROM   products_table
    WHERE  price BETWEEN 200 AND 300
           AND status = 1
    Note: I skipped production date.

    If that is ok, then that way any combination in WHERE calause will be covered by LEFTMOUST PREFIX.

IMN logo majestic logo threadwatch logo seochat tools logo