How to improve an already optimized query that takes 18 seconds?

Question

So, I have vps with 512mb ram, and the MySQL table looks like this:

CREATE TABLE `table1` ( `id` int(20) unsigned NOT NULL auto_increment, `ts` timestamp NOT NULL default CURRENT_TIMESTAMP, `value1` char(31) collate utf8_unicode_ci default NULL, `value2` varchar(100) collate utf8_unicode_ci default NULL, `value3` varchar(100) collate utf8_unicode_ci default NULL, `value4` mediumtext collate utf8_unicode_ci, `type` varchar(30) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `type` (`type`), KEY `date` (`ts`) ) ENGINE=MyISAM AUTO_INCREMENT=469692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

If I execute such a request, it takes 2 ~ 18 seconds to complete:

 SELECT `id`, `ts`, `value1`, `value2`, `value3` FROM table1 WHERE `type` = 'something' ORDER BY `id` DESC limit 0,10; 

EXPLAIN SELECT tells me:

  select_type: SIMPLE type: ref possible_keys: type key: type key_len: 92 ref: const rows: 7291 Extra: Using where; Using filesort 

I thought that “using filesort” might be a problem, but this is not the case. If I delete ORDER BY and LIMIT, the query speed is the same (I will disable the query cache for testing with SET @@query_cache_type=0; ).

 mysql> EXPLAIN SELECT `id`,`ts`,`value1`,`value2`, `value3` FROM table1 WHERE `type` = 'something'\G select_type: SIMPLE type: ref possible_keys: type key: type key_len: 92 ref: const rows: 7291 Extra: Using where 

I don’t know if the approximation is inaccurate except for the lines:

 SELECT COUNT(*) FROM table1 WHERE `type` = 'something'; 

Returns 22.8k lines.

The request seems already optimized, I do not know how I could further Improve this. The entire table contains 370 thousand lines and is about 4.6 GB in size. Is it possible that, since the type randomly changes row by row (random distribution throughout the table), it takes 2 ~ 18 seconds to extract data from disk?

The funny thing is, when I use a type that contains only a few hundred lines, these queries are also slow. MySQL returns rows at about 100 rows / second!

 |-------+------+-----------| | count | time | row/sec | |-------+------+-----------| | 22802 | 18.7 | 1219.3583 | | 11 | 0.1 | 110. | | 491 | 4.8 | 102.29167 | | 705 | 5.6 | 125.89286 | | 317 | 2.6 | 121.92308 | |-------+------+-----------| 

Why is it so slow? Can I optimize the query again? Should I move data to smaller tables?

I thought that automatic partitioning would be a good idea to create a new partition for each type dynamically. This is not possible because many reasons include that the maximum number of partitions is 1024, and can be of any type. I could also try the application level partitioning, creating a new table for each new type. I would not want to do this, because it introduces a lot of complexity. I do not know how I can have a unique identifier for all rows in all tables. In addition, if I have reached multiple inserts / seconds, performance will decrease significantly.

Thanks in advance.

Answer 1

For this query, you need an index with several columns:

 KEY `typeid` (`type`, `id`) 

Unfortunately, as you stated, this is also slow, without ORDER, so it slows down because the records are scattered across the disk on the disk, and it has to make a lot of queries. After caching, it should be pretty fast (Note: 22.8 / 370 * 4.6G = 283M, so if you perform other actions / requests, this record will not be in memory for a long time or may not even correspond.).

Make iostat 1 to check for I / O bottleneck. Downloading RAM may solve your problem. SSD can also solve your problem. But RAM is cheaper;)

Answer 2

If you are desperate for optimization, you can try resetting the table. First, you select and order each row from the type and overwrite it in a new table and each time add other types to this table. I suggest some kind of defragment the table, but I have no experience with this.

Answer 3

There are many ways to improve a query. In your case, I see that your index should be huge due to the Unicode VARCHAR (30) indexed column responsible for key_len: 92 . Here you can try: replace the large VARCHAR index with something much smaller. Save the type column, but delete the index and create a new indexed typeidx column, which you can create as INT UNSIGNED (or SMALLINT, if possible).

Create a table like this:

 CREATE TABLE `typetable` ( `typeidx` INT UNSIGNED NOT NULL auto_increment, `type` varchar(30) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`typeidx`), UNIQUE KEY `type` (`type`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

Which you populate with existing types

 INSERT INTO typetable (type) SELECT DISTINCT type FROM table1; 

Then you need to update table1.typeidx with something like

 UPDATE table1 t1 JOIN typetable tt USING (type) SET t1.typeidx = tt.typeidx 

Now your old request may become something like this

 SELECT `id`,`ts`,`value1`,`value2`, `value3` FROM table1 WHERE `typeidx` = (SELECT typeidx FROM typetable WHERE type = 'something') 

Of course, you will also have to support typetable and insert new values ​​from type as they are created.

Answer 4

I have no better idea than to implement vertical splitting. I made an identical table without a mediumtext column, copied the entire table without this column, and an 18 second query will only take 100 ms! New table - only 55 mb.