Query with left join not using index

Question

The below query is taking about 1 second to run

SELECT `taxi_driver_mapping`.*,
        concat("dpk00",
         taxi_driver_mapping.mapping_payment_plan_id) AS package_text,
         `people`.*,
         `taxi`.*,
         `company`.`company_name`,
         `country`.`country_name`,
         `state`.`state_name`,
         `city`.`city_name`,
         `peoplecreated`.`name` AS `created_name`,
         `peopleupdated`.`name` AS `updated_name`,
         `device_owner`
FROM `taxi_driver_mapping`
LEFT JOIN `company`
    ON `taxi_driver_mapping`.`mapping_companyid` = `company`.`cid`
LEFT JOIN `taxi`
    ON `taxi_driver_mapping`.`mapping_taxiid` = `taxi`.`taxi_id`
LEFT JOIN `country`
    ON `taxi_driver_mapping`.`mapping_countryid` = `country`.`country_id`
LEFT JOIN `state`
    ON `taxi_driver_mapping`.`mapping_stateid` = `state`.`state_id`
LEFT JOIN `city`
    ON `taxi_driver_mapping`.`mapping_cityid` = `city`.`city_id`
LEFT JOIN `people`
    ON `taxi_driver_mapping`.`mapping_driverid` = `people`.`id`
LEFT JOIN `people` AS `peoplecreated`
    ON `taxi_driver_mapping`.`mapping_createdby` = `peoplecreated`.`id`
LEFT JOIN `people` AS `peopleupdated`
    ON `taxi_driver_mapping`.`mapping_updatedby` = `peopleupdated`.`id`
LEFT JOIN `driver_information`
    ON `taxi_driver_mapping`.`mapping_driverid` = `driver_information`.`driver_id`
WHERE (`people`.`name` LIKE '%abm1173%'
        OR `people`.`lastname` LIKE '%abm1173%'
        OR `people`.`email` LIKE '%abm1173%'
        OR `company`.`company_name` LIKE '%abm1173%'
        OR `people`.`phone` LIKE '%abm1173%'
        OR `people`.`id` LIKE '%abm1173%'
        OR `people`.`username` LIKE '%abm1173%'
        OR `taxi`.`taxi_no` LIKE '%abm1173%')
ORDER BY  `mapping_id` DESC limit 10 offset 0 

Below is the explain plan for it:

+----+-------------+---------------------+--------+------------------+---------+---------+----------------------------------------------+-------+----------------------------------------------------+
| id | select_type | table               | type   | possible_keys    | key     | key_len | ref                                          | rows  | Extra                                              |
+----+-------------+---------------------+--------+------------------+---------+---------+----------------------------------------------+-------+----------------------------------------------------+
|  1 | SIMPLE      | taxi_driver_mapping | ALL    | NULL             | NULL    | NULL    | NULL                                         | 78718 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | company             | eq_ref | PRIMARY          | PRIMARY | 4       | Eztaxi.taxi_driver_mapping.mapping_companyid |     1 | NULL                                               |
|  1 | SIMPLE      | taxi                | eq_ref | PRIMARY          | PRIMARY | 4       | Eztaxi.taxi_driver_mapping.mapping_taxiid    |     1 | NULL                                               |
|  1 | SIMPLE      | country             | eq_ref | PRIMARY          | PRIMARY | 4       | Eztaxi.taxi_driver_mapping.mapping_countryid |     1 | NULL                                               |
|  1 | SIMPLE      | state               | ALL    | PRIMARY,state_id | NULL    | NULL    | NULL                                         |     3 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | city                | ALL    | PRIMARY          | NULL    | NULL    | NULL                                         |     2 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | people              | eq_ref | PRIMARY          | PRIMARY | 4       | Eztaxi.taxi_driver_mapping.mapping_driverid  |     1 | Using where                                        |
|  1 | SIMPLE      | peoplecreated       | eq_ref | PRIMARY          | PRIMARY | 4       | Eztaxi.taxi_driver_mapping.mapping_createdby |     1 | NULL                                               |
|  1 | SIMPLE      | peopleupdated       | eq_ref | PRIMARY          | PRIMARY | 4       | Eztaxi.taxi_driver_mapping.mapping_updatedby |     1 | NULL                                               |
|  1 | SIMPLE      | driver_information  | eq_ref | PRIMARY          | PRIMARY | 4       | Eztaxi.taxi_driver_mapping.mapping_driverid  |     1 | NULL                                               |
+----+-------------+---------------------+--------+------------------+---------+---------+----------------------------------------------+-------+----------------------------------------------------+

Below is the table definition for taxi_driver_mapping table:

| taxi_driver_mapping | CREATE TABLE `taxi_driver_mapping` (
  `mapping_id` int(100) NOT NULL AUTO_INCREMENT,
  `mapping_driverid` int(100) NOT NULL,
  `mapping_taxiid` int(100) NOT NULL,
  `mapping_taxi_model_id` int(11) NOT NULL,
  `mapping_companyid` int(100) NOT NULL,
  `mapping_countryid` int(100) NOT NULL,
  `mapping_stateid` int(100) NOT NULL,
  `mapping_cityid` int(100) NOT NULL,
  `mapping_startdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mapping_enddate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mapping_createdby` int(100) NOT NULL,
  `mapping_createdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mapping_status` varchar(3) NOT NULL DEFAULT 'A',
  `mapping_updatedby` int(11) DEFAULT NULL,
  `mapping_updatedate` timestamp NULL DEFAULT NULL,
  `mapping_payment_plan_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`mapping_id`),
  KEY `ndx_mapping_driverid` (`mapping_driverid`),
  KEY `ndx_mapping_taxiid` (`mapping_taxiid`),
  KEY `ndx_driver_status_taxi_cid` (`mapping_driverid`,`mapping_status`,`mapping_taxiid`,`mapping_companyid`),
  KEY `idx_test` (`mapping_status`,`mapping_driverid`,`mapping_taxiid`)
) ENGINE=InnoDB AUTO_INCREMENT=123027 DEFAULT CHARSET=latin1 |

Table definition for company table:

| company | CREATE TABLE `company` (
  `cid` int(100) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(250) NOT NULL,
  `company_address` varchar(250) NOT NULL,
  `current_location` text NOT NULL,
  `latitude` varchar(250) NOT NULL,
  `longitude` varchar(250) NOT NULL,
  `bankname` varchar(100) NOT NULL,
  `bankaccount_no` varchar(250) NOT NULL,
  `company_country` int(11) NOT NULL,
  `company_state` int(11) NOT NULL,
  `company_city` int(11) NOT NULL,
  `header_bgcolor` varchar(25) NOT NULL,
  `menu_color` varchar(25) NOT NULL,
  `mouseover_color` varchar(25) NOT NULL,
  `time_zone` varchar(250) NOT NULL,
  `userid` int(11) NOT NULL,
  `company_status` varchar(3) NOT NULL DEFAULT 'A',
  `company_type` enum('0','1','2','4') NOT NULL DEFAULT '0' COMMENT '0-Company , 1-Kiosk , 2-Corporate 4-Restaurant',
  `drivers_count` int(11) NOT NULL DEFAULT '500',
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=4122 DEFAULT CHARSET=latin1 |

I thought that the column mapping_companyid used in the first left join not being indexed is the reason for full table scan. But even after adding the index to it, the query is taking 1 second. Can someone please let me know what could be the issue here? Let me know if any more info. is needed. Thanks

Answer 1

• Overnormalized -- It is rarely worth it to split up an address and normalize each piece.
• You have a company_address plus separate columns for city, state, etc. If these are redundant, that is a no-no.
• (Style nitpick): Don't prefix column names with the table name; it clutters the queries.
• INT is always a 4-byte quantity; the (100) in INT(100) means nothing.
• Leading wildcard cannot use an index: LIKE '%abm1173%'
• OR usually prevents us of an index.

Performance suggestion:

Assuming that abm1173 is a "word", build a FULLTEXT index on a column that contains the concatenation (with spacing) of all the columns that you want to search in. Then a single MATCH(search_col) AGAINST('+abm1173' IN BOOLEAN MODE) will very rapidly find the row(s) that you desire.

There are limitations of FULLTEXT that you could run into. In this case, you may need to revert to the 1-second query you have (after some of the cleanup I suggest), but only if your code determines that the FT query will fail due to restrictions.