0

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

  • With joins you should have always have indexes for all columns that are in the ON clause. Also use aliases for the table, that makes it somewhat easier to read and you have to type in less. – nbk Jan 29 at 19:17
  • Yes, I had added the index on 'mapping_companyid' column, but still the issue persists – user5594148 Jan 30 at 2:41
  • The query is using 9 columns from the 'taxi_driver_mapping' table, would it be correct to add indexes to all these columns? – user5594148 Jan 30 at 4:53
  • all columns that are in On clause involved are better ann index, that is valid for both sides of the ON clause. the nino from 'taxi_driver_mapping and the others from city a.s.o. – nbk Jan 30 at 8:29
0
  • 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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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