1

Since InnoDB secondary indexes are separate B-trees with the key as the sort order and the PK as the 'value' stored, I assumed that reading into the main table by the secondary index was a lot like certain joining scenarios, but this seems not to be true for performance at least.

Here's an example

CREATE TABLE abc (a int unsigned, b int unsigned, c int unsigned, PRIMARY KEY (a), UNIQUE KEY (b));

a is the (ordered) set of numbers from 0 to 1000000. b is the "randomly" ordered set of the same numbers. So they're not parallel.

The following is intended to be an imitation of the secondary index b on abc.

CREATE TABLE ba (b int unsigned, a int unsigned, PRIMARY KEY (b));

INSERT INTO ba SELECT b, a FROM abc ORDER BY b ASC;

In the following examples I will SELECT c so as to prevent Using Index.

EXPLAIN SELECT AVG(c) FROM  abc WHERE b > 702991 AND b < 900000;

+----+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                            |
+----+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------+
|  1 | SIMPLE      | abc   | range | b             | b    | 4       | NULL | 385202 | Using index condition; Using MRR |
+----+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------+


EXPLAIN SELECT AVG(c) FROM ba JOIN abc USING (a) WHERE ba.b > 702991 AND ba.b < 900000;

+----+-------------+-------+--------+---------------+---------+---------+-----------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows   | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------+--------+-------------+
|  1 | SIMPLE      | ba    | range  | PRIMARY       | PRIMARY | 4       | NULL      | 403560 | Using where |
|  1 | SIMPLE      | abc   | eq_ref | PRIMARY       | PRIMARY | 4       | perf.ba.a |      1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+-----------+--------+-------------+

I wasn't expecting the explains to be identical But, for example, why can't we use MRR in the latter query? Why can't we buffer the as we read in off ba, sort those, and then do a range query into abc? It's at least relationally equivalent to the former query.

As expected, both queries yield the exact same result. However, the secondary index one takes about 1 second and then join one takes about 10 seconds, with default mysql settings.

Can someone explain why that is?

Thanks

Your Answer

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

Browse other questions tagged or ask your own question.