mysqlcomposite-keycompound-key

Mysql key: is single key guaranteed to be faster than compound key?

In the following two scenarios:

create table `Table` (
    `id` int(10),
    `column1` int(10),
    `column2` int(10),
    KEY (`column2`)
);

and

create table `Table` (
    `id` int(10),
    `column1` int(10),
    `column2` int(10),
    KEY (`column1`, `column2`)
);

Now consider the query select * from Table where column2=xxx;

Is there any possibility that the second scenario will be faster than the first scenario, for example in the case where the rows just happen to cluster densely on column1?

Or can we say with 100% certainty that the first scenario is always at least as fast as the second scenario?

I tried searching composite/compound key speed but cannot find answer with 100% certainty when compared to single key.

Solution

  • Is there any possibility that the second scenario will be faster than the first scenario

    Yes.

    This is a scenario when the table statistic is so incorrect that server errorneously uses the index instead of table scan. For example, the statistic shows that approximately 1% of rows contains the value xxx whereas really this is 50%.

    Of course the probability of such situation is extremely low, but it is not zero nevertheless.

    ANALYZE TABLE will fix this issue.