1

If I give you 2 explain results from MySQL,how do you know which query is better?

the first sql explain

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest2
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 700100
        Extra: 
1 row in set (0.00 sec)

and the second sql explain

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: b.id
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: sbtest2
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 700100
        Extra: Using index
3 rows in set (1.46 sec)

I think I will choose the first query because there are "a lot of" things to do in the second explain,but actually the second query is much faster than the first query , so my question is if I don't see the response time,how to judge their performance through explain ?

the first query's Duration: 4.43704325

select * from sbtest.sbtest2 
order by id 
limit 700000,100;

after restart mysql to clean cache and the second one is Duration: 2.08889400 (faster)

select * from sbtest.sbtest2 a
inner join 
(select id from sbtest.sbtest2  order by id limit 700000,100 ) as b
on a.id=b.id;

table structure is above

mysql> show create table sbtest.sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=5265529 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
1 row in set (0.00 sec)

can anybody help me ?


additional

mysql> explain select id from sbtest.sbtest2  order by id limit 700000,100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest2
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 700100
        Extra: Using index
1 row in set (0.00 sec)

time is 4.28456200

0

1 Answer 1

1

EXPLAIN estimates row counts, often poorly. Even so, the counts do not reflect how much stuff is hauled around. If it is only id, that is not much. If it is all of * (as in SELECT *), that is a lot more. This is especially the case when it has to reach into the data to get all the columns.

Using index means that it is cruising through the index BTree. Without that phrase, it is bouncing between the index BTree and the data BTree (using the PRIMARY KEY). This bouncing is costly, and that cost is not adequately reflected in EXPLAIN.

Using index may be your only clue, but it cannot be trusted. Sometimes it helps only a little, sometimes it helps a lot.

limit 700000,100 is an awful thing to do. It must fetch 700100 rows (of just id or of *) but throw away nearly all of them.

Your complex query is the trick to make OFFSET perform somewhat better. There is no way to say skip over 700000 rows without looking at them (which is what everyone wants).

Other notes:

Don't use CHAR, especially not with utf8; switch to VARCHAR. char(120) consumes a fixed 360 bytes, regardless of the text. The unnecessary bulk is a performance issue, especially if the table is bigger than can be cached.

More discussion of OFFSET.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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