I'm auditing the schema of a MySQL database used by a off-the-shelf e-commerce application and I found this as output of SHOW CREATE TABLE thistable:

CREATE TABLE `thistable` 
    `case_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `company_id` int(11) NOT NULL DEFAULT '0',
    `timestamp` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`case_id`),
    KEY `idx_case_id` (`case_id`) USING BTREE

It explicitly adds an index on a primary key.

Is this a blunder of the database designer, or is there actually a reason why is that?

It is worth noting that this table was MyISAM in origin.

  • Essentially no reason. Yet I see it all too often.
    – Rick James
    Mar 30, 2018 at 1:39

2 Answers 2


No, there is clearly no good reason.

Most probably the index originally contained another column that has been dropped - at least, this is the only explanation from the top of my mind.


Here's a couple of reasons when SQL Server will use the index rather than the clustered index. (I'm a SQL Server guy, so I'll leave this to a MySQL person to edit if there's slightly different syntax for MySQL.)


Here, SQL Server chooses to use the narrower index on just case_id because it's faster to scan that than scan the entire table (with all of its associated fields.)

The same thing happens even with a query that doesn't touch case_id:

SELECT SUM(1) FROM thistable

That also uses the narrowest index available for the table, in this case, the one on case_id.

Both of these examples serve not to show good code practices, but just to show scenarios where a database engine will actually choose to use that index. In the case of the second query, I could work around it by having an index on any other field instead (because I probably need an index on a different column anyway.)

  • Thanks for your answer. I'm not sure I understand; here, the clustered index (aka PK) and the secondary index (aka the index) are the same i.e. case_id. Could you please clarify?
    – dr_
    Mar 23, 2018 at 10:54
  • @dr01 the clustered index holds all of the fields on the table, not just the index key. If I gave you a blank pad of paper, and I told you to write down all of the rows, organized by the clustering key - that's what the clustered index is. It has to include all of the table's fields, so your stack of pieces of paper would be very large. Now, on the other hand, if I give you a blank pad of paper and only tell you to write down the IDs - and nothing else - your stack of paper would be very small, and therefore faster to scan.
    – Brent Ozar
    Mar 23, 2018 at 10:57
  • Since case_id is unique (due to being the PRIMARY KEY), DISTINCT is unnecessary in COUNT(DISTINCT case_id). COUNT(*) is the traditional spelling.
    – Rick James
    Mar 30, 2018 at 1:38
  • The "narrowest index" is a valid, though, rare argument.
    – Rick James
    Mar 30, 2018 at 1:40
  • @RickJames thanks - yeah, that was definitely the question, is there ANY reason for that.
    – Brent Ozar
    Mar 30, 2018 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.