0

Finding the first row of a table with an alpha date/time (eg. "20200515.152300") of a particular table is taking a long time. I am using this query:

SELECT path FROM tsFileListTbl WHERE `tsNum` = 18 AND path LIKE "/mnt/das.h%" ORDER BY `dateTime` ASC LIMIT 1;
+----------------------------------+
| path                             |
+----------------------------------+
| /mnt/das.h/18/20200207.204900.ts |
+----------------------------------+
1 row in set (14.32 sec)

Where:

DESCRIBE tsFileListTbl;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| tsNum       | int(11)             | NO   | MUL | NULL    |                |
| path        | varchar(128)        | NO   | UNI |         |                |
| dateTime    | char(15)            | YES  | MUL |         |                |
| endDateTime | char(15)            | YES  |     |         |                |
+-------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.60 sec)

And...

SHOW INDEXES ON tsFileListTbl;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON tsFileListTbl' at line 1
mysql> SHOW INDEX FROM tsFileListTbl;
+---------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tsFileListTbl |          0 | PRIMARY          |            1 | id          | A         |     3159454 |     NULL | NULL   |      | BTREE      |         |
| tsFileListTbl |          0 | path             |            1 | path        | A         |     3159454 |     NULL | NULL   |      | BTREE      |         |
| tsFileListTbl |          1 | dateTimeIdx      |            1 | dateTime    | A         |     1579727 |     NULL | NULL   | YES  | BTREE      |         |
| tsFileListTbl |          1 | tsNumDateIdx     |            1 | tsNum       | A         |          36 |     NULL | NULL   |      | BTREE      |         |
| tsFileListTbl |          1 | tsNumDateIdx     |            2 | dateTime    | A         |     3159454 |     NULL | NULL   | YES  | BTREE      |         |
| tsFileListTbl |          1 | tsNumPathDateIdx |            1 | tsNum       | A         |          36 |     NULL | NULL   |      | BTREE      |         |
| tsFileListTbl |          1 | tsNumPathDateIdx |            2 | path        | A         |     3159454 |     NULL | NULL   |      | BTREE      |         |
| tsFileListTbl |          1 | tsNumPathDateIdx |            3 | dateTime    | A         |     3159454 |     NULL | NULL   | YES  | BTREE      |         |
+---------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (1.41 sec)

Is there anything I could do, without changing the table field definitions, to improve the query time?

Linux / Centos 6 / 32-bit

mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (i386) using readline 5.1

EDIT I

I ran the query directly from mysql to look at different measurements.

show processlist
Query   |  188 | Sending data | SELECT path FROM tsFileListTbl WHERE `tsNum` = 18 AND path LIKE "/mnt/das.h%" ...

In all the query took 4 minutes 20 seconds to complete.

mysql> explain SELECT path FROM tsFileListTbl WHERE `tsNum` = 18 AND path LIKE "/mnt/das.h%" ORDER BY `dateTime` ASC LIMIT 1;
+----+-------------+---------------+-------+------------------------------------+-------------+---------+------+-------+-------------+
| id | select_type | table         | type  | possible_keys                      | key         | key_len | ref  | rows  | Extra       |
+----+-------------+---------------+-------+------------------------------------+-------------+---------+------+-------+-------------+
|  1 | SIMPLE      | tsFileListTbl | index | path,tsNumDateIdx,tsNumPathDateIdx | dateTimeIdx | 46      | NULL | 15931 | Using where |
+----+-------------+---------------+-------+------------------------------------+-------------+---------+------+-------+-------------+

EXPLAIN is new to me. Does the output above mean that the dateTimeIdx was used for the query? If so, that would be the correct index, right?

SELECT COUNT(*) FROM tsFileListTbl;
+----------+
| COUNT(*) |
+----------+
|  2913301 |
+----------+
1 row in set (34.77 sec)

EDIT II

As requested, here is the output from SHOW CREATE TABLE tsFileListTbl:

CREATE TABLE `tsFileListTbl` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tsNum` int(11) NOT NULL,
  `path` varchar(128) NOT NULL DEFAULT '',
  `dateTime` char(15) DEFAULT '',
  `endDateTime` char(15) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `path` (`path`),
  KEY `dateTimeIdx` (`dateTime`),
  KEY `tsNumDateIdx` (`tsNum`,`dateTime`),
  KEY `tsNumPathDateIdx` (`tsNum`,`path`,`dateTime`)
) ENGINE=InnoDB AUTO_INCREMENT=3015594 DEFAULT CHARSET=utf8

EDIT III

One of the indices suggested by Rick James was already there. After adding the other one, the query ran faster, not not that much faster:

mysql> SELECT path FROM tsFileListTbl WHERE `tsNum` = 18 AND path LIKE "/mnt/das.h%" ORDER BY `dateTime` ASC LIMIT 1;
+----------------------------------+
| path                             |
+----------------------------------+
| /mnt/das.h/18/20200208.035900.ts |
+----------------------------------+
1 row in set (2 min 5.44 sec)

mysql> EXPLAIN SELECT path FROM tsFileListTbl WHERE `tsNum` = 18 AND path LIKE "/mnt/das.h%" ORDER BY `dateTime` ASC LIMIT 1;
+----+-------------+---------------+-------+-----------------------------------------------------+-------------+---------+------+---------+-------------+
| id | select_type | table         | type  | possible_keys                                       | key         | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+-------+-----------------------------------------------------+-------------+---------+------+---------+-------------+
|  1 | SIMPLE      | tsFileListTbl | index | path,tsNumDateIdx,tsNumPathDateIdx,tsNumDatePathIdx | dateTimeIdx | 46      | NULL | 2912893 | Using where |
+----+-------------+---------------+-------+-----------------------------------------------------+-------------+---------+------+---------+-------------+
1 row in set (0.02 sec)

CREATE TABLE `tsFileListTbl` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tsNum` int(11) NOT NULL,
  `path` varchar(128) NOT NULL DEFAULT '',
  `dateTime` char(15) DEFAULT '',
  `endDateTime` char(15) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `path` (`path`),
  KEY `dateTimeIdx` (`dateTime`),
  KEY `tsNumDateIdx` (`tsNum`,`dateTime`),
  KEY `tsNumPathDateIdx` (`tsNum`,`path`,`dateTime`),
  KEY `tsNumDatePathIdx` (`tsNum`,`dateTime`,`path`)
) ENGINE=InnoDB AUTO_INCREMENT=3015730 DEFAULT CHARSET=utf8

EDIT IV

I tried the "IGNORE INDEX" as suggested by Rick James. It takes half the time as the previous time but still about 1 minute to find the first row. Is that the best I can expect? I was hoping to find first/last row in the order of milliseconds not seconds...

SELECT path FROM tsFileListTbl IGNORE INDEX(dateTimeIdx) WHERE `tsNum` = 18 AND path LIKE "/mnt/das.h%" ORDER BY `dateTime` ASC LIMIT 1;

+----------------------------------+
| path                             |
+----------------------------------+
| /mnt/das.h/18/20200208.055500.ts |
+----------------------------------+
1 row in set (57.17 sec)

mysql> EXPLAIN SELECT path FROM tsFileListTbl IGNORE INDEX(dateTimeIdx) WHERE `tsNum` = 18 AND path LIKE "/mnt/das.h%" ORDER BY `dateTime` ASC LIMIT 1;
+----+-------------+---------------+-------+-----------------------------------------------------+--------------+---------+------+--------+-------------+
| id | select_type | table         | type  | possible_keys                                       | key          | key_len | ref  | rows   | Extra       |
+----+-------------+---------------+-------+-----------------------------------------------------+--------------+---------+------+--------+-------------+
|  1 | SIMPLE      | tsFileListTbl | range | path,tsNumDateIdx,tsNumPathDateIdx,tsNumDatePathIdx | tsNumDateIdx | 4       | NULL | 125828 | Using where |
+----+-------------+---------------+-------+-----------------------------------------------------+--------------+---------+------+--------+-------------+
1 row in set (0.16 sec)

EDIT V

FLUSH STATUS;
SELECT path FROM tsFileListTbl WHERE `tsNum` = 18 AND path LIKE "/mnt/das.h%" ORDER BY `dateTime` ASC LIMIT 1;
+----------------------------------+
| path                             |
+----------------------------------+
| /mnt/das.h/18/20200208.083300.ts |
+----------------------------------+
1 row in set (10.51 sec)

mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 1       |
| Handler_delete             | 0       |
| Handler_discover           | 0       |
| Handler_prepare            | 0       |
| Handler_read_first         | 1       |
| Handler_read_key           | 1       |
| Handler_read_next          | 1498109 |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_next      | 0       |
| Handler_rollback           | 0       |
| Handler_savepoint          | 0       |
| Handler_savepoint_rollback | 0       |
| Handler_update             | 0       |
| Handler_write              | 0       |
+----------------------------+---------+
15 rows in set (0.46 sec)

EDIT VI

Ignoring all indices except for one improves the performance a lot on the test system. Will try on the production machine.

SELECT path FROM tsFileListTbl IGNORE INDEX(dateTimeIdx, tsNumDateIdx,tsNumPathDateIdx) WHERE `tsNum` = 18 AND path LIKE "/mnt/das.h%" ORDER BY `dateTime` ASC LIMIT 1;
+----------------------------------+
| path                             |
+----------------------------------+
| /mnt/das.h/18/20200209.092000.ts |
+----------------------------------+
1 row in set (0.71 sec)

EXPLAIN  SELECT path FROM tsFileListTbl IGNORE INDEX(dateTimeIdx, tsNumDateIdx,tsNumPathDateIdx) WHERE `tsNum` = 18 AND path LIKE "/mnt/das.h%" ORDER BY `dateTime` ASC LIMIT 1;
+----+-------------+---------------+------+-----------------------+------------------+---------+-------+--------+--------------------------+
| id | select_type | table         | type | possible_keys         | key              | key_len | ref   | rows   | Extra                    |
+----+-------------+---------------+------+-----------------------+------------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | tsFileListTbl | ref  | path,tsNumDatePathIdx | tsNumDatePathIdx | 4       | const | 298080 | Using where; Using index |
+----+-------------+---------------+------+-----------------------+------------------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)

EDIT VII

Taking Rick James' suggestion, to avoid the wildcard search, we added a column partPath and changed app to support to support it.

CREATE TABLE `tsFileListTbl` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tsNum` int(11) NOT NULL,
  `partPath` varchar(20) DEFAULT NULL,
  `path` varchar(128) NOT NULL DEFAULT '',
  `dateTime` char(15) DEFAULT '',
  `endDateTime` char(15) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `path` (`path`),
  KEY `dateTimeIdx` (`dateTime`),
  KEY `tsNumDateIdx` (`tsNum`,`dateTime`),
  KEY `tsNumPathDateIdx` (`tsNum`,`path`,`dateTime`),
  KEY `partPathDateIdx` (`partPath`,`dateTime`)
) ENGINE=InnoDB AUTO_INCREMENT=2906868 DEFAULT CHARSET=utf8

Good news: search time is very very much faster.

SELECT path FROM tsFileListTbl IGNORE INDEX(dateTimeIdx, tsNumDateIdx, tsNumPathDateIdx) WHERE `tsNum` = 12 AND   `partPath` = "/mnt/das.b" ORDER BY `dateTime` ASC LIMIT 1;
+----------------------------------+
| path                             |
+----------------------------------+
| /mnt/das.b/12/20191013.061400.ts |
+----------------------------------+
1 row in set (0.00 sec)

explain SELECT path FROM tsFileListTbl IGNORE INDEX(dateTimeIdx, tsNumDateIdx, tsNumPathDateIdx) WHERE `tsNum` = 23 AND   `partPath` = "/mnt/das.b" ORDER BY `dateTime` ASC LIMIT 1;
+----+-------------+---------------+------+-----------------+-----------------+---------+-------+--------+-------------+
| id | select_type | table         | type | possible_keys   | key             | key_len | ref   | rows   | Extra       |
+----+-------------+---------------+------+-----------------+-----------------+---------+-------+--------+-------------+
|  1 | SIMPLE      | tsFileListTbl | ref  | partPathDateIdx | partPathDateIdx | 63      | const | 991200 | Using where |
+----+-------------+---------------+------+-----------------+-----------------+---------+-------+--------+-------------+

However the query still takes 2-3 seconds if there is no data. ie, no rows match the query. Why would that be? How can I get the same (fast) response regardless of whether there is data or not?

  • using EXPLAIN on your query will provide a lot of info as to why it is so slow, but I'm guess the main issue would be the full text search on path. Sorting for time using a char column doesn't help either - you should have used a DATETIME column. – Guss May 18 at 11:18
  • Your dateTime wouldn't perchance correspond to the name of the file? – Gerard H. Pille May 18 at 11:19
  • Constraints in the application made it difficult to use a DATETIME rather than textual date/time. Yes, the dateTime is the root of the filename but there are various extensions, so like "20200515.231200.ts", ".xx", ".yy", ".zz" etc – Danny May 19 at 1:13
  • 1.5M -- That's half the table?! I added a bunch to my Answer. If ORDER BY path is as good as ORDER BY dateTime, then you have another solution. – Rick James May 23 at 16:37
  • @Danny - as I've discussed in my answer and comments, it looks like the best index would be tsNumDatePathIdx and as long as you can't get the optimizer to use it, you will get bad results. I have not seen it being used in any of your examples. Please note that IGNORE INDEX can accept multiple indexes to ignore! That being said, you should try just forcing one of the indexes using FORCE INDEX (index_name) and try out all of your indexes until you find the one that works best. Also, it could be that your database server has other performance issues, such as insufficient hardware... – Guss May 24 at 6:40
1

Add these two indexes:

INDEX(tsNum, path, datetime)
INDEX(tsNum, datetime, path)

If you want both the first and last, then use UNION ALL.

When showing a schema, please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.

Discussion

For

SELECT path FROM tsFileListTbl
    WHERE `tsNum` = 18 AND path LIKE "/mnt/das.h%"
    ORDER BY `dateTime` ASC LIMIT 1;

INDEX(tsNum, path, datetime)

Here is what is being done:

  • Since the index is "covering", all the work will be done in the index's BTree; no need to look in the data's BTree. EXPLAIN says Using index to indicate "covering".
  • It will drill down the BTree to find the first row with tsNum = 18 AND path LIKE "/mnt/das.h%". This is very fast.
  • Then it will scan forward in the BTree to collect all the rows matching that. In one of your tests, it seems there were 1.5M such rows. Of course, changing the 18 or the LIKE string will change this count, possibly a lot. This probably takes half the time of the query.
  • After that, sort on dateTime -- another half of the time
  • Finally, deliver the first row. Changing the value in the LIMIT won't change the timing much.

The cause of a performance problem can be characterized as "having a 'range' (LIKE '...%') and an unrelated ORDER BY"

Split the path

If path were split into base and rest such that the query were

SELECT base, rest FROM tsFileListTbl
    WHERE `tsNum` = 18
      AND base = "/mnt/das.h"   -- no range
    ORDER BY `dateTime` ASC LIMIT 1;

Then this can handle the entire query extremely fast, regardless of the number of rows with [18,"/mnt/das.h"]. It would need

INDEX(tsNum, base,  -- either order is OK
      datetime,     -- for the order by
      rest)         -- for covering (last)

The query would function thus:

  • Reach into the index BTree for the last row matching tsNum and base, where "last" is given by dateTime. The Handler numbers will be essentially "1".
| improve this answer | |
  • The first index was already there. I added the second. See above for the results. – Danny May 21 at 6:30
  • @Danny - Try this: SELECT path FROM tsFileListTbl IGNORE INDEX(dateTimeIdx) WHERE ... I'm thinking that the Optimizer is using that index when it should not. – Rick James May 21 at 20:58
  • Hi Rick, results are above. It take half the time, but still 1 minute. – Danny May 22 at 2:39
  • @Danny - If you want to discuss this further, please get the Handler counts . (That won't lead to a solution, just an understanding -- maybe.) – Rick James May 22 at 2:48
  • Hi Rick, you've been very helpful. Thanks. I've added the Handler Counts above. Perhaps there is a way to change the schema to improve speed? (of course the application would need to change too). What about splitting the path into two pieces, the /mnt/das.h prefix part and the rest of the path? – Danny May 23 at 13:56
0

I ran some tests, and on my setup - which is different, using MariaDB 10, Docker, 64-bit, but should still map well to the same constraints - the problem seems to be that the database prefers to use the tsNumDateIdx even though it is less effective. After dropping it, the database resorts to tsNumPathDateIdx which speeds up my queries by about 20 times.

I can't explain why the database chooses a slower index - using tsNumDateIdx means you get fast filter only on the tsNum lookup and then the database has to be "Using where" for the path filter which is very slow.

AS mentioned in the comments, in these kinds of situations - EXPLAIN is your friend - if the results look weird, like in my case (see below), then fiddle with your indexes.

  • My tests:
MariaDB [test]> explain select path FROM filelist WHERE `tsNum` = 18 AND path LIKE "/home/odeda/%" ORDER BY `dateTime` ASC LIMIT 1;
+------+-------------+----------+-------+------------------------------------+--------------+---------+------+-------+-------------+
| id   | select_type | table    | type  | possible_keys                      | key          | key_len | ref  | rows  | Extra       |
+------+-------------+----------+-------+------------------------------------+--------------+---------+------+-------+-------------+
|    1 | SIMPLE      | filelist | range | path,tsNumDateIdx,tsNumPathDateIdx | tsNumDateIdx | 4       | NULL | 19534 | Using where |
+------+-------------+----------+-------+------------------------------------+--------------+---------+------+-------+-------------+
1 row in set (0.000 sec)

MariaDB [test]> select count(path) FROM filelist WHERE `tsNum` = 18 AND path LIKE "/home/odeda/%" ORDER BY `dateTime` ASC LIMIT 1;
+-------------+
| count(path) |
+-------------+
|        2607 |
+-------------+
1 row in set (0.165 sec)
MariaDB [test]> alter table filelist drop index tsNumDateIdx;
Query OK, 0 rows affected (0.046 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [test]> explain select path FROM filelist WHERE `tsNum` = 18 AND path LIKE "/home/odeda/Documents%" ORDER BY `dateTime` ASC LIMIT 1;
+------+-------------+----------+-------+-----------------------+------------------+---------+------+-------+------------------------------------------+
| id   | select_type | table    | type  | possible_keys         | key              | key_len | ref  | rows  | Extra                                    |
+------+-------------+----------+-------+-----------------------+------------------+---------+------+-------+------------------------------------------+
|    1 | SIMPLE      | filelist | range | path,tsNumPathDateIdx | tsNumPathDateIdx | 134     | NULL | 27361 | Using where; Using index; Using filesort |
+------+-------------+----------+-------+-----------------------+------------------+---------+------+-------+------------------------------------------+
1 row in set (0.000 sec)

MariaDB [test]> select count(path) FROM filelist WHERE `tsNum` = 18 AND path LIKE "/home/odeda/Documents%" ORDER BY `dateTime` ASC LIMIT 1;
+-------------+
| count(path) |
+-------------+
|        2913 |
+-------------+
1 row in set (0.008 sec)

| improve this answer | |
  • Thank you. I'll investigate using EXPLAIN and see which indices are used in what order. – Danny May 19 at 1:14
  • Be aware: The distribution of the data values can have a big effect on the speed of the queries and whether the Optimizer picks the "right" index. (This is because of two ranges involved (LIKE and ORDER BY). – Rick James May 21 at 21:03
  • In your edits I can see in the explain that the database used the index on the date field. This is useless as you aren't even filtering on that. Use the IGNORE syntax Rick suggested to blacklist indexes until you get good behaviour (then consider removing useless indexes from the table - any index that doesn't speedup queries has negative impact as indexes slow updates) – Guss May 21 at 21:20
  • @Guss - In some situations, the Optimizer will decide to use an INDEX to handle the ORDER BY even when it can't handle the WHERE. This can, however, backfire -- it may have to scan the entire table before it finds any [tsNum,path] pair that matches the WHERE. – Rick James May 23 at 16:35
  • @RickJames Yea, this looks like the case - but unless the where clause will cause a significant portion of the table to be retrieved - making a full table scan needed just for the data retrieval, and I don't think that is the case, there is no reason to do that, and it looks like the optimizer is just wrong. I wonder what edge case is needed to trigger this broken behavior. – Guss May 24 at 6:33

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.