June 2, 2006

Indexes in MySQL

Posted by Vadim |

MySQL does not always make a right decision about indexes usage.
Condsider a simple table:

PLAIN TEXT
SQL:
  1. CREATE TABLE `t2` (
  2. `ID` int(11) DEFAULT NULL,
  3. `ID1` int(11) DEFAULT NULL,
  4. `SUBNAME` varchar(32) DEFAULT NULL,
  5. KEY `ID1` (`ID1`)
  6. ) ENGINE=MyISAM DEFAULT CHARSET=latin1

SELECT COUNT(*) FROM t2;
250001 (V1)

SELECT COUNT(*) FROM t2 WHERE ID1=1;
83036 (V2)
(execution time = 110 ms)

That is index selectivity by condition (ID1=1) is V2/V1 = 0.3321 or 33.21%

It is said (e.g. book "SQL Tuning") if selectivity over 20% then a full table scan is preferable than an index access.
As far as I know Oracle alway chooses a full table scan if selectivity over 25%.

What with MySQL:

PLAIN TEXT
SQL:
  1. mysql> EXPLAIN SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1;
  2. +----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
  3. | id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
  4. +----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
  5. | 1 | SIMPLE | t2 | ref | ID1 | ID1 | 5 | const | 81371 | USING WHERE |
  6. +----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+

That is MySQL will use index for this query.

Let's compare the execution time with index access and with table scan:

SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1 - 410 ms
SELECT COUNT(SUBNAME) FROM t2 IGNORE INDEX (ID1) WHERE ID1=1 - 200 ms

As you see the table scan is faster by 2 times.

Consider more extremal case: selectivity ~95%:
SELECT cnt2 / cnt1 FROM (SELECT count(*) cnt1 FROM t2) d1, (SELECT count(*) cnt2 FROM t2 WHERE ID1=1) d2;
0.9492 = 94.92%

Explain still claims MySQL will use index.

Execution time:
SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1 - 1200 ms
SELECT COUNT(SUBNAME) FROM t2 IGNORE INDEX (ID1) WHERE ID1=1 - 260 ms

That is table scan is faster by 4.6 times.

Why does MySQL choose index access?
MySQL doesn't calculate index selectivity, just estimates count of logical input/output operations, and for
our case count of Logical I/O for index access is less than for table scan.

So be careful with indexes, they help in not all cases.

Posted by Vadim @ 10:36 am :: optimizer  
Print This Post Print This Post book mark Indexes in MySQL in del.icio.usdel.icio.us :: submit Indexes in MySQL to digg.comdigg  
Comment RSS :: Trackback URI  
Related posts: :Duplicate indexes and redundant indexes::Dropping unused indexes::Should you name indexes while doing ALTER TABLE ?:
 

21 Comments »

  1. 1. peter

    Actually, The problem is much more complicated than it looks. A while back I did benchmarks and depending on the situation I could get index being more optimal than full table scan even if 70% of rows would be accessed or Full tables can could be faster than retrieving 1% of rows by index – if they all end up in different locations on the disk. So MySQL is not optimal but 20% hard value would not be better ether.

    For wise decision MySQL would need to consider a lot of things including types of IO (seq vs random) cache efficiency, table size relative to memory size etc.

    In general much more complex cost model is required which means serious optimizer overhaul. Such changes are serious step as different optimizer will change a lot of execution plans, and some will surely be changed to worse as no optimizer is perfect in all cases. This makes it scary step besides optimizer being very complex peice of sofware.

    Comment :: June 2, 2006 @ 3:45 pm

  2. 2. Lukas

    According to the oreilly “oracle sql tuning” pocket guide oracle moves to a table scan if it expects to read more than 12% of the rows. supposedly mysql does so at 30%.

    Comment :: June 3, 2006 @ 12:11 am

  3. 3. Vadim

    Well, maybe for Oracle it is 12%, I don’t know for sure, but as Peter said it is not always good to have the hard values, there can be a lot of cases.
    Regarding MySQL: MySQL does not use the selectivity calculation, so it’s impossible to say when MySQL prefers table scan.

    Comment :: June 4, 2006 @ 3:19 am

  4. 4. peter

    One more thing to add – MySQL has to deal with multiple storage engines which complicates things a lot. For example for MEMORY tables there is very small penalty for “random IO” or Innodb tables which have full table scan being scan by primary index.

    Comment :: June 4, 2006 @ 11:24 am

  5. 5. Roy

    Folks, I am new to sql as was looking for some guidance on the following sql select statement

    Select AttributeValue FROM DT_Attribute a WHERE AttributeName = ‘cn’ AND
    ObjectID IN SELECT ObjectID FROM DT_Object o WHERE a.VersionID = o.VersionID
    AND ObjectType = ‘ncpServer’)

    Rows Data Length Index Length
    DT_Attribute 3,243,993 280.4 MB 157.8 MB

    DT_Object 79,828 291.2 MB 5.9 MB

    Running this resulted in 500,000,000 IO reads per hour on a 4-way 3.06 Ghz and 3GB computer.

    Is there a way to analyse such a SELECT statement?
    Is there an explanation why MySQLD-NT was solidly consuming 25% of the CPU rather than asking for more?

    Thanks in advance,

    Roy

    Comment :: September 28, 2006 @ 4:09 am

  6. 6. Mike

    >>Is there an explanation why MySQLD-NT was solidly consuming 25% of the CPU rather than asking for more?

    Comment :: October 1, 2006 @ 8:27 pm

  7. 7. Mike

    ::Is there an explanation why MySQLD-NT was solidly consuming 25% of the CPU rather than asking for more?::

    Sure, it’s because the database is disk bound. The CPU has to wait for the disk I/O to complete. It could mean your disks are slower than they should be. Switching to SCSI drives (if you’re not using them already) may help. Of course if the table was small enough (

    Comment :: October 1, 2006 @ 8:34 pm

  8. 8. peter

    Mike,

    Disk is one possible problem, the other reason (for 25% in particular) would be CPU bound workload using totally one CPU out of 4. Depending on how you set up graphing you may see combined CPU usage or per CPU.

    Comment :: October 3, 2006 @ 3:16 am

  9. 9. Labus

    Possible wrong strategy decision if index strategy is a partial quantity of key strategy, too!?

    Comment :: November 29, 2006 @ 1:20 pm

  10. 10. peter

    Labus,

    I do not understand what do you mean ?

    Comment :: November 29, 2006 @ 3:59 pm

  11. 11. i

    dfgd

    Comment :: May 23, 2007 @ 2:03 am

  12. 12. i

    why dont understand

    Comment :: May 23, 2007 @ 2:04 am

  13. 13. i

    b g g

    Comment :: May 23, 2007 @ 2:04 am

  14. 14. Dinesh

    What is index….pls reply urgent

    Comment :: July 16, 2007 @ 6:21 am

  15. 15. Kishore

    What Is Index ?

    Comment :: June 26, 2008 @ 2:21 am

  16. 16. Vincent

    You know ! the finger just before the middle one…

    Comment :: July 4, 2008 @ 12:54 am

  17. 17. Vijay

    Hello,
    Can you please help me to improve the MYSql Query Performance. Actually we have the data which is using near about 2,00,000 data . so please give me tips to improve this sql performance.

    Thanks in Advance
    Vijay

    Comment :: August 15, 2008 @ 3:00 am

  18. 18. Santiago

    First, i’ve to say Great Post!

    I’ve a Real Estate web app, where some houses can be marked as “Distinguished”, something like “Featured”. So those property is shown in a special way.

    I’d like to look up in the database for all the properties with a given Feature. Supose there’s 3 kinds of feature (eg. Special, Great, Good), and every property has its own. If i’ve 500 properties, my selectivity is about 0,006, then a index wouldn’t be a good choice. But i still want to speed up my search, what can i do? I’ve been thinking to have 3 in-memory arrays containing the ids of the properties. One array for each feature. So, for example, i’d have the array of Special props, and would be like this:
    SpecialsProps = [1,15,52,355,61,123,561].

    Then if i need to search for all the special props, i would perform a “SELECT … WHERE id IN SpecialProps”, and then, the ID Primary Key, Unique Index would be use. But, in this case, doing so, i’d force to look several times for the index and the ids, and wouldn’t be faster than making a full scan (at least, that’s what i think). Another good strategy is, having all the properties cached, i could reference them directly.

    So, to finalize this comment, a simple question. Does MySQL have any index like the BitMap from Oracle?

    Thank you very much!

    Comment :: March 5, 2010 @ 8:20 am

  19. 19. Vadim

    Santiago,

    In simple answer – no, MySQL does not have bitmap indexes.

    Comment :: March 5, 2010 @ 8:25 am

  20. 20. Santiago

    Ok, thanks for your quick answer Vadim. I’m taking a look at your book, it’s seems awesome. I’ll try to get it from Amazon, it’s a little dificult because i’m in Argentina, but i’ll give my best.

    So, do you think i could use one of the strategies i mentioned above?

    Comment :: March 5, 2010 @ 8:27 am

  21. 21. Vadim

    Santiago,

    To decide about strategy – you just benchmark each if them and decide what work better for you.

    Comment :: March 5, 2010 @ 8:33 am

 

Subscribe without commenting

Trackbacks/Pingbacks