Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Mysql query almost takes more than 12 minutes though indexing is in place. Please can you throw some light on how to bring down the response time. Thanks

select count(*) 
from IMSI t1 USE INDEX(imsi_index),
StdSubServiceProfile t4 USE INDEX(msisdn_mode),
Auc t3 
where t1.mSubIdentificationNumberId=t3.mSubIdentificationNumberId and    
      t1.mobileNetworkCodeId=t3.mobileNetworkCodeId and 
      t1.mobileCountryCodeId=t3.mobileCountryCodeId and 
      t4.associatedMainMsisdn=t1.mainMSISDN and 
      t3.authenticationSubscriberType=1 and 
      (t4.networkAccessMode=1 or t4.networkAccessMode=2);

EXPLAIN for the above query is :

+----+-------------+-------+--------+---------------+------------+---------+-----------------------------------------------------------------------------------------+---------+-----------------------------------+
| id | select_type | table | type   | possible_keys | key        | key_len | ref                                                                                     | rows    | Extra                             |
+----+-------------+-------+--------+---------------+------------+---------+-----------------------------------------------------------------------------------------+---------+-----------------------------------+
|  1 | SIMPLE      | t4    | ALL    | msisdn_mode   | NULL       | NULL    | NULL                                                                                    | 2374015 | Using where with pushed condition |
|  1 | SIMPLE      | t1    | ref    | imsi_index    | imsi_index | 13      | hlr.t4.associatedMainMsisdn                                                             |      11 | Using where                       |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY       | PRIMARY    | 12      | hlr.t1.mSubIdentificationNumberId,hlr.t1.mobileNetworkCodeId,hlr.t1.mobileCountryCodeId |       1 | Using where                       |
+----+-------------+-------+--------+---------------+------------+---------+-----------------------------------------------------------------------------------------+---------+-----------------------------------+

INDEX for the table t1:

+-------+------------+------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+
| IMSI  |          0 | PRIMARY    |            1 | mSubIdentificationNumberId | NULL      |        NULL |     NULL | NULL   |      | HASH       |         |
| IMSI  |          0 | PRIMARY    |            2 | mobileNetworkCodeId        | NULL      |        NULL |     NULL | NULL   |      | HASH       |         |
| IMSI  |          0 | PRIMARY    |            3 | mobileCountryCodeId        | NULL      |     2374031 |     NULL | NULL   |      | HASH       |         |
| IMSI  |          1 | imsi_index |            1 | mainMSISDN                 | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+

Index for the table t4 :

+----------------------+------------+-------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                | Non_unique | Key_name    | Seq_in_index | Column_name              | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------------+------------+-------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+
| StdSubServiceProfile |          0 | PRIMARY     |            1 | gsmServiceProfileId      | NULL      |        NULL |     NULL | NULL   |      | HASH       |         |
| StdSubServiceProfile |          0 | PRIMARY     |            2 | suMSubscriptionProfileId | NULL      |        NULL |     NULL | NULL   |      | HASH       |         |
| StdSubServiceProfile |          0 | PRIMARY     |            3 | suMSubscriberProfileId   | NULL      |     2374015 |     NULL | NULL   |      | HASH       |         |
| StdSubServiceProfile |          1 | msisdn_mode |            1 | associatedMainMsisdn     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| StdSubServiceProfile |          1 | msisdn_mode |            2 | networkAccessMode        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+----------------------+------------+-------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+
share|improve this question
    
How many rows re returned by this query? –  redguy Jul 18 '13 at 10:36
add comment

1 Answer

As you see from the explain output, mysql does a full table scan on table t4 (rows: 2374015, keys: NULL).

Adding an index just for networkAccessMode will probably help - depending on the value diversity in networkAccessMode.

How does the explain change when you don't specify the indices explicitly?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.