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?
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)>
Without modifying the schema a bit, as far as I can tell, this is about as fast as it could possibly go.
If you are able to modify the schema, create a locus(id int not null auto_increment primary key, val varchar(15), unique key(val)) table, and replace locus with locus_id in locus_anno_3_blat. Then you would not even need to query locus_anno_3_blat.
-- Sasha Pachev Create online surveys at http://www.surveyz.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
