MySQL Forums :: Performance :: Slow query on very large table


Advanced Search

Slow query on very large table
Posted by: Gu Z ()
Date: April 16, 2008 08:54AM

Hi,
I have a very large table (affy_all, details see below) and a simple query on an indexed column takes over an hour.
I don't expect it to be really fast but is it normal to be that slow? Is the problem the bad cardinality of the index (although the shown number is high but that's due to the many NULL values I assume).
Is there anything I can do to improve that performance?

The values for chrom are '1' to '23', 'X' and NULL.

mysql> select count(Affy_ID) from affy_all where chrom='15';
+----------------+
| count(Affy_ID) |
+----------------+
| 1673124 |
+----------------+
1 row in set (1 hour 27 min 39.10 sec)

mysql> explain select count(Affy_ID) from affy_all where chrom='15';
+----+-------------+----------+------+---------------+-----------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+-----------+---------+-------+---------+-------------+
| 1 | SIMPLE | affy_all | ref | ind_chrom | ind_chrom | 3 | const | 2658602 | Using where |
+----+-------------+----------+------+---------------+-----------+---------+-------+---------+-------------+
1 row in set (0.00 sec)


However, if I count for 'chrom is null' it takes only three to four minutes. Somehow I would have expected that it would take longer due to the larger number for 'rows' in explain?

mysql> select count(Affy_ID) from affy_all where chrom is null;
+----------------+
| count(Affy_ID) |
+----------------+
| 44136356 |
+----------------+
1 row in set (3 min 38.88 sec)

mysql> explain select count(Affy_ID) from affy_all where chrom is null;
+----+-------------+----------+------+---------------+-----------+---------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+-----------+---------+-------+----------+-------------+
| 1 | SIMPLE | affy_all | ref | ind_chrom | ind_chrom | 3 | const | 12658348 | Using where |
+----+-------------+----------+------+---------------+-----------+---------+-------+----------+-------------+


thanks,
Gu

Here the table details:
mysql> show table status like 'affy_all';
+----------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------------------------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------------------------------+---------+
| affy_all | MyISAM | 9 | Dynamic | 101820637 | 94 | 9598685124 | 1099511627775 | 2751949824 | 0 | NULL | 2008-04-16 15:02:53 | 2008-04-16 15:13:34 | 2008-04-16 15:56:46 | latin1_swedish_ci | NULL | max_rows=1215752192 avg_row_length=200 | |
+----------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------------------------------+---------+

mysql> show index from affy_all;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| affy_all | 1 | ind_affyid | 1 | Affy_ID | A | 934134 | NULL | NULL | | BTREE | |
| affy_all | 1 | ind_chrom | 1 | chrom | A | 50910318 | NULL | NULL | YES | BTREE | |
| affy_all | 1 | ind_chrom | 2 | chr_pos | A | 50910318 | NULL | NULL | YES | BTREE | |
| affy_all | 1 | ind_score | 1 | score | A | 3511056 | NULL | NULL | YES | BTREE | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


mysql> show create table affy_all;

| affy_all | CREATE TABLE `affy_all` (
`Affy_ID` varchar(14) NOT NULL default '',
`dbSNP` int(11) default NULL,
`gnt` tinyint(4) default NULL,
`allele_bases` char(2) default NULL,
`person` varchar(50) NOT NULL default '',
`file` varchar(20) default NULL,
`score` float default NULL,
`strand` char(1) default NULL,
`strand_vs_dbSNP` varchar(20) default NULL,
`flank` varchar(80) default NULL,
`in_hapmap` char(1) default NULL,
`gender` char(1) default NULL,
`chrom` char(2) default NULL,
`ctg_acc` varchar(20) default NULL,
`ctg_ID` varchar(22) default NULL,
`ctg_pos` int(10) unsigned default NULL,
`chr_pos` int(10) unsigned default NULL,
`local_loci` varchar(45) default NULL,
`avg_het` float default NULL,
`validated` smallint(2) default NULL,
`last_build` smallint(3) default NULL,
KEY `ind_affyid` (`Affy_ID`),
KEY `ind_chrom` (`chrom`,`chr_pos`),
KEY `ind_score` (`score`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1215752192 AVG_ROW_LENGTH=200 |

Options: ReplyQuote


Subject Views Written By Posted
Slow query on very large table 4180 Gu Z 04/16/2008 08:54AM
Re: Slow query on very large table 1762 Rick James 05/28/2008 09:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.