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

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

    Slow query. Why is it slow?


    1 - It takes almost 20 seconds. How else can I optimise?
    2 - Why not use convering_index_three?

    Indexes, EXPLAINed
    Code:
     SELECT `products_table`.`id`,
           `code`,
           `class`,
           `category`,
           `status`,
           `price`,
           `production_date`,
           `products_status`.`title` AS STATUS
    FROM   products_table
           JOIN `products_status`
             ON `products_status`.`id` = `products_table`.`status`
    WHERE  `products_table`.`id` > 0
           AND `class` = '6'
           AND `category` = 'E'
    ORDER  BY `products_table`.`id`
    LIMIT  50
    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`),
         KEY `covering_index_class` (`class`, `category`, `price`, `production_date`
         , `status`),
         KEY `covering_index` (`id`, `code`, `class`, `category`, `price`,
         `production_date`, `status`),
         KEY `covering_index_three` (`id`, `class`, `category`, `price`,
         `production_date`, `status`),
         KEY `covering_category` (`id`, `category`, `price`, `production_date`,
         `status`)
      )
    engine=innodb
    auto_increment=7642518
    DEFAULT charset=latin1
    Thanks
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    This is different from your other query that I answered here.

    1.
    All your indexes that begin with the id column:
    covering_index
    covering_index_three
    covering_category
    will probably not increase performance anything at all (or very marginally). This is due to as I mentioned in the other post that all data in an InnoDB table is already ordered on the primary key in this case id. So scanning the table or one of your large covering indexes will probably not make a difference and if you don't have those indexes then the amount of cache memory that they occupy can be put to better use elsewhere.

    2.
    WHERE `products_table`.`id` > 0
    AND `class` = '6'
    AND `category` = 'E'
    ORDER BY `products_table`.`id`
    LIMIT 50
    # Why do you have the first row:
    WHERE `products_table`.`id` > 0
    in your query? Since id is primary key and auto_increment that statement would always be true.
    # And the second row:
    AND `class` = '6'
    should be written:
    AND `class` = 6
    because the "class" column is a numeric value and by using quotes around them you force the DBMS to perform implicit type conversions that can come with a high penalty in performance.

    3.
    Now if you remove the "WHERE `products_table`.`id` > 0" then the optimal index for a query like this should be (class, category, id), but since your class and category have so very low cardinality I'm guessing a reverse range scan of the primary key/table is probably faster to find 50 entries. And since MySQL really wants to use indexes for WHERE I think that in this special case an index hint could be appropriate (I usually say that index hints are bad since if you have good indexes they are obvious choices and you don't need the hints, but in this case I'm guessing that this combination of class and category is very common which would mean that using indexes for the WHERE is not beneficial, but try it both with and without to see if MySQL figures it out).
    Code:
     SELECT `products_table`.`id`,
           `code`,
           `class`,
           `category`,
           `status`,
           `price`,
           `production_date`,
           `products_status`.`title` AS STATUS
    FROM   products_table USE INDEX (PRIMARY)
           JOIN `products_status`
             ON `products_status`.`id` = `products_table`.`status`
    WHERE
           `class` = 6
           AND `category` = 'E'
    ORDER  BY `products_table`.`id`
    LIMIT  50
    /Stefan
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by sr
    ... you force the DBMS to perform implicit type conversions that can come with a high penalty in performance.
    really?

    check this thread on reddit

    some people think it makes a difference, some people think it's negligible

    it can't be both

    therefore, in the interests of caution, it would be wise to pay attention to and avoid implicit conversions, whether or not they actually may have a performance penalty
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Originally Posted by r937
    therefore, in the interests of caution, it would be wise to pay attention to and avoid implicit conversions, whether or not they actually may have a performance penalty
    Correct, thanks for clarifying my point Rudy.
    In the case above it is most probably negligible.

    The main case that bites people is actually the other way round when they compare a numeric value with a CHAR column:
    Code:
    create table myVarchar (
      col1 varchar(30)
      ,index myVarchar_ix_col1(col1)
    );
    
    insert into myVarchar values (1),(2),(3),(4);
    
    -- Using wrong type forcing implicit conversion
    explain select col1 from myVarchar WHERE col1 = 3;
    
    -- Using right type avoiding implicit conversion
    explain select col1 from myVarchar WHERE col1 = '3';
    
    drop table myVarchar;
    Which causes two different execution plans:
    Code:
    mysql> -- Using right type avoiding implicit conversion
    mysql> explain select col1 from myVarchar WHERE col1 = '3';
    +----+-------------+-----------+------+-------------------+-------------------+---------+-------+------+--------------------------+
    | id | select_type | table     | type | possible_keys     | key               | key_len | ref   | rows | Extra                    |
    +----+-------------+-----------+------+-------------------+-------------------+---------+-------+------+--------------------------+
    |  1 | SIMPLE      | myVarchar | ref  | myVarchar_ix_col1 | myVarchar_ix_col1 | 93      | const |    2 | Using where; Using index |
    +----+-------------+-----------+------+-------------------+-------------------+---------+-------+------+--------------------------+
    1 row in set (0.00 sec)
    
    mysql> -- Using wrong type forcing implicit conversion
    mysql> explain select col1 from myVarchar WHERE col1 = 3;
    +----+-------------+-----------+-------+-------------------+-------------------+---------+------+------+--------------------------+
    | id | select_type | table     | type  | possible_keys     | key               | key_len | ref  | rows | Extra                    |
    +----+-------------+-----------+-------+-------------------+-------------------+---------+------+------+--------------------------+
    |  1 | SIMPLE      | myVarchar | index | myVarchar_ix_col1 | myVarchar_ix_col1 | 93      | NULL |    9 | Using where; Using index |
    +----+-------------+-----------+-------+-------------------+-------------------+---------+------+------+--------------------------+
    1 row in set (0.00 sec)
    Where we can see that a fast type=const becomes a slow type=index because it can't use the index properly. Instead it has to perform the implicit conversion from CHAR to numeric value for _every_ record in the table to be able to compare the record with the numeric value in the query. Which on a large table causes quite an impact.

    So yes my point was more that you should write correct queries to avoid extra work for the database and unnecessary problems.
    /Stefan
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,672
    Rep Power
    171
    Excellent info. Thanks

    1 - About forceing type conversions, class = 6 and class = '6' did not make any difference.

    2 - I can't remove "WHERE `products_table`.`id` > 0" . This is a very important part of the query because I use this for paginations.

    3 - The issue (slowness) was caused by adding ORDER BY. What you added there USE INDEX (PRIMARY) is causing the query to work really fast (less that 1 second) now. Would you please EXPLAIN what is going on there?

    Thanks
    Last edited by zxcvbnm; May 20th, 2013 at 07:13 PM.
  10. #6
  11. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    1.
    As I thought.
    It was just that I wanted to emphasize the importance of details.

    2.
    ok

    3.
    The reason why the USE INDEX (PRIMARY) is more effective is that I recommend to the optimizer to scan the table instead of using the merge of two unfavorable indexes, both class and category have a cardinality of 18, which means that if the values are evenly distributed the resulting merge would have to merge two sets of 411,000 rows (7,400,000/18).
    While with that index hint it will instead scan the table in id order and discard rows not matching class/category and it doesn't have to perform an order by since the scanning is already performed in order.
    BUT read below because I think that that index is probably better for you.

    4.
    When I now looked at your query I think you should try the real optimal index for this query:
    Code:
    ALTER TABLE products_table ADD INDEX products_ix_class_cat_id (class, category, id);
    The first time I thought that the combination of class and category would be very common and return a lot of rows so that index wouldn't be so good, but now I saw that it would only return about 15,000 rows which definitely makes it a good candidate.
    So add this index and remove the index hint from the query and try it.
    /Stefan
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,672
    Rep Power
    171
    @Sr

    Thank you.

    1 - I am still wondering why isn't MySql using covering_index_three since the query is:

    WHERE `products_table`.`id` > 0
    AND `class` = '6'
    AND `category` = 'E'
    and the first 3 columns of the index are id, class and category!

    I have read in several resources that Index has to be leftmost prefixes so it works. . So this should be a good candidate. Why isn't it being used?

    2 - I am creating the index that you recommened. As the database is very big it takes about 20 hours till it finishes the job. I am waiting on that.

    Meanwhile based on what I know above, I wonder why (class, category, id) could work! I understood that the order of the columns appearing in WHERE has to be the same with the order of the columns in the INDEX. This does not apply to what you adviced. Would you please explain why you sugested in INDEX that has a different or columns appearing in the WHERE.

    3 - Is there anything wrong with forcing indexes like USE INDEX PRIMARY? It is working EXTEREMLY fast and it solved my pagination issue too!

    Thank you.
  14. #8
  15. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    1.
    I'm guessing that the optimizer considers covering index_three a very poor candidate due to the condition id > 0 and that the id column is the first column in the index. This means that it will always have to scan the entire index. And as I mentioned, if you are going to scan that entire index then scanning the table itself is probably easier. And the optimizer thinks both is actually a pretty heavy operation it thinks the index_merge is cheaper. Which unfortunately isn't so since the cardinality for the two columns are so low that you get a huge amount of rows to merge.

    2.
    Originally Posted by zxcvbnm
    I understood that the order of the columns appearing in WHERE has to be the same with the order of the columns in the INDEX.
    Not so, the order of the columns in the _index_ is important yes! But the order in the query is not since a query is lexically analyzed and broken down in tokens (unless you move it around so that for example operator precedence makes the query to return a different result).
    But if you have conditions that contains wildcards (LIKE 'John%') or ranges (someColumn > 10 AND someColumn < 200) then they will force a range scan of the values in the index. While a condition that compares with a constant value (someColumn = 'John Doe' will be able to use the binary tree structure of the index to directly find that specific value in the index.
    So the optimal index for a specific query should be one that has all columns that are compared with a constant value in the WHERE/ON(when joining) to the left and any columns that are compared to ranges to the right. So that the DBMS can use the leftmost columns to find all records matching constant values and only has to range scan a limited amount of records.
    And in this case since the ORDER BY is on id and id is the last column it will use the index to retrieve the rows in order and avoid a filesort.

    3.
    No not really, but I usually try to avoid them because once you start using them you are trying to force the optimizer to use a certain index which may or may not be the best depending on differences in hardware and if somebody has created a new index.
    Consider that you wrote the application when 1GB RAM and a single threaded CPU was the hottest you could buy. So you add an index that is small in size and avoids CPU intensive work like indexmerge or filesorts.
    But now you can have huge amounts of RAM and CPU so your server could possibly execute the query faster by using another index and a bit more brute force and scan a larger part of a table which now happens to be all in the cache and discard non matching records, and then filesort it. What I'm trying to say is that you loose flexibility during runtime and introduce more administration further down the line.
    And my opinion is that most times when MySQL fails to find a good execution plan is when there is no really obvious good index available and it has to decide between two poor ones.
    /Stefan
  16. #9
  17. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,672
    Rep Power
    171
    @Sr

    Many thanks! Before I write I just mentione that I learned a technique than increases the performance dramatically! Joining the table to itself! Not sure why it works but it makes things MUCH faster!
    I discuss later in this post.




    Interesting that the order of the columns in WHERE is not important but the orders in _index_ is! I think I have been looking for
    So the optimal index for a specific query should be one that has all columns that are compared with a constant value in the WHERE/ON(when joining) to the left and any columns that are compared to ranges to the right.
    Does this mean:
    Code:
    WHERE X =
    to the left and
    Code:
    WHERE X > 
    WHERE X <
    WHERE BETWEEN
    to the right.
    For example these:
    Code:
    SELECT id,
           class
    FROM   table_id
    WHERE  category = 'A'
           AND class = 4 
    ORDER BY id
    Can benefit from index (category, class) or (class, category)
    Code:
    SELECT id,
           class
    FROM   table_id
    WHERE  id > 45
           AND category = 'A'
           AND class = 4  
    ORDER BY id
    Can benefit from index (category, class, id) or (category, class, id)
    Code:
    SELECT id,
           class
    FROM   table_id
    WHERE  id > 45
           AND category = 'A'
           AND class = 4
           AND production_date = '2012-12-12' 
    ORDER BY id
    Can benefit from index (category, class, production_date, id) or ( class, category, production_date, id) or (production_date, class, category, id) or (production_date, category, class, id)
    Code:
     SELECT id,
           class
    FROM   table_id
    WHERE  id > 45
           AND category = 'A'
           AND class = 4
           AND production_date BETWEEN '2012-12-12' AND '2013-01-01'
    ORDER BY id
    can benefit from index (category, class, production_date, id) or (category, class, id, produciton_date) or (category, class, produciton_date, id)
    ------------------------------------------------------------------




    About joining a table to itself.

    1 - Do you consider it a s a good idea?

    2 - Query 3 here how do you choose the right index or how do you approach to optimise this question?

    My guess would be for query 3:

    add an index on (status, class, category, id) or
    add an index on ( class, category, status, id)

    3 - About ORDER BY. How does it work when it comes to indexes? For example in order to optimise this :

    WHERE column_1 = something
    AND column_two = something
    ORDER BY column_three

    would this index be usable:
    (column_one,column_two,column_three)
    ?

    4 - Is it too much to expect all queries to run under 1 second or there are times that it's OK for query to take longer? In other words, what is OPTIMISED query?

    Gold medal for your patience Sr!


    EDIT:

    5 -
    Not so, the order of the columns in the _index_ is important yes! But the order in the query is not since a query is lexically analyzed and broken down in tokens (unless you move it around so that for example operator precedence makes the query to return a different result).
    But if you have conditions that contains wildcards (LIKE 'John%') or ranges (someColumn > 10 AND someColumn < 200) then they will force a range scan of the values in the index. While a condition that compares with a constant value (someColumn = 'John Doe' will be able to use the binary tree structure of the index to directly find that specific value in the index.
    So the optimal index for a specific query should be one that has all columns that are compared with a constant value in the WHERE/ON(when joining) to the left and any columns that are compared to ranges to the right. So that the DBMS can use the leftmost columns to find all records matching constant values and only has to range scan a limited amount of records.
    And in this case since the ORDER BY is on id and id is the last column it will use the index to retrieve the rows in order and avoid a filesort.
    What about the times when it is NOT ordered by id?
    Last edited by zxcvbnm; May 23rd, 2013 at 09:33 PM.
  18. #10
  19. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Originally Posted by zxcvbnm
    Interesting that the order of the columns in WHERE is not important but the orders in _index_ is! I think I have been looking for Does this mean:
    Code:
    WHERE X =
    to the left and
    Code:
    WHERE X > 
    WHERE X <
    WHERE BETWEEN
    to the right.
    For example these:
    Code:
    SELECT id,
           class
    FROM   table_id
    WHERE  category = 'A'
           AND class = 4 
    ORDER BY id
    Can benefit from index (category, class) or (class, category)
    Code:
    SELECT id,
           class
    FROM   table_id
    WHERE  id > 45
           AND category = 'A'
           AND class = 4  
    ORDER BY id
    Can benefit from index (category, class, id) or (category, class, id)
    Code:
    SELECT id,
           class
    FROM   table_id
    WHERE  id > 45
           AND category = 'A'
           AND class = 4
           AND production_date = '2012-12-12' 
    ORDER BY id
    Can benefit from index (category, class, production_date, id) or ( class, category, production_date, id) or (production_date, class, category, id) or (production_date, category, class, id)
    Code:
     SELECT id,
           class
    FROM   table_id
    WHERE  id > 45
           AND category = 'A'
           AND class = 4
           AND production_date BETWEEN '2012-12-12' AND '2013-01-01'
    ORDER BY id
    can benefit from index (category, class, production_date, id) or (category, class, id, produciton_date) or (category, class, produciton_date, id)
    ------------------------------------------------------------------
    Correct you have grasped it perfectly.

    Originally Posted by zxcvbnm
    About joining a table to itself.

    1 - Do you consider it a s a good idea?
    Yes if it's needed and improves performance I have no reservations about using it.
    For example if you are selecting one of the columns that contains a large BLOB/Text but you also use ORDER BY and LIMIT as in your case, then often the way MySQL solves that query is by creating a temporary table that is being sorted (filesort) before the LIMIT removes a lot of rows. Sorting speed is very dependent on the size of the data that has to be sorted so executing the logic to find the right records first and then self join to get the BLOB column can be a very good way forward, since the self join will most likely be performed on Primary Key lookup which is very fast.

    Originally Posted by zxcvbnm
    2 - Query 3 here how do you choose the right index or how do you approach to optimise this question?

    My guess would be for query 3:

    add an index on (status, class, category, id) or
    add an index on ( class, category, status, id)
    Correct!
    Which column you place first is dependent on the cardinality of that column, ie how unique the values are in that column. The most unique values should be the first column.
    And if you need to use that column on it's own in another query since you can use the leftmost columns in the indexes for other usages and potentially save you a single column index.

    Originally Posted by zxcvbnm
    3 - About ORDER BY. How does it work when it comes to indexes? For example in order to optimise this :

    WHERE column_1 = something
    AND column_two = something
    ORDER BY column_three

    would this index be usable:
    (column_one,column_two,column_three)
    ?
    Yes, but _if_ you add a LIMIT to this query you actually have two different choices for optimal index here and depending on the distribution of values in your table one or the other can be the faster one.
    Options:
    1: The index that you suggested with columns (1,2,3)
    2. You start with the order by column 3 (3,1,2).
    The difference is how many rows that the combination of the conditions on (1,2) will return. If there are few rows then option 1 will be better because you will very fast find the matching rows and range scan the third column in the index to solve the ORDER BY without filesort.
    BUT if the conditions on (1,2) will return a lot of rows then scanning the records in the index in id order and throwing away non matching records are usually better.

    Originally Posted by zxcvbnm
    4 - Is it too much to expect all queries to run under 1 second or there are times that it's OK for query to take longer? In other words, what is OPTIMISED query?
    Yes sometimes you will have to accept that some queries takes a longer time. For example different kinds of Reports can take a long time since you usually aggregate a lot of data in them.
    Optimizing database applications is very much about compromises:
    1. If you add indexes then you can have very huge gains on SELECT's but INSERT's/UPDATE's/DELETE's can also go very much slower. So if your application writes a lot to the database you don't want indexes that the database needs to keep up to date and instead you might have to live with that running a query from time to time will take a longer time.
    2. Optimizing costs money in the form of work hours which you could instead trade away by possibly buying more serious hardware for the DB server. For example any DB that grows beyond the available RAM on the server start to feel the performance impact. And by buying 16GB RAM for maybe $250 you might have postponed the problem for 2 years or indefinitely. Hardware can be pretty cheap these days compared to people.
    That said, if you don't know anything about queries, indexes and optimization you can very easily create a database and write queries that can bring the beefiest hardware to it's knees.

    Originally Posted by zxcvbnm
    Gold medal for your patience Sr!
    Thanks, sorry for the delay of this answer I've had a hectic schedule the last days.

    Originally Posted by zxcvbnm
    5 - What about the times when it is NOT ordered by id?
    At those times it will just ignore the id column in the index and the index will be a bit bigger in size than required for that query.
    /Stefan
  20. #11
  21. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,672
    Rep Power
    171
    Hi Sr;

    Thank you for help. Learning heaps. 3 other things:

    1 - Would you please explain
    And if you need to use that column on it's own in another query since you can use the leftmost columns in the indexes for other usages and potentially save you a single column index.
    2 - For a table with 5 columns: id, name, email, price, production_date; I am going to have MANY MANY indexes to cover different scenarios! Some examples:

    (name, id) for WHERE name = 'ee' ORDER BY id
    (price, id) for WHERE price > 14 ORDER BY id
    (email, id) for WHERE email = 'ee' and name = 'ew' ORDER BY id
    (name, email, id) ...
    (name, price, id)...
    (name, production_date, id)........
    (name, price, production_date, id).......
    (name, email, production_date, id)........

    3 - What about LIKE. Where is the order of the LIKEd column in regards to an index. For example:

    SELECT * FROM `blog` WHERE title LIKE '%wew' AND name LIKE '%ff%' ORDER BY id LIMIT 3

    Thank you
  22. #12
  23. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    1.
    The leftmost columns of a multi-column index can be used by a query on their own.
    Example:
    Index (col1, col2)
    can be used both by:
    WHERE col1 = ... AND col2 = ...
    WHERE col1 = ... ORDER BY col2
    WHERE col1 = ...
    But not by the query:
    WHERE col2 = ...

    2.
    Yes and that is why you don't try to cover all different scenarios with indexes but instead rely on that some queries or parts of queries will have to be solved by brute force at execution time instead.

    For example:
    You have a table: (col1, col2, col3, col4)
    Optimal index for query "WHERE col1 = .. AND col2 = .. ORDER BY col4" probably is (col1, col2, col4).
    But if you write to the table then this index will need to be updated and maintained every time you insert/delete records or update either col1, col2 or col4. And it might be quite large in MB since it contains all three columns and this consumes both disk space and RAM memory (which might be put to better use by caching another index).
    So what if you drop col4 from the index?
    If the index (col1, col2) will return 20 rows then the ORDER BY col4 can be solved by just sorting 20 rows before returning the result. That way you have reduced the index size to 2/3's and yet have almost the same query execution time (give or take maybe 1-2 microseconds) which might be a very good trade off.
    So the trick is to figure out which queries that are most common and where you will have the most benefit of an index. Because you can't create indexes everywhere since it slows down writes and they might compete about RAM which means that index data has to be read from disk instead which is very slow.

    3.
    Queries with LIKE that begins with a wild card % like "LIKE '%John%' " should be tried to be avoided since they can't be indexed properly. Since the wildcard is at the beginning a query execution must include scanning through the entire text string of every post in the database.
    Queries like WHERE col2 LIKE 'John%' are much better since the start point of the search string correlates to the start point of every string in the database. This means that the database can scan an index that contains this column trying to find matches.
    But if you write a query like WHERE col2 = 'John' with no wildcards then the database can use a much faster way to find the records.

    So to answer your question a query like that will always have to scan all rows of any index with for example (title, name) or (title, name, id).

    So to sum it up, wildcards at the end are not ideal but ok, while wildcards in the beginning of a search string should be tried to avoided.
    /Stefan
  24. #13
  25. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,672
    Rep Power
    171
    Hi Sr;

    It looks like all queries are working super fast, many thanks for all the above

    There was a scenario that I needed to create index for (the query was taking +10 seconds):
    Code:
    SELECT q2.id,
           q3.code,
           q3.class,
           q3.category,
           q3.price,
           q3.production_date,
           q4.title AS STATUS
    FROM   (SELECT q1.id
            FROM   products_table AS q1
            WHERE  q1.id > 0
                   AND price BETWEEN 4 AND 300
                   AND class = 3
                   AND category = 'e'
                   AND status = 3
            ORDER  BY q1.id ASC
            LIMIT  20) AS q2
           INNER JOIN products_table AS q3
                   ON q3.id = q2.id
           JOIN products_status AS q4
             ON q4.id = q3.status
    LIMIT  0, 30
    So I decided to create this index:
    class, category, status, id, price and as expected it worked fine and it now runs in less than a second.

    About this:

    1 - class, category and status have the same Cardinality (17) so the first three columns in the index could be
    class, category, status or
    category, class, status or
    status, class, category

    Correct?


    2 - Now about "ranges". There are price and id. As id has much higher Cardinality, it comes first. I wonder, would it still be before price if it didn't exist in WHERE and it only appeared in ORDER BY?

    3 - What about underscore?

    Code:
    SELECT 
       id
        WHERE code LIKE '_a%C%';
    4- You probably mentioned this before but can the query below benefit from the index?

    Code:
    SELECT id 
        FROM table 
        WHERE
        name = 'test' AND 
        id < 355 
        AND level = 45
    Index: (name, class, level, id).

    The reason I ask is appearance of class in the index (and not in the query).



    Thanks
    Last edited by zxcvbnm; June 8th, 2013 at 02:16 AM.

IMN logo majestic logo threadwatch logo seochat tools logo