I've got simple query with big IN clause:
SELECT test_id FROM sample WHERE id IN (99 000 of ids);
The explain gives me this result:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE sample range PRIMARY PRIMARY 4 NULL 40 Using where
The id field is primary key of sample table (~320 000 rows) and test_id is foreign key to test table - both are mysql InnoDB tables. Query takes over 2000 secs! I tried to join tables but it took a similar time. After some research i found this topic but the correct answer was only saying what the problem may be (which i don't understand, to be honest :/ ) and there is no solution other than
If these are in cache, the query should run fast
How can i speed up this query? Please be as precise as possible, cause as I found out I'm a optimization novice.
EDIT 1:
SHOW CREATE TABLE sample
CREATE TABLE `sample` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test_id` int(11) NOT NULL,
...
PRIMARY KEY (`id`),
KEY `sample_FI_1` (`test_id`),
... other keys ...,
CONSTRAINT `sample_FK_1` FOREIGN KEY (`test_id`) REFERENCES `test` (`id`),
... other foreign keys ...
) ENGINE=InnoDB AUTO_INCREMENT=315607 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
There was simple join something like this:
SELECT t.* FROM test t JOIN sample s ON t.id = s.test_id JOIN sample_x x ON s.id = x.sample_id WHERE x.field_id = '321' AND x.value LIKE '%smth%';
innodb_buffer_pool_size:
SELECT @ @innodb_buffer_pool_size /1024 /1024 /1024
@@innodb_buffer_pool_size/1024/1024/1024
24.000000000000
Statuses:
SHOW TABLE STATUS FROM zert1442
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
...
sample InnoDB 10 Compact 357323 592 211632128 0 54837248 7340032 315647 2017-02-15 10:22:03 NULL NULL utf8_general_ci NULL
test InnoDB 10 Compact 174915 519 90865664 0 33947648 4194304 147167 2017-02-15 10:22:03 NULL NULL utf8_general_ci NULL
...
SHOW CREATE TABLE sampleoutput in your question.JOINversion of your query, along with theSHOW CREATE TABLEfor any additional tables involved. Likely this can be fixed with an appropriate index.innodb_buffer_pool_size. AlsoSHOW TABLE STATUSso we get a feel for the size of the table.