CODEWITHSUNDEEP
mysqlsql
Hayi
Hayi

Reputation: 6236

Count query take much time

I have i have 61K rows in my database mysql and I try to make a count but the query take 4 seconds and i think it's too much.

select count( distinct appeloffre0_.ID_APPEL_OFFRE) 
from ao.appel_offre appeloffre0_ 
inner join ao.lot lots1_ on appeloffre0_.ID_APPEL_OFFRE=lots1_.ID_APPEL_OFFRE 
inner join ao.lieu_execution lieuexecut2_ on appeloffre0_.ID_APPEL_OFFRE=lieuexecut2_.appel_offre 
inner join ao.acheteur acheteur3_ on appeloffre0_.ID_ACHETEUR=acheteur3_.ID_ACHETEUR 
inner join ao.ao_activite aoactivite4_ on appeloffre0_.ID_APPEL_OFFRE=aoactivite4_.ID_APPEL_OFFRE

my query result :

+----------------------------------------------+
| count( distinct appeloffre0_.ID_APPEL_OFFRE) |
+----------------------------------------------+
|                                        61100 |
+----------------------------------------------+
1 row in set (4.35 sec)

why in explain cmd table appeloffre0_ use key appel_offre_ibfk_2 which is a index on FK column ID_ACHETEUR ?

+----+-------------+--------------+--------+----------------------------+--------------------+---------+--------------------------------+-------+--------------------------+
| id | select_type | table        | type   | possible_keys              | key                | key_len | ref                            | rows  | Extra                    |
+----+-------------+--------------+--------+----------------------------+--------------------+---------+--------------------------------+-------+--------------------------+
|  1 | SIMPLE      | appeloffre0_ | index  | PRIMARY,appel_offre_ibfk_2 | appel_offre_ibfk_2 | 4       | NULL                           | 60031 | Using index              |
|  1 | SIMPLE      | acheteur3_   | eq_ref | PRIMARY                    | PRIMARY            | 4       | ao.appeloffre0_.ID_ACHETEUR    |     1 | Using index              |
|  1 | SIMPLE      | lieuexecut2_ | ref    | fk_ao_lieuex               | fk_ao_lieuex       | 4       | ao.appeloffre0_.ID_APPEL_OFFRE |     1 | Using index              |
|  1 | SIMPLE      | aoactivite4_ | ref    | ao_activites_ao_fk         | ao_activites_ao_fk | 4       | ao.lieuexecut2_.appel_offre    |     3 | Using where; Using index |
|  1 | SIMPLE      | lots1_       | ref    | FK_LOT_AO                  | FK_LOT_AO          | 4       | ao.lieuexecut2_.appel_offre    |     5 | Using where; Using index |
+----+-------------+--------------+--------+----------------------------+--------------------+---------+--------------------------------+-------+--------------------------+
5 rows in set (0.00 sec)

show index from appel_offre

+-------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name           | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| appel_offre |          0 | PRIMARY            |            1 | ID_APPEL_OFFRE      | A         |       60953 |     NULL | NULL   |      | BTREE      |         |               |
| appel_offre |          1 | appel_offre_ibfk_1 |            1 | ID_APPEL_OFFRE_MERE | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| appel_offre |          1 | appel_offre_ibfk_2 |            1 | ID_ACHETEUR         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| appel_offre |          1 | appel_offre_ibfk_3 |            1 | USER_SAISIE         | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| appel_offre |          1 | appel_offre_ibfk_4 |            1 | USER_VALIDATION     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| appel_offre |          1 | ao_fk_3            |            1 | TYPE_MARCHE         | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| appel_offre |          1 | ao_fk_5            |            1 | USER_CONTROLE       | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.03 sec)

  1. how can i use index to run fast count query ?

  2. how to use index when we have multiple join ?

  3. how to use index when we have multiple join and multiple search query ?

Upvotes: 0

Views: 69

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

Instead of using distinct in your count try replacing your 1 to many inner joins with exists conditions.

For example, if lot.ID_APPEL_OFFRE is not unique, then remove the inner join

inner join ao.lot lots1_ on appeloffre0_.ID_APPEL_OFFRE=lots1_.ID_APPEL_OFFRE

and add an exists condition in your where clause

where exists (select 1 from ao.lot lots_1 where appeloffre0_.ID_APPEL_OFFRE=lots1_.ID_APPEL_OFFRE)

Upvotes: 2

Related Questions