对这个查询，我无能为力了

-- Table "cb2009103_a" DDLCREATE TABLE `cb2009103_a` (
  `ProbeSetName` varchar(255) DEFAULT NULL,
  `Chromosome` varchar(255) DEFAULT NULL,
  `Position` int(10) 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_chro` (`Chromosome`),
  KEY `index_position` (`Position`),
  KEY `index_all` (`Chromosome`,`Position`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;数据量：180万-- 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 `a` (`chrom`),
  KEY `b` (`structure_Start`),
  KEY `c` (`structure_Ends`),
  KEY `d` (`chrom`,`structure_Start`,`structure_Ends`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
数据量：80万
root@localhost : test Thu Nov 04 10:31:55 2010>show index from 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         |     1813441 |     NULL | NULL   | YES  | BTREE      |         | 
| cb2009103_a |          1 | index_chro     |            1 | Chromosome   | A         |          24 |     NULL | NULL   | YES  | BTREE      |         | 
| cb2009103_a |          1 | index_position |            1 | Position     | A         |     1813441 |     NULL | NULL   | YES  | BTREE      |         | 
| cb2009103_a |          1 | index_all      |            1 | Chromosome   | A         |          24 |     NULL | NULL   | YES  | BTREE      |         | 
| cb2009103_a |          1 | index_all      |            2 | Position     | A         |     1813441 |     NULL | NULL   | YES  | BTREE      |         | 
+-------------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)root@localhost : test Thu Nov 04 10:32:19 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 | a        |            1 | chrom           | A         |          24 |     NULL | NULL   | YES  | BTREE      |         | 
| hg18_gene_structure_qp |          1 | b        |            1 | structure_Start | A         |      384904 |     NULL | NULL   | YES  | BTREE      |         | 
| hg18_gene_structure_qp |          1 | c        |            1 | structure_Ends  | A         |      384904 |     NULL | NULL   | YES  | BTREE      |         | 
| hg18_gene_structure_qp |          1 | d        |            1 | chrom           | A         |          24 |     NULL | NULL   | YES  | BTREE      |         | 
| hg18_gene_structure_qp |          1 | d        |            2 | structure_Start | A         |      384904 |     NULL | NULL   | YES  | BTREE      |         | 
| hg18_gene_structure_qp |          1 | d        |            3 | structure_Ends  | A         |      384904 |     NULL | NULL   | YES  | BTREE      |         | 
+------------------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)root@localhost : test Thu Nov 04 10:33:03 2010>explain SELECT a.*, b.ProbeSetName,b.Position FROM cb2009103_a b,hg18_gene_structure_qp a where b.Chromosome=a.chrom and b.POSITION between a.structure_start and a.structure_ends;
+----+-------------+-------+------+-------------------------------------+------------+---------+--------------+--------+-------------+
| id | select_type | table | type | possible_keys                       | key        | key_len | ref          | rows   | Extra       |
+----+-------------+-------+------+-------------------------------------+------------+---------+--------------+--------+-------------+
|  1 | SIMPLE      | a     | ALL  | a,b,c,d                             | NULL       | NULL    | NULL         | 769809 |             | 
|  1 | SIMPLE      | b     | ref  | index_chro,index_position,index_all | index_chro | 258     | test.a.chrom |  75560 | Using where | 
+----+-------------+-------+------+-------------------------------------+------------+---------+--------------+--------+-------------+
2 rows in set (0.00 sec)
只能这样了吗？

解决方案 »

  1.   

    在cb2009103_a上建立Chromosome、POSITION
    在hg18_gene_structure_qp上建立chrom、structure_start、structure_ends建立复合索引试试
      

  2.   

    使用FORCE INDEX强制使用index_all索引。
      

  3.   

    SELECT a.*, b.ProbeSetName,b.Position FROM cb2009103_a b,hg18_gene_structure_qp a where b.Chromosome=a.chrom and b.POSITION between a.structure_start and a.structure_ends;
    试试看这条语句是否可以拆分成几条命令来完成？
      

  4.   

    貌似我只建立一个index_all索引，它会使用。结果如下：root@localhost : test Thu Nov 04 10:09:17 2010>explain SELECT a.*, b.ProbeSetName,b.Position FROM hg18_gene_structure_qp a,cb2009103_a b where b.Chromosome=a.chrom and b.POSITION between a.structure_start and a.structure_ends;
    +----+-------------+-------+------+------------------------------+-----------+---------+--------------+--------+-------------+
    | id | select_type | table | type | possible_keys                | key       | key_len | ref          | rows   | Extra       |
    +----+-------------+-------+------+------------------------------+-----------+---------+--------------+--------+-------------+
    |  1 | SIMPLE      | a     | ALL  | index_start_end,a,b,c        | NULL      | NULL    | NULL         | 769809 |             | 
    |  1 | SIMPLE      | b     | ref  | index_all,index_chr,index_po | index_all | 258     | test.a.chrom |  75560 | Using where | 
    +----+-------------+-------+------+------------------------------+-----------+---------+--------------+--------+-------------+
    2 rows in set (0.02 sec)分析结果还是一样。769809*75560这也太大了吧。
      

  5.   


    这个是关于两个表连接的sql，我不知道怎样拆了？请指教
      

  6.   

    between a.structure_start and a.structure_ends改为变量赋值