0

I designed two tables, both using InnoDB.

The first one has columns "Offset, Size, and ColumnToBeIndexed" with BIGINT "Offset" being the primary key, and the second one has columns "OffsetAndSize, and ColumnToBeIndexed" with BIGINT "OffsetAndSize" being the primary key. And there are both 15 millions rows in each table.

I added indexes to both tables on "ColumnToBeIndexed.

My two queries for them are

SELECT Offset, Size 
FROM someTable 
WHERE ColumnToBeIndex BETWEEN 20 AND 10000 
ORDER BY Offset ASC

and

SELECT OffsetAndSize 
FROM someTable 
WHERE ColumnToBeIndex BETWEEN 20 AND 10000 
ORDER BY OffsetAndSize ASC

Because the second query could use the secondary index and does not need to look up the clustered index to find the "size" information, I expected that the second query on the second table performs better than the first query on the first table. However, as the test came out, it turned out that the first query performs better every single time.

Does anybody out there know what seems to be the problem?

Edit:

mysql> SHOW CREATE TABLE Star2002PrimaryTracksOffsetAndSize;
+------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                              | Create Table                                                                                                                                                                                                                                                   |
+------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Star2002PrimaryTracksOffsetAndSize | CREATE TABLE `Star2002PrimaryTracksOffsetAndSize` (
  `offsetAndSize` bigint(20) unsigned NOT NULL,
  `primaryTracks` int(11) DEFAULT NULL,
  PRIMARY KEY (`offsetAndSize`),
  KEY `primaryTracksIndex` (`primaryTracks`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE Star2002PrimaryTracksInnoDB;
+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                       | Create Table                                                                                                                                                                                                                                                                                   |
+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Star2002PrimaryTracksInnoDB | CREATE TABLE `Star2002PrimaryTracksInnoDB` (
  `offsetInByte` bigint(20) unsigned NOT NULL,
  `sizeInByte` smallint(6) DEFAULT NULL,
  `primaryTracks` int(11) DEFAULT NULL,
  PRIMARY KEY (`offsetInByte`),
  KEY `primaryTracksIndex` (`primaryTracks`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select offsetInByte, sizeInByte from Star2002PrimaryTracksInnoDB where primaryTracks between 20 and 10000 order by offset InByte asc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Star2002PrimaryTracksInnoDB
   partitions: NULL
         type: index
possible_keys: primaryTracksIndex
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 15822837
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select offsetAndSize from Star2002PrimaryTracksOffsetAndSize where primaryTracks between 20 and 10000 order by offsetAndSize asc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Star2002PrimaryTracksOffsetAndSize
   partitions: NULL
         type: range
possible_keys: primaryTracksIndex
          key: primaryTracksIndex
      key_len: 5
          ref: NULL
         rows: 7912512
     filtered: 100.00
        Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> select count(offsetInByte) from Star2002PrimaryTracksInnoDB where primaryTracks between 20 and 10000 order by offsetInByte asc; 
+---------------------+
| count(offsetInByte) |
+---------------------+
|             9094926 |
+---------------------+
1 row in set (1.73 sec)
mysql> select count(*) from Star2002PrimaryTracksInnoDB;
+----------+
| count(*) |
+----------+
| 15857625 |
+----------+
1 row in set (1.97 sec)
mysql> select count(offsetAndSize) from Star2002PrimaryTracksOffsetAndSize where primaryTracks between 20 and 10000 order by offsetAndSi ze asc;
+----------------------+
| count(offsetAndSize) |
+----------------------+
|              9094926 |
+----------------------+
1 row in set (1.70 sec)

mysql> select count(*) from Star2002PrimaryTracksOffsetAndSize;
+----------+
| count(*) |
+----------+
| 15857625 |
+----------+
1 row in set (2.09 sec)

mysql Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using EditLine wrapper

5
  • 1
    Your queries selects more than a half of a table. Index will be used only when it matches all conditions fully or is covering (the border when index usage is effective is ~5-10%, when above then table scan is more effective in most cases). In shown case only composite indices may be used effectively.
    – Akina
    Jun 10 '20 at 7:33
  • @Akina Mr. Akina, thank you very much for your great and valuable expertise! You have been of great help! Thank you!
    – Bruce
    Jun 10 '20 at 7:56
  • @Akina Mr. Akina, one more thing. Could you offer me some web page links or materials about this matter? I would like to dive deep into this.
    – Bruce
    Jun 10 '20 at 8:01
  • Here's my cookbook: mysql.rjweb.org/doc.php/index_cookbook_mysql It explains the 20% rule (like Akina's 5-10%), "covering", and why OffsetAndSize mysteriously turned the INDEX(ColumnToBeIndex) into a "covering" index.
    – Rick James
    Jun 11 '20 at 6:47
  • @RickJames Thank you, Mr. James.
    – Bruce
    Jun 21 '20 at 8:13

Your Answer

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

Browse other questions tagged or ask your own question.