I have single table query with order by clause. The execution plan of this query is as below:
mysql> explain select barTime,dataDate,closePrice,shortnm from MarketDataTDB.equity_pricemin where datadate >= 20170103 and datadate <= 20170227 and ticker=000001 and exchangecd=‘XSHG’ order by datadate,secOffset;
±—±------------±----------------±------±------------------------------±--------±--------±-----±------±----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±----------------±------±------------------------------±--------±--------±-----±------±----------------------------+
| 1 | SIMPLE | equity_pricemin | range | equity_price_dtticker,cvr_idx | cvr_idx | 9 | NULL | 14026 | Using where; Using filesort |
±—±------------±----------------±------±------------------------------±--------±--------±-----±------±----------------------------+
1 row in set (0.00 sec)
After I use mysqlslamp to concurrently run it with 20 sessions, it ran so slowly, with only 50% performance with same query of Innodb Engine. The table have same data structure and indexes on TokuDB and InnoDB engine.
I saw many "Creating sort index“ and “Queried about 10000 rows” from ‘show processlist’, when I did above query testing.
So my question is how to tuning this ‘sort’?