MySql - Further Query optimization SELECT WHERE IN

Question

I am currently working on a financial application. A MYSql Database with 8 columns. 1 Million records.

INSERTS : 30 rows per minute.
UPDATES : 0.
SELECTS : A query that runs once every 200ms. Returns around 200,000 records for each execution.

Select Query as follows:

SELECT COLUMNB, COLUMNC, COLUMND, COLUMNE FROM TABLE
WHERE COLUMNE IN('ABCD','EFGH','IJKL','MNOP')
ORDER BY COLUMNB DESC

my response times are as follows: Execution :0.903 Seconds Fetch : 0.2 Seconds.

Total ~ 1.1 seconds

When i do an Explain I get the following:

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra '1', 'SIMPLE', 'table', NULL, 'ALL', 'tbl_ColumnE_ColumnB,tbl_ColumnE', NULL, NULL, NULL, '1180834', '34.33', 'Using where; Using filesort'

INDEXES already active:

tbl_ColumnE_ColumnB,
tbl_ColumnB_ColumnE,
tbl_ColumnE
tbl_ColumnB

As this is a financial Stock market based application. I need to get total execution time further down to close to 200milliseconds or lesser as possible. Is there anything i can improve?

Already done :

innodb_buffer_pool increase from 8M to 2000M (saved 30%)
Changed OR operator to IN operator ( it was OR before. Saved 20%)

Show Create Table :

CREATE TABLE `tbl` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `COLUMNB` timestamp NULL DEFAULT NULL, 
    `COLUMNC` decimal(20,10) DEFAULT NULL, 
    `COLUMND` decimal(20,10) DEFAULT NULL, 
    `COLUMNX` decimal(20,10) DEFAULT NULL, 
    `COLUMNY` decimal(20,10) DEFAULT NULL, 
    `COLUMNZ` decimal(20,10) DEFAULT NULL, 
    `COLUMNE` varchar(45) DEFAULT NULL, 
    `COLUMNF` int(11) DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `id_UNIQUE` (`id`), 
    KEY `tbl_ColumnE_ColumnB` (`ColumnE`,`ColumnB`), 
    KEY `tbl_ColumnB_ColumnE` (`ColumnB`,`ColumnE`), 
    KEY `tbl_ColumnB` (`ColumnB`), 
    KEY `tbl_ColumnE` (`ColumnE`)
) ENGINE=InnoDB AUTO_INCREMENT=1718507 DEFAULT CHARSET=utf8'

The query do not use a index : you must create a composite index over two field (COLUMNE, COLUMNB) and then send the explain again — Bernd Buffen, Feb 21 '16 at 12:23
@sagi Yes absolutely. I need to start processing in reverse order straight away once i get this results. — Metaplace, Feb 21 '16 at 12:27
sorry the optimizer does not use them. please post show create table TABLE — Bernd Buffen, Feb 21 '16 at 12:28
Try to force mysql to use some of the indexes: http://dev.mysql.com/doc/refman/5.7/en/index-hints.html — mitkosoft, Feb 21 '16 at 13:46
Dont do that!!! thats not the best way !! When you force to use a index the optimezer cant optimize the query and when they change the optimizer you dont get the changes. — Bernd Buffen, Feb 21 '16 at 14:45

score 0 · Answer 1 · answered Feb 21 '16 at 14:02

This query:

SELECT COLUMNB, COLUMNC, COLUMND, COLUMNE
FROM TABLE
WHERE COLUMNE IN ('ABCD', 'EFGH', 'IJKL', 'MNOP')
ORDER BY COLUMNB DESC;

Has no convenient method for optimization. The problem is the IN and ORDER BY. For a single value in the IN list, the optimal index might be (COLUMNE, COLUMNB, COLUMNC, COLUMND) (descending sorts in MySQL can be tricky).

If the four values in the IN list are constant, I would recommend changing the data structure so they are in one column. Then this query:

SELECT COLUMNB, COLUMNC, COLUMND, COLUMNE
FROM TABLE
WHERE COLUMNEPRIME = 'ABCDEFGHIJKLMNOP'
ORDER BY COLUMNB DESC;

can take advantage of an index on (COLUMNEPRIME, COLUMNB, COLUMNC, COLUMND, COLUMNE). Alas, in MySQL, that might require the use of a trigger which would slow down the inserts.

If the fetch is so quick, you might find it faster to sort in the application rather than the database. So, try the query:

SELECT COLUMNB, COLUMNC, COLUMND, COLUMNE
FROM TABLE
WHERE COLUMNE IN ('ABCD', 'EFGH', 'IJKL', 'MNOP');

With the four-part composite index.

I do note that returning 20% of a table multiple times per second seems unnecessary for most purposes. Perhaps there are better ways of designing the overall system to do what you need.

score 0 · Answer 2 · answered Feb 21 '16 at 14:48

Try this. How many different data do you have in the COLUMNE? If it not so mutch you can also use ENUMs to speed up the query. You also can use the compress option in the Client to minimize the transfer of data.

CREATE TEMPORARY TABLE `tbls` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `val` VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `val` (`val`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbls (val) VALUES('ABCD'),('EFGH'),('IJKL'),('MNOP');

EXPLAIN
 SELECT COLUMNB, COLUMNC, COLUMND, COLUMNE FROM tbl
 INNER JOIN tbls ON tbl.COLUMNE = tbls.val
 ORDER BY COLUMNB DESC;

score 0 · Answer 3 · answered Feb 21 '16 at 18:56

What percent of row match the IN? If it is more than something like 20%, the optimizer will shun the index (starting with E) because it is likely to be faster to scan the entire table.

A Sort ("filesort") is required because there is no way to fetch the rows in the correct order. You could experiment with this by trying FORCE INDEX(ColumnB).

Part of the reason for the query taking so long is that you are returning 200K rows. Ponder whether you can avoid that.

But wait! If you are inserting only one row every 2 seconds, why do you think anything will change after 0.2s? When you insert a row, trigger the recalculation (this connection has to be done in the app, can't be done in totally in sql). That will lead to faster response, since you won't be waiting up to 0.2s the next calculation.

MySql - Further Query optimization SELECT WHERE IN

3 Answers3