比如我查lincRNA和processed_transcript,如下:
select * from lncrna_type inner join search on search.gene_name=lncrna_type.genename where lncrna_type.lnctype='lincRNA' order by plot desc limit 0,10;
所用时间0.63s
执行计划:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+------------------------------+---------------+---------+-------------------------+--------+----------+-----------------------------+
| 1 | SIMPLE | search | NULL | ALL | gene_name_index,gene_index | NULL | NULL | NULL | 739830 | 100.00 | Using where; Using filesort |
| 1 | SIMPLE | lncrna_type | NULL | ref | lnctype_index,lnctype2_index | lnctype_index | 43 | lncrna.search.gene_name | 1 | 48.39 | Using where
select * from lncrna_type inner join search on search.gene_name=lncrna_type.genename where lncrna_type.lnctype='processed_transcript' order by plot desc limit 0,10;
所用时间3.77s
执行计划:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+------------------------------+-----------------+---------+-----------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | lncrna_type | NULL | ref | lnctype_index,lnctype2_index | lnctype2_index | 33 | const | 556 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | search | NULL | ref | gene_name_index,gene_index | gene_name_index | 43 | lncrna.lncrna_type.genename | 130 | 100.00 | NULL
我对每个字段都加了索引,不知道为什么搜索processed_transcript会使用Using temporary; Using filesort,搜索lincRNA会很快。谢谢