CODEWITHSUNDEEP
mysql
vignesh raj kumar
vignesh raj kumar

Reputation: 181

How ro reduce the mysql query running time

This is my query running in one page of my site

SELECT 
    DISTINCT b.CruisePortID,
    b.SailingDates,
    b.CruisePortID,
    b.ArriveTime,
    b.DepartTime,
    b.PortName,
    b.DayNumber
FROM
    cruise_itineraries a,
    cruise_itinerary_days b,
    cruise_ports c
WHERE
a.ID = b.CruiseItineraryID
AND a.CruisePortID = c.ID
AND a.ID = '352905'
AND b.CruisePortID != 0
GROUP BY b.DayNumber;

while running this query in phpmy admin its take 3.20 sec because of cruise_itineraries had more 300 000 records I tried indexing also after indexing it show 2.92 sec. Is any possible to reduced query time less .10 sec. Its help my site performance

here details

 CREATE TABLE IF NOT EXISTS `cruise_itineraries` (
  `cl` int(11) NOT NULL,
  `ID` bigint(20) NOT NULL,
  `Description` varchar(500) NOT NULL,
  `SailingPlanID` varchar(100) NOT NULL,
  `VendorID` varchar(100) NOT NULL,
  `VendorName` varchar(100) NOT NULL,
  `ShipID` varchar(100) NOT NULL,
  `ShipName` varchar(100) NOT NULL,
  `Duration` int(11) NOT NULL,
  `DestinationID` varchar(100) NOT NULL,
  `Date` datetime NOT NULL,
  `CruisePortID` varchar(100) NOT NULL,
  `TradeRestriction` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `cruise_itinerary_days` (
  `cld` int(11) NOT NULL,
  `CruiseItineraryID` varchar(100) NOT NULL,
  `SailingDates` datetime NOT NULL,
  `VendorID` int(11) NOT NULL,
  `VendorName` varchar(100) NOT NULL,
  `ShipID` int(11) NOT NULL,
  `ShipName` varchar(100) NOT NULL,
  `SailingPlanID` int(11) NOT NULL,
  `PlanName` varchar(100) NOT NULL,
  `DayNumber` bigint(20) NOT NULL,
  `PortName` varchar(100) NOT NULL,
  `CruisePortID` varchar(100) NOT NULL,
  `ArriveTime` varchar(100) NOT NULL,
  `DepartTime` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `cruise_ports` (
  `cp` int(11) NOT NULL,
  `ID` varchar(100) NOT NULL,
  `Name` varchar(100) NOT NULL,
  `Description` varchar(1000) NOT NULL,
  `NearestAirportCode` varchar(100) NOT NULL,
  `UNCode` varchar(100) NOT NULL,
  `Address` varchar(500) NOT NULL,
  `City` varchar(100) NOT NULL,
  `StateCode` varchar(100) NOT NULL,
  `CountryCode` varchar(100) NOT NULL,
  `PostalCode` varchar(100) NOT NULL,
  `Phone` varchar(50) NOT NULL,
  `Fax` varchar(100) NOT NULL,
  `Directions` varchar(1000) NOT NULL,
  `Content` varchar(1000) NOT NULL,
  `HomePageURL` varchar(100) NOT NULL,
  `Longitude` varchar(100) NOT NULL,
  `Latitude` varchar(500) NOT NULL,
  `CarnivalID` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


    ALTER TABLE `cruise_itineraries`
  ADD PRIMARY KEY (`cl`),
  ADD KEY `ID_2` (`ID`);

    ALTER TABLE `cruise_itineraries`
  ADD PRIMARY KEY (`cl`),
  ADD KEY `ID_2` (`ID`);


ALTER TABLE `cruise_itinerary_days`
  ADD PRIMARY KEY (`cld`);

ALTER TABLE `cruise_ports`
  ADD PRIMARY KEY (`cp`);


ALTER TABLE `cruise_itineraries`
  MODIFY `cl` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `cruise_itinerary_days`
  MODIFY `cld` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `cruise_ports`
  MODIFY `cp` int(11) NOT NULL AUTO_INCREMENT;

EXPLAIN RESULT:

+----+-------------+-------+------+---------------+------+---------+-------+---------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows    | Extra                                                  |
+----+-------------+-------+------+---------------+------+---------+-------+---------+--------------------------------------------------------+
|  1 | SIMPLE      | a     | ref  | ID_2          | ID_2 | 8       | const |       1 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL  |    3267 | Using where; Using join buffer (Block Nested Loop)     |
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL  | 2008191 | Using where; Using join buffer (Block Nested Loop)     |
+----+-------------+-------+------+---------------+------+---------+-------+---------+--------------------------------------------------------+

+----+-------------+-------+------+------------------------------------+------------------------------------+---------+-------+------+--------------------------------------------------------------+
| id | select_type | table | type | possible_keys                      | key                                | key_len | ref   | rows | Extra                                                        |
+----+-------------+-------+------+------------------------------------+------------------------------------+---------+-------+------+--------------------------------------------------------------+
|  1 | SIMPLE      | b     | ref  | Idx_CruiseItineraryID_CruisePortID | Idx_CruiseItineraryID_CruisePortID | 9       | const |   12 | Using index condition; Using temporary; Using filesort       |
|  1 | SIMPLE      | a     | ref  | ID_2                               | ID_2                               | 8       | const |    1 | Distinct                                                     |
|  1 | SIMPLE      | c     | ALL  | NULL                               | NULL                               | NULL    | NULL  | 3267 | Using where; Distinct; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+------------------------------------+------------------------------------+---------+-------+------+--------------------------------------------------------------+

Upvotes: 2

Views: 111

Answers (1)

1000111
1000111

Reputation: 13519

First I would like to state that try to avoid IMPLICIT MySQL JOINS. Use INNER JOINS instead.

I personally think the INNER JOIN is better, because it is more readable. It shows better the relations between the table. You got those relations in the join, and you do the filtering in the WHERE clause. This separation makes the query more readable.

The faults I've found:

  • The data type of cruise_itineraries.ID is BIGINT and the data type of cruise_itinerary_days.CruiseItineraryID is varchar. But you are matching them in a query. So it will run slow no matter if you use index on cruise_itinerary_days.CruiseItineraryID in cruise_itinerary_days table.

    Change the data type of cruise_itinerary_days.CruiseItineraryID to BIGINT.

    ALTER TABLE cruise_itinerary_days MODIFY CruiseItineraryID BIGINT;

  • Next you have to create a composite index on cruise_itinerary_days table based on your query.

    ALTER TABLE cruise_itinerary_days ADD INDEX Idx_CruiseItineraryID_CruisePortID (CruiseItineraryID, CruisePortID)`

  • Now create an index in cruise_ports table on cruise_ports.ID field.

    ALTER TABLE cruise_ports ADD INDEX Idx_cruise_ports_ID (ID);

And finally the query is formulated using INNER JOINS since I've stated reasons above behind this choice:

SELECT 
    DISTINCT b.CruisePortID,
    b.SailingDates,
    b.CruisePortID,
    b.ArriveTime,
    b.DepartTime,
    b.PortName,
    b.DayNumber
FROM cruise_itineraries a
INNER JOIN cruise_itinerary_days b ON a.ID = b.CruiseItineraryID
INNER JOIN cruise_ports c ON a.CruisePortID = c.ID
WHERE a.ID = 352905
AND b.CruisePortID != 0
GROUP BY b.DayNumber;

Upvotes: 4

Related Questions