0

I'm new to the MySQL world.

When I read about MySQL Range Optimization some questions were raised related to multipart indices.

MySQL 5.7 Doc

(http://jorgenloland.blogspot.com/2011/08/mysql-range-access-method-explained.html)

Question 1:
What is the meaning of single and continuous intervals?

Question 2:
Related to Multipart index and keylen

Table Structure

Create table orders(
    customer_id int,
    value int,
    name varchar(20),
    primary key(customer_id,value)
) ;

A)

mysql> explain select * from orders where customer_id  <= 2  and value = 1;

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    4 |    25.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

B)

mysql> explain select * from orders where customer_id  < 2  and value = 1;

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    4 |    25.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

Why does the explain statement (A) show keylen as 8 but (B) shows keylen as 4 ?

0

There are many flaws in the output from EXPLAIN. This is a new one to me.

Things like "covering" indexes, ranges, ORDER BY, etc are poorly indicated by key_length. Also, the length of NULLable columns is a constant, not 1 or 2, depending on the schema.

"Using temporary, Using filesort" is usually on the first line, even if that is not when the sort occurs.

GROUP BY x ORDER BY y may require two sorts; there is no clue of such.

Use EXPLAIN FORMAT=JSON SELECT ... to get more information, sometimes close to all the info available.

Back to the specific example.

Well, a better index is this order: (value, customer_id). Columns involved in col=constant should come first for both columns of the index to be efficiently used.

A guess is that there is a relatively new Optimization. What version(s) of MySQL/MariaDB show this inconsistent key_len?

As Jørgen says in that blog:

The difference between <,> and BETWEEN sounds strange as these conditions should be treated the same way in the optimizer code. One theory is that the optimizer considers two ways of executing this query to be almost equally good and therefore sometimes picks a good plan and other times a suboptimal plan due to small differences in index statistics.

If you are able to reproduce this issue consistently, however, you might want to file a bug at bugs.mysql.com.

That is, please provide a full test case -- CREATE, INSERT, EXPLAIN, etc., that consistently demonstrates the issue in at least one version of MySQL (the newer, the better).

Found the answer in his latest blog:

But that leaves a question: Why is key_length=10 for the BETWEEN version of the query? The reason is that although the value keypart cannot be used to set up ranges, it is used to determine the starting point of the first range (there is only have one range in this query - try optimizer trace and see for yourself), and there is no use in reading index rows with value < 500 and customer_id=7.

Thanks for the Question; I learned something.

As for your Question 1:

By contrast, the condition key_part3 = 'abc' does not define a single interval and cannot be used by the range access method.

That, together with WHERE key_part3 = 'abc' (without any other clauses) together with INDEX(kp1, kp2, key_part3)... It is saying that there may be several clumps (not a "single range") of places in the ordered list based on (kp1, kp2, key_part3) where abc is found.

It is better to go to one spot in the index, then scan until there are no more relevant rows. But in that case, you need to look in several spots in the index. The discussion is leading up to the 'new' ability to do so (somewhat).

Bottom line... As discussed in my Index Cookbook, the best indexes start with column(s) that are tested against constant(s). Sometimes the Optimizer can make some use of indexes where the columns are not optimally ordered.

  • @RKs -- I edited to my Answer a few times; please check for the latest. – Rick James Nov 23 '18 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.