0

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
) ENGINE=InnoDB AUTO_INCREMENT=21688281 DEFAULT CHARSET=utf8

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 '18 at 1:39
1

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.

|improve this answer|||||
0

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

SELECT COUNT(DISTINCT case_id) FROM thistable

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

|improve this answer|||||
  • 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 '18 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 '18 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 '18 at 1:38
  • The "narrowest index" is a valid, though, rare argument. – Rick James Mar 30 '18 at 1:40
  • @RickJames thanks - yeah, that was definitely the question, is there ANY reason for that. – Brent Ozar Mar 30 '18 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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