Indexes in MySQL

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

;
250001 (V1)

;
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:

That is MySQL will use index for this query.

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

– 410 ms

– 200 ms

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

Consider more extremal case: selectivity ~95%:

0.9492 = 94.92%

Explain still claims MySQL will use index.

Execution time:

– 1200 ms

– 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.

Share this post

Comments (27)

  • kumars

    I Just have numbers and marks of the 50,000 students but sql query took long to respond why

    June 2, 2006 at 12:00 am
  • balaji

    Dear Pradeep,

    Try to select restricted data from the table.
    Because you can not show all 4 lacs of records at a time on the page,
    possibly use LIMIT or do pagination to your page.
    Also while selecting the data avoid using LIKE operator, search by = operator instead.

    Thanks,
    Balaji

    June 2, 2006 at 12:00 am
  • 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.

    June 2, 2006 at 3:45 pm
  • 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%.

    June 3, 2006 at 12:11 am
  • 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.

    June 4, 2006 at 3:19 am
  • 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.

    June 4, 2006 at 11:24 am
  • 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

    September 28, 2006 at 4:09 am
  • Mike

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

    October 1, 2006 at 8:27 pm
  • 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 (

    October 1, 2006 at 8:34 pm
  • 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.

    October 3, 2006 at 3:16 am
  • Labus

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

    November 29, 2006 at 1:20 pm
  • peter

    Labus,

    I do not understand what do you mean ?

    November 29, 2006 at 3:59 pm
  • i

    dfgd

    May 23, 2007 at 2:03 am
  • i

    why dont understand

    May 23, 2007 at 2:04 am
  • i

    b g g

    May 23, 2007 at 2:04 am
  • Dinesh

    What is index….pls reply urgent

    July 16, 2007 at 6:21 am
  • Kishore

    What Is Index ?

    June 26, 2008 at 2:21 am
  • Vincent

    You know ! the finger just before the middle one…

    July 4, 2008 at 12:54 am
  • 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

    August 15, 2008 at 3:00 am
  • Santiago

    First, i’