Posted this yesterday, but it never showed up...? Hi,
I have the following table (sorry if the formatting gets tweaked) and query. The table is around 36k rows long, and the query returns about 350 rows. The query takes 12.15 seconds. Is there any way I can speed this up? Thanks, Bryan mysql> desc locus_anno_3_blat; +-------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+-------+ | subsnp_fk | int(11) unsigned | | MUL | 0 | | | locus | varchar(15) | | MUL | | | | locus_orig | varchar(15) | | MUL | | | | locusid | int(11) | | MUL | 0 | | | snptype | varchar(30) | YES | MUL | NULL | | | allele | char(1) | YES | | NULL | | | frame | smallint(6) | YES | | NULL | | | residue | char(1) | YES | | NULL | | | aa_position | int(11) | YES | | NULL | | | locusChrom | varchar(20) | YES | MUL | NULL | | | locusStart | int(11) unsigned | YES | | NULL | | | locusEnd | int(11) unsigned | YES | | NULL | | +-------------+------------------+------+-----+---------+-------+ 12 rows in set (0.00 sec) mysql> show index from locus_anno_3_blat; +-------------------+------------+-----------------+--------------+--------- ----+-----------+-------------+----------+--------+------+------------+----- ----+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------------+------------+-----------------+--------------+--------- ----+-----------+-------------+----------+--------+------+------------+----- ----+ | locus_anno_3_blat | 1 | fk_ix | 1 | subsnp_fk | A | 2308025 | NULL | NULL | | BTREE | | | locus_anno_3_blat | 1 | name_ix | 1 | locus_orig | A | 21570 | NULL | NULL | | BTREE | | | locus_anno_3_blat | 1 | locusid_ix | 1 | locusid | A | 25644 | NULL | NULL | | BTREE | | | locus_anno_3_blat | 1 | chrs_ix | 1 | locusChrom | A | 25 | 3 | NULL | YES | BTREE | | | locus_anno_3_blat | 1 | chrs_ix | 2 | locusStart | A | 25362 | NULL | NULL | YES | BTREE | | | locus_anno_3_blat | 1 | chre_ix | 1 | locusChrom | A | 25 | 3 | NULL | YES | BTREE | | | locus_anno_3_blat | 1 | chre_ix | 2 | locusEnd | A | 25362 | NULL | NULL | YES | BTREE | | | locus_anno_3_blat | 1 | locus_ix | 1 | locus | A | 25087 | 10 | NULL | | BTREE | | | locus_anno_3_blat | 1 | l_a_3_b_snptype | 1 | snptype | A | 9 | NULL | NULL | YES | BTREE | | +-------------------+------------+-----------------+--------------+--------- ----+-----------+-------------+----------+--------+------+------------+----- ----+ 9 rows in set (0.00 sec) And I am trying to do the following simple query: select distinct locus from locus_anno_3_blat where locus like 'A%' order by locus; mysql> explain select distinct locus from locus_anno_3_blat where locus like 'A%' order by locus; +-------------------+-------+---------------+----------+---------+------+--- -----+---------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------------+-------+---------------+----------+---------+------+--- -----+---------------------------------------------+ | locus_anno_3_blat | range | locus_ix | locus_ix | 10 | NULL | 117253 | where used; Using temporary; Using filesort | +-------------------+-------+---------------+----------+---------+------+--- -----+---------------------------------------------+ 1 row in set (0.04 sec)> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
