Hi, I have a perl script that loops through and executes 2 queries 50 times. I need to make sure that I have done all I can to make these queries and the indexing on the table as efficient as possible. Would someone do me the gargantuan favor of taking a peek at the info below and offer any suggestions that may improve things? Thanks! Bryan (apologies for text wrapping making things hard to read :P ) The table: Note: imagequery_3 is actually generated by 'create table select....' where there is a left outer join on two tables, but the selection criteria are the same (chrom and chrompos). I did this because I figured it was faster to avoid the left join and index a normal table on chrompos. mysql> describe imagequery_3; +-----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------+------+-----+---------+-------+ | subsnp_fk | int(11) | | | 0 | | | chrom | char(5) | YES | | NULL | | | locus | char(15) | YES | | NULL | | | chrompos | int(11) | | MUL | 0 | | +-----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> select count(*) as n from imagequery_3; +---------+ | n | +---------+ | 1762834 | +---------+ 1 row in set (0.00 sec) mysql> show index from imagequery_3; +--------------+------------+-----------+--------------+-------------+------ -----+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +--------------+------------+-----------+--------------+-------------+------ -----+-------------+----------+--------+---------+ | imagequery_3 | 1 | chrom_key | 1 | chrompos | A | NULL | NULL | NULL | | +--------------+------------+-----------+--------------+-------------+------ -----+-------------+----------+--------+---------+ 1 row in set (0.00 sec) The Queries (chrompos increments by some precalculated offset for every loop in the perl script): Query 1: select distinct c.subsnp_fk,locus,chrompos from chrom_position_3 c left outer join locus_anno_3 a on c.subsnp_fk=a.subsnp_fk where chrom='01' and chrompos >= 1 and chrompos <= 5202881; Query 2: select count(distinct locus) as n from imagequery_3 where chrom='01' and chrompos >= 1 and chrompos <= 5202881; --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
