Mysql Creating sort index performance for simple frequent query

Question

I'm working on a mysql table with roughly 4 million message entries and I'm trying to select the latest 50 messages based on a timestamp.

An additional requirement is that the returned messages do not start with a fixed prefix.

The problem is that a single query is taking roughly 25% cpu and takes around up to 1.5 seconds. The query is done frequently by multiple clients and is causing performance issues on our 8 core db-server.

SELECT * FROM largeTable 
WHERE msg NOT LIKE 'myPrefix%' 
ORDER BY timestamp DESC LIMIT 0, 50;

I tried profiling with the builtin mysql profiler, here's the result for the query:

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000044 |
| checking permissions | 0.000004 |
| Opening tables       | 0.000010 |
| init                 | 0.000019 |
| System lock          | 0.000005 |
| optimizing           | 0.000005 |
| statistics           | 0.000007 |
| preparing            | 0.000007 |
| Sorting result       | 0.000002 |
| executing            | 0.000002 |
| Sending data         | 0.000006 |
| Creating sort index  | 0.788023 |
| end                  | 0.000009 |
| query end            | 0.000003 |
| closing tables       | 0.000009 |
| freeing items        | 0.000012 |
| cleaning up          | 0.000010 |
+----------------------+----------+

I first thought that maybe the problem is that it checks the prefix for all entries but after the profiling .

| Creating sort index  | 0.788023 |

Seems to be the culprit. So the ORDER BY Clause? How can i speed this up? Is there some type of index i can build to fix this? New messages are added approximately every few seconds, while queries happen a more often.

Thanks for your help!

Edit: Thanks for the comments, here's the info requested.

The database is not being created and filled by my code but by some external python service. I didn't add any indexes yet.

Explain output:

id:1    
select_type:SIMPLE  
table:largeTable 
type:ALL    
possible_keys:NULL  
key: NULL   
key_len:NULL    
ref: NULL   
rows: 3492633   
Extra: Using where; Using filesort

Table Structure:

 CREATE TABLE `largeTable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` int(10) unsigned NOT NULL,
  `client_id` int(11) unsigned NOT NULL,
  `name` varchar(32) NOT NULL,
  `msg` varchar(528) NOT NULL,
  `target_id` int(11) unsigned DEFAULT NULL,
  `target_name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `client` (`client_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4013829 DEFAULT CHARSET=utf8 |

Missing info: output of `EXPLAIN`, output of `SHOW CREATE TABLE`, which engine and if InnoDB - configuration used. As usual, this is most likely an I/O problem caused by insufficient `innod_buffer_pool_size` setting. The default value is `128`mb, you should up that. — Mjh, Aug 10 '16 at 15:27
`show create table largeTable` and publish. Btw the core count is irrelevant. smp on mysql is iffy — Drew, Aug 10 '16 at 15:30
or *consider* putting a composite index on (msg,timestamp) and ponder where that may slow down other stuff. If you already have an index on `msg` (which presumably is wide), tacking on a *thin* timestamp and ditching the other index won't be too painful — Drew, Aug 10 '16 at 15:32
@RyanVincent related, who knows http://stackoverflow.com/a/12033130 — Drew, Aug 10 '16 at 15:38

score 2 · Accepted Answer · answered Aug 13 '16 at 20:48

The EXPLAIN and the CREATE TABLE say that you do not have an index for optimizing the WHERE clause. And that happens before the ORDER BY. So let's focus on indexing first.

ALTER TABLE largeTable
    ADD INDEX(msg);

However, that won't work because of two things:

`msg` varchar(528) NOT NULL,
ENGINE=MyISAM

Do you need 528 characters? If you could lower it to 255, that would work. (Or maybe 341 for MyISAM.)

What version of MySQL are you running? 5.7 allows 528+utf8 to be indexes. 5.6 can do likewise, but you have to do some steps to make it possible.

Mysql Creating sort index performance for simple frequent query

1 Answers1

Linked