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.

I have this query (below) which keeps hitting the table when it should be using the created index. Is there something simple I'm missing here? How can I get it to use the index instead of the table?

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.27-log |
+------------+

    mysql> explain SELECT `Lead`.`subid` AS `subid` FROM `leads` AS `Lead` WHERE `Lead`.`is_live` = '1' AND `Lead`.`created` >= '2012-12-13 00:00:00' AND `Lead`.`created` <= '2012-12-13 23:59:59' AND NOT (`Lead`.`subid` IS NULL) GROUP BY `Lead`.`subid`;
+----+-------------+-------+-------+-----------------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type  | possible_keys         | key     | key_len | ref  | rows  | Extra                                        |
+----+-------------+-------+-------+-----------------------+---------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | Lead  | range | is_live,subid,created | created | 8       | NULL | 86390 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+-----------------------+---------+---------+------+-------+----------------------------------------------+
1 row in set (0.01 sec)

Indexes:

    mysql> show indexes from leads;
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| leads |          0 | PRIMARY         |            1 | id              | A         |     2375473 |     NULL | NULL   |      | BTREE      |         |               |
| leads |          1 | id              |            1 | id              | A         |     2375473 |     NULL | NULL   |      | BTREE      |         |               |
| leads |          1 | client_id       |            1 | client_id       | A         |         559 |     NULL | NULL   | YES  | BTREE      |         |               |
| leads |          1 | is_live         |            1 | is_live         | A         |         444 |     NULL | NULL   | YES  | BTREE      |         |               |
| leads |          1 | sold            |            1 | sold            | A         |          19 |     NULL | NULL   |      | BTREE      |         |               |
| leads |          1 | amount          |            1 | amount          | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
| leads |          1 | subid           |            1 | subid           | A         |         587 |     NULL | NULL   | YES  | BTREE      |         |               |
| leads |          1 | client_url      |            1 | client_url      | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
| leads |          1 | originating_url |            1 | originating_url | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
| leads |          1 | ping_id         |            1 | ping_id         | A         |          19 |     NULL | NULL   |      | BTREE      |         |               |
| leads |          1 | network_id      |            1 | network_id      | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
| leads |          1 | group_id        |            1 | group_id        | A         |         565 |     NULL | NULL   | YES  | BTREE      |         |               |
| leads |          1 | is_completed    |            1 | is_completed    | A         |          19 |     NULL | NULL   |      | BTREE      |         |               |
| leads |          1 | uid             |            1 | uid             | A         |     2375473 |     NULL | NULL   |      | BTREE      |         |               |
| leads |          1 | redirected      |            1 | redirected      | A         |          19 |     NULL | NULL   |      | BTREE      |         |               |
| leads |          1 | created         |            1 | created         | A         |     2375473 |     NULL | NULL   |      | BTREE      |         |               |
| leads |          1 | first_name      |            1 | first_name      | A         |      339353 |     NULL | NULL   |      | BTREE      |         |               |
| leads |          1 | last_name       |            1 | last_name       | A         |       2375473 |     NULL | NULL   |      | BTREE      |         |               |
| leads |          1 | email           |            1 | email           | A         |     2375473 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
19 rows in set (0.14 sec)

Table:

CREATE TABLE `leads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  `client_ip` char(16) NOT NULL,
  `loan_amount` char(7) NOT NULL DEFAULT '',
  `gender` char(1) DEFAULT NULL,
  `salutation` char(5) DEFAULT NULL,
  `first_name` varchar(100) NOT NULL DEFAULT '',
  `last_name` varchar(100) NOT NULL DEFAULT '',
  `email` varchar(200) NOT NULL,
  `alternate_email` varchar(100) DEFAULT NULL,
  `date_of_birth` datetime NOT NULL,
  `address1` varchar(100) NOT NULL,
  `address2` varchar(100) NOT NULL,
  `city` varchar(30) NOT NULL,
  `state` char(2) NOT NULL,
  `zip` varchar(10) NOT NULL,
  `country_code` varchar(10) NOT NULL,
  `phone_home` varchar(15) NOT NULL,
  `phone_work` varchar(15) NOT NULL,
  `phone_work_extension` varchar(10) NOT NULL,
  `mobile` varchar(20) NOT NULL,
  `fax` varchar(10) NOT NULL,
  `is_military` char(1) NOT NULL,
  `drivers_license_state` char(2) DEFAULT NULL,
  `drivers_license_number` varchar(20) DEFAULT NULL,
  `residence_type` varchar(10) NOT NULL,
  `own_home` char(5) NOT NULL DEFAULT '0',
  `months_at_address` char(5) NOT NULL,
  `years_at_address` char(5) DEFAULT NULL,
  `monthly_residence_cost` double(6,2) DEFAULT NULL,
  `income_type` varchar(100) NOT NULL,
  `occupation` varchar(100) NOT NULL,
  `job_title` varchar(100) NOT NULL,
  `pay_per_period` double(6,2) NOT NULL DEFAULT '0.00',
  `monthly_income` double(6,2) NOT NULL DEFAULT '0.00',
  `pay_frequency` varchar(20) NOT NULL,
  `pay_day1` datetime NOT NULL,
  `pay_day2` datetime NOT NULL,
  `employer_name` varchar(100) NOT NULL,
  `employer_address` varchar(100) DEFAULT NULL,
  `employer_city` varchar(100) DEFAULT NULL,
  `employer_state` char(2) DEFAULT NULL,
  `employer_zip` varchar(20) DEFAULT NULL,
  `years_employed` char(5) DEFAULT NULL,
  `months_employed` varchar(6) DEFAULT NULL,
  `hire_date` datetime NOT NULL,
  `subid` varchar(200) DEFAULT NULL,
  `mother_maiden_name` varchar(100) NOT NULL,
  `ping_id` int(11) NOT NULL,
  `lead_mgmt` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'This field defines if we sent this lead to primelead or not\n  not.',
  `supervisor_name` varchar(100) NOT NULL,
  `supervisor_phone` varchar(20) NOT NULL,
  `supervisor_phone_ext` varchar(10) NOT NULL,
  `bank` varchar(100) NOT NULL,
  `bank_phone` varchar(20) DEFAULT NULL,
  `months_at_bank` char(5) NOT NULL,
  `bank_routing_number` varchar(20) NOT NULL,
  `bank_account_number` varchar(20) NOT NULL,
  `bank_account_type` varchar(10) NOT NULL,
  `direct_deposit` char(5) NOT NULL,
  `referer_first_name1` varchar(50) DEFAULT NULL,
  `referer_last_name1` varchar(50) DEFAULT NULL,
  `referer_phone1` varchar(20) DEFAULT NULL,
  `referer_relation1` varchar(50) DEFAULT NULL,
  `referer_first_name2` varchar(50) DEFAULT NULL,
  `referer_last_name2` varchar(50) DEFAULT NULL,
  `referer_phone2` varchar(20) DEFAULT NULL,
  `referer_relation2` varchar(50) DEFAULT NULL,
  `referer_first_name3` varchar(50) DEFAULT NULL,
  `referer_last_name3` varchar(50) DEFAULT NULL,
  `referer_phone3` varchar(20) DEFAULT NULL,
  `referer_relation3` varchar(50) DEFAULT NULL,
  `contact_time` char(5) DEFAULT NULL,
  `sms_optin` char(5) DEFAULT NULL,
  `credit_profile` varchar(20) DEFAULT NULL,
  `mortgage_balance1` int(11) DEFAULT NULL,
  `mortgage_interest_rate1` decimal(10,2) DEFAULT NULL,
  `mortgage_rate_type1` varchar(20) DEFAULT NULL,
  `mortgage_balance2` int(11) DEFAULT NULL,
  `mortgage_interest_rate2` decimal(10,2) DEFAULT NULL,
  `mortgage_rate_type2` varchar(20) DEFAULT NULL,
  `monthly_debt` int(11) DEFAULT NULL,
  `mortgage_loan_type` varchar(20) DEFAULT NULL,
  `mortgage_preferred_loan_type` varchar(20) DEFAULT NULL,
  `property_type` varchar(20) DEFAULT NULL,
  `existing_property_value` int(11) DEFAULT NULL,
  `new_property_value` int(11) DEFAULT NULL,
  `down_payment` int(11) DEFAULT NULL,
  `property_found` tinyint(4) DEFAULT NULL,
  `pawn_transaction_type` varchar(4) DEFAULT NULL,
  `pawn_has_item` tinyint(4) DEFAULT NULL,
  `pawn_will_sell` varchar(4) DEFAULT NULL,
  `pawn_nplanet_id` varchar(10) DEFAULT NULL,
  `pawn_how_soon` int(11) DEFAULT NULL,
  `bankruptcy` varchar(50) DEFAULT NULL,
  `loan_purpose` tinyint(1) DEFAULT NULL,
  `contact_at_work` tinyint(1) DEFAULT NULL,
  `supplemental_income` int(11) DEFAULT NULL,
  `lic_plate` varchar(10) DEFAULT NULL,
  `auto_make` varchar(30) DEFAULT NULL,
  `auto_miles` int(11) DEFAULT NULL,
  `auto_model` varchar(30) DEFAULT NULL,
  `auto_type` varchar(200) DEFAULT NULL,
  `auto_vin` varchar(200) DEFAULT NULL,
  `auto_year` int(11) DEFAULT NULL,
  `credit_check_auth` tinyint(1) DEFAULT NULL,
  `fico_score` int(11) DEFAULT NULL,
  `co_signer_available` tinyint(1) DEFAULT NULL,
  `opt_in` tinyint(1) DEFAULT NULL,
  `forward_application` tinyint(1) DEFAULT NULL,
  `marital_status` tinyint(1) DEFAULT NULL,
  `sort_code` varchar(20) DEFAULT NULL,
  `originating_url` varchar(255) DEFAULT NULL,
  `client_url` varchar(255) DEFAULT NULL,
  `is_live` char(1) DEFAULT NULL,
  `sold` tinyint(1) NOT NULL DEFAULT '0',
  `network_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `raw_data` text,
  `redirect_url` text,
  `redirected` tinyint(1) NOT NULL DEFAULT '0',
  `uid` varchar(55) NOT NULL,
  `is_completed` tinyint(1) NOT NULL,
  `lead_response` text,
  `r_response` text,
  `resubmitted` datetime DEFAULT NULL,
  `user_agent` varchar(200) DEFAULT NULL,
  `param_type` varchar(7) DEFAULT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,

Primary Key and indexes:

  PRIMARY KEY (`id`),
  KEY `id` (`id`) USING BTREE,
  KEY `client_id` (`client_id`),
  KEY `is_live` (`is_live`),
  KEY `sold` (`sold`),
  KEY `amount` (`amount`),
  KEY `subid` (`subid`) USING BTREE,
  KEY `client_url` (`client_url`),
  KEY `originating_url` (`originating_url`),
  KEY `ping_id` (`ping_id`),
  KEY `network_id` (`network_id`),
  KEY `group_id` (`group_id`),
  KEY `is_completed` (`is_completed`),
  KEY `uid` (`uid`),
  KEY `redirected` (`redirected`),
  KEY `created` (`created`),
  KEY `first_name` (`first_name`),
  KEY `last_name` (`last_name`),
  KEY `email` (`email`),
  KEY `subid_islive_created_ndx` (`subid`,`is_live`,`created`) USING BTREE,
  KEY `islive_subid_created_ndx` (`is_live`,`subid`,`created`)
) ENGINE=InnoDB 
  AUTO_INCREMENT=2217258 
  DEFAULT CHARSET=latin1 
  ROW_FORMAT=DYNAMIC 
  COMMENT='table to store leads'
share|improve this question
1  
Wow, that's lost of indexes on a table. How many columns does it have? I imagine the optimizer has lost its mind... –  Phil Dec 30 '12 at 1:31
    
Heh. Yea, the table has 135 fields and about 2MM records in it. –  nateritter Dec 30 '12 at 2:01
    
Wow, that leads table needs mad normailzation. –  RolandoMySQLDBA Jan 2 '13 at 17:53
    
Yes, typically I would, but the way this application runs, it's much faster/easier denormalized in this way. –  nateritter Jan 2 '13 at 20:24

1 Answer 1

up vote 2 down vote accepted

Your query has to use the table and not the index alone because you have two columns (is_live and sub_id) in the WHERE clause that are not part of this (created) index. The sub_id column is also included in the GROUP BY and the SELECT list so how would it be possible for the table not to be used?


I would try an index that "covers" the query, like the (is_live, created, subid) and writing the query like this:

SELECT subid 
FROM leads
WHERE is_live = 1
  AND created >= '2012-12-13 00:00:00' 
  AND created <= '2012-12-13 23:59:59' 
GROUP BY subid 
HAVING subid IS NOT NULL ;

or the index (is_live, subid, created) and the query written like:

SELECT subid 
FROM 
    ( SELECT subid
      FROM leads
      WHERE is_live = 1
      GROUP BY is_live, subid
    ) AS d
WHERE subid IS NOT NULL
  AND EXISTS
      ( SELECT *
        FROM leads l 
        WHERE l.is_live = 1
          AND l.subid = d.subid
          AND l.created >= '2012-12-13 00:00:00' 
          AND l.created <= '2012-12-13 23:59:59'
      ) ;
share|improve this answer
    
I tried that with "create unique index created_subid_islive_ndx on leads (created, subid, is_live);" and then re-ran both mine and your query and it didn't seem to change a thing other than adding the index to the possible_keys explain column (in my query), and removing the subid from possible_keys (in your query). –  nateritter Dec 30 '12 at 2:23
    
Did you try the indexes I suggested? –  ypercube Dec 30 '12 at 2:29
    
I didn't, and now I see after doing a bit of reading that the order makes a difference. I'll give that a shot and let you know. –  nateritter Dec 31 '12 at 7:36
    
I just tried it and it didn't change anything. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+------------------------------------------+-‌​--------+---------+------+-------+----------------------------------------------+ | 1 | SIMPLE | Lead | range | is_live,created,islive_subid_created_ndx | created | 8 | NULL | 86390 | Using where; Using temporary; Using filesort | –  nateritter Jan 2 '13 at 17:06
    
subid_islive_created_ndx = subid, is_live, created .... and ... islive_subid_created_ndx = is_live, subid, created –  nateritter Jan 2 '13 at 17:08

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.