Geek Answers Handbook

MySQL: slow avg query for 411M strings

I have a simple table (created by django) - the InnoDB engine:

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(11)          | NO   | PRI | NULL    | auto_increment |
| correlation | double           | NO   |     | NULL    |                |
| gene1_id    | int(10) unsigned | NO   | MUL | NULL    |                |
| gene2_id    | int(10) unsigned | NO   | MUL | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

The table has more than 411 million rows . (The target table will contain about 461M rows, 21471 * 21470 rows)

My main query looks like this: there can be up to 10 genes, indicated at least.

 SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation 
 WHERE gene2_id IN (176829, 176519, 176230) 
 GROUP BY gene1_id ORDER BY NULL

This request is very slow, it takes almost 2 minutes to start:

21471 rows in set (1 min 11.03 sec)

Indices (power looks strange - too little?):

  Non_unique| Key_name                                         | Seq_in_index | Column_name | Collation | Cardinality |
          0 | PRIMARY                                          |            1 | id          | A         |   411512194 | 
          1 | c_gene1_id_6b1d81605661118_fk_genes_gene_entrez  |            1 | gene1_id    | A         |          18 |
          1 | c_gene2_id_2d0044eaa6fd8c0f_fk_genes_gene_entrez |            1 | gene2_id    | A         |          18 |

I just started counting (*) in this table and took 22 minutes:

select count(*) from predictions_genescorrelation;

+-----------+
| count(*)  |
+-----------+
| 411512002 |
+-----------+
1 row in set (22 min 45.05 sec)

What could be wrong? I suspect mysql configuration is not configured correctly.

During data import, I ran into a problem with space, so this could also affect the database, although I ran check tablelater - it took 2 hours and OK was indicated.

- . (254945589,56528,17).

? MySQL? InnoDB, MyISAM?

,

+4
1

https://www.percona.com/blog/2006/12/01/count-for-innodb-tables/

SELECT COUNT(*) WHERE SELECT COUNT(id)... USE INDEX (PRIMARY).

:

 SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation 
 WHERE gene2_id IN (176829, 176519, 176230) 
 GROUP BY gene1_id ORDER BY NULL

(gene2_id, gene1_id, ) .

-: Innodb , ( ). (IS?) https://bugs.mysql.com/bug.php?id=58382

.

+3

More articles:


All Articles