is this mysql bug? two command almost the same, but complex one is faster than simple

Question

mysql> desc ads_common;
+--------------+-----------------------+------+-----+-------------------+----------------+
| Field        | Type                  | Null | Key | Default           | Extra          |
+--------------+-----------------------+------+-----+-------------------+----------------+
| ads_id       | bigint(20) unsigned   | NO   | PRI | NULL              | auto_increment |
| user_id      | bigint(20) unsigned   | NO   | MUL | NULL              |                |
| website_type | enum('group','user')  | NO   | MUL | NULL              |                |
| website_id   | bigint(20) unsigned   | NO   |     | NULL              |                |
| subgroup_id  | bigint(20) unsigned   | NO   |     | NULL              |                |
| country_id   | smallint(5) unsigned  | NO   | MUL | NULL              |                |
| type_id      | int(10) unsigned      | NO   | MUL | NULL              |                |
| phone        | int(11)               | NO   |     | NULL              |                |
| mobile_phone | int(10) unsigned      | NO   |     | NULL              |                |
| cat_id       | int(10) unsigned      | NO   | MUL | NULL              |                |
| brand_id     | mediumint(8) unsigned | NO   | MUL | NULL              |                |
| model_id     | mediumint(8) unsigned | NO   | MUL | NULL              |                |
| area_id      | int(10) unsigned      | NO   | MUL | NULL              |                |
| is_offering  | tinyint(1)            | NO   | MUL | 1                 |                |
| price        | int(11)               | NO   | MUL | NULL              |                |
| item_status  | tinyint(3) unsigned   | NO   | MUL | NULL              |                |
| add_time     | timestamp             | NO   | MUL | CURRENT_TIMESTAMP |                |
| question_num | smallint(5) unsigned  | NO   |     | NULL              |                |
| is_stop      | tinyint(1)            | NO   | MUL | 0                 |                |
+--------------+-----------------------+------+-----+-------------------+----------------+

I do have type_id,country_id,is_top Multiple-Column Index and type_id single index. ads_common table about 180245 rows.

1.first query command

SELECT c.ads_id FROM ads_common AS c                                
WHERE c.type_id IN (185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241) 
AND c.country_id=226 AND is_stop=0  ORDER BY c.ads_id DESC

180,047 rows found,This took 0.0026 second

2.second query command

SELECT c.ads_id FROM ads_common AS c
WHERE c.type_id=187 
AND c.country_id =226 AND is_stop=0 ORDER BY c.ads_id DESC

180,044 rows found, This took 0.2335 second

Only different two command is "type_id IN (..187..)" and "type_id=187", why command 2 is slower than 1?

If command 2 change to:

SELECT c.ads_id FROM ads_common AS c
WHERE c.type_id IN (185, 187)
AND c.country_id =226 AND is_stop =0 ORDER BY c.ads_id DESC

180,044 rows found, This took 0.0007 second, this is faster.

SELECT c.ads_id FROM ads_common AS c
WHERE c.type_id IN (187)
AND c.country_id =226 AND is_stop =0 ORDER BY c.ads_id DESC

180,044 rows found, This took 0.2267 second, this is faster.

All command return almost the same rows.

What else is the processor doing? Is the data already in the cache? Run your tests in reverse order! — Ed Heal
Wow, you've seriously carpet bombed your table with indexes =/ — Ja͢ck
MySQL uses one index per table, so in the first case it probably uses country_id whereas for the second query it uses type_id. Depending on the cardinality of your columns, one could be faster than the other. — Ja͢ck

李澤成李澤成 · Accepted Answer · 2013-04-02T07:27:36

1.command

SELECT c.ads_id
FROM ads_common AS c                                
WHERE c.type_id=187
AND c.country_id=226 AND is_stop=0  ORDER BY c.ads_id DESC

( 180,044 total, Query took 0.2364 sec)


    EXPLAIN

    id:1
    select_type:    SIMPLE
    table:c
    type:index_merge
    possible_keys:type_id,country_id,is_stop
    key:is_stop,country_id,type_id
    key_len:1,2,4
    ref:NULL
    rows:22530  
    Extra:Using intersect(is_stop,country_id,type_id); Using...

2.command

SELECT c.ads_id
FROM ads_common AS c                                
WHERE c.type_id IN (0,187)
AND c.country_id=226 AND is_stop=0  ORDER BY c.ads_id DESC

180,044 total, Query took 0.0010 sec


    EXPLAIN

    id:1
    select_type:    SIMPLE
    table:c
    type:ref
    possible_keys:type_id,country_id,is_stop
    key:country_id
    key_len:2
    ref:const
    rows:90122
    Extra:Using where

very interesting, almost the same command, but get different performance.

finally, i restart mysql and execute command, restart every time before execute command,so cache is not problem.


command 1 took 1.2358 second "c.type_id=187"
command 2 took 0.05 second "c.type_id IN (0, 187)", you can replace 0 to any number, result the same.

Yes, "c.type_id in (187)" is same performance with "c.type_id=187", but "c.type_id in (187,0)" or "c.type_id in (0,187)" get better performance.

is this mysql bug? two command almost the same, but complex one is faster than simple

1 Answers