sql优化语句

Question

-- Table "cb2009103_a" DDLCREATE TABLE `cb2009103_a` (
  `ProbeSetName` varchar(255) DEFAULT NULL,
  `Chromosome` varchar(255) DEFAULT NULL,
  `Position` int(11) DEFAULT NULL,
  `CNState` varchar(255) DEFAULT NULL,
  `Log2Ratio` varchar(255) DEFAULT NULL,
  `SmoothSignal` varchar(255) DEFAULT NULL,
  `LOH` varchar(255) DEFAULT NULL,
  `Allele Difference` varchar(255) DEFAULT NULL,
  KEY `pr` (`ProbeSetName`),
  KEY `index_Chromosome` (`Chromosome`),
  KEY `index_Position` (`Position`),
  KEY `index_all` (`Chromosome`,`Position`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- Table "hg18_gene_structure_qp" DDLCREATE TABLE `hg18_gene_structure_qp` (
  `bin` smallint(5) DEFAULT NULL,
  `gene_bank` varchar(255) DEFAULT NULL,
  `chrom` varchar(255) DEFAULT NULL,
  `strand` char(1) DEFAULT NULL,
  `txStart` int(10) DEFAULT NULL,
  `txEnd` int(10) DEFAULT NULL,
  `cds_Start` int(10) DEFAULT NULL,
  `cds_End` int(10) DEFAULT NULL,
  `structure_Start` int(10) DEFAULT NULL,
  `structure_Ends` int(10) DEFAULT NULL,
  `structure_lable` varchar(255) DEFAULT NULL,
  `gene_name` varchar(255) DEFAULT NULL,
  `structure_num` int(10) DEFAULT NULL,
  `qpStart` int(10) unsigned NOT NULL DEFAULT '0',
  `qpEnd` int(10) unsigned NOT NULL DEFAULT '0',
  `qpName` varchar(255) NOT NULL DEFAULT '',
  `gieStain` varchar(255) NOT NULL DEFAULT '',
  KEY `index_chrom` (`chrom`),
  KEY `index_structure_Start` (`structure_Start`),
  KEY `index_structure_Ends` (`structure_Ends`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
需求：如果cb2009103_a.Chromosome=hg18_gene_structure_qp.chrom。
      那么查询出cb2009103_a.position在（structure_Start，structure_Ends）之间的记录。我的SQL语句为：cnv_chip_annotation，cnv_chip分别为数据库名
EXPLAIN
SELECT a.*, b.ProbeSetName,b.Position FROM cnv_chip_annotation.hg18_gene_structure_qp a,cnv_chip.cb2009103_a b
where b.Chromosome=a.chrom
and b.POSITION>a.structure_Start AND 
b.POSITION<a.structure_Ends;由于数据量比较大，都上白万查询效率很低，需要很长时间，请问如果要满足以上要求，
如何优化sql语句？？？

Answer 1

在cb2009103_a表上建立Chromosome、POSITION索引
hg18_gene_structure_qp表上建立chrom、structure_Start、structure_Ends索引
试试

Answer 2

贴出你的EXPLAIN结果看看。
另外用到的关联字段和查询条件字段，加上符合索引，index(a,b)这样。

Answer 3

楼主看来是做semiconduct的。如果两表记录总数差不多，数据分布也差不多的话。你的语句已经没什么可以再改的了。建议你贴出你的
show index from cb2009103_a;
show index from hg18_gene_structure_qp;还有你的
EXPLAIN
SELECT a.*, b.ProbeSetName,b.Position 
FROM cnv_chip_annotation.hg18_gene_structure_qp a,cnv_chip.cb2009103_a b
where b.Chromosome=a.chrom
and b.POSITION>a.structure_Start 
AND b.POSITION<a.structure_Ends;
的结果。

Answer 4

能告诉我，你怎么从这些信息中找到你要的答案？我不知道怎么看，能否指教？
[email protected] : cnv_chip_annotation Wed Nov 03 09:17:27 2010>show index from hg18_gene_structure_qp;
+------------------------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                  | Non_unique | Key_name              | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| hg18_gene_structure_qp |          1 | index_chrom           |            1 | chrom           | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
| hg18_gene_structure_qp |          1 | index_structure_Start |            1 | structure_Start | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
| hg18_gene_structure_qp |          1 | index_structure_Ends  |            1 | structure_Ends  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
+------------------------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)[email protected] : cnv_chip_annotation Wed Nov 03 09:17:34 2010>show index from cnv_chip.cb2009103_a;
+-------------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name         | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| cb2009103_a |          1 | pr               |            1 | ProbeSetName | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
| cb2009103_a |          1 | index_Chromosome |            1 | Chromosome   | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
| cb2009103_a |          1 | index_Position   |            1 | Position     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
| cb2009103_a |          1 | index_all        |            1 | Chromosome   | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
| cb2009103_a |          1 | index_all        |            2 | Position     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
+-------------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)[email protected] : cnv_chip_annotation Wed Nov 03 09:18:15 2010>EXPLAIN
    -> SELECT a.*, b.ProbeSetName,b.Position FROM cnv_chip_annotation.hg18_gene_structure_qp a,cnv_chip.cb2009103_a b
    -> where b.Chromosome=a.chrom
    -> and b.POSITION>a.structure_Start AND  
    -> b.POSITION<a.structure_Ends;
+----+-------------+-------+------+--------------------------------------------------------+------------------+---------+-----------------------------+--------+-------------+
| id | select_type | table | type | possible_keys                                          | key              | key_len | ref                         | rows   | Extra       |
+----+-------------+-------+------+--------------------------------------------------------+------------------+---------+-----------------------------+--------+-------------+
|  1 | SIMPLE      | a     | ALL  | index_chrom,index_structure_Start,index_structure_Ends | NULL             | NULL    | NULL                        | 769809 |             | 
|  1 | SIMPLE      | b     | ref  | index_Chromosome,index_Position,index_all              | index_Chromosome | 258     | cnv_chip_annotation.a.chrom |     10 | Using where | 
+----+-------------+-------+------+--------------------------------------------------------+------------------+---------+-----------------------------+--------+-------------+
2 rows in set (0.00 sec)

Answer 5

a的 chrom ,structure_Start  建联合索引试试

Answer 6

你提醒了我。
from a inner join b
确实是a 全表扫描，b走索引。内循环实现的。不知道该怎么优化了，看狼头哥的高见。

Answer 7

b也是在走索引，但是它为什么不用我的index_all，而用index_chromsome?奇怪