CODEWITHSUNDEEP
mysql
李澤成
李澤成

Reputation: 45

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

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.

Upvotes: 3

Views: 99

Answers (1)

李澤成
李澤成

Reputation: 45

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.

Upvotes: 0

Related Questions