MySQL – How to improve this query? EXPLAIN attached

explainMySQLperformancequery-performance

I have a query that I feel should go much faster then it does. Right now it'll execute around 1,5xx secs each time. I don't have any advanced knowledge of looking at the EXPLAIN and draw conclusions. Maybe someone can help?

The query has all its ID-fields indexed and unsigned.

"reservations" contains around 100,000 records and "reservation_nights" around 180,000 records. All other tables have under 1000 records each.

"profiles" is LEFT JOIN:ed because not all reservations has profiles.

Here's the query:

SELECT DISTINCT(reservations.id), `reservations`.`referenceId`, `reservations`.`referenceStatus`, `reservations`.`status`, `reservations`.`firstname`, `reservations`.`lastname`, `reservations`.`dateArrival`, `reservations`.`daysToCheckIn`, `reservations`.`rateCode`, `reservations`.`roomCode`, `reservations`.`totalNights`, `reservations`.`rateRevenue`, `reservations`.`dailyRate`, `reservations`.`totalRevenue`, `reservations`.`foodBeverageRevenue`, `reservations`.`additionalRevenue`, `channels`.`name` AS `channelName`, `channelgroups`.`name` AS `channelGroupName`, `markets`.`name` AS `marketName`, `profiles`.`totalReservations` 
FROM `reservation_nights` 
INNER JOIN `reservations` ON reservations.id = reservation_nights.reservationId 
INNER JOIN `channels` ON channels.id = reservations.channelId 
INNER JOIN `channelgroups` ON channelgroups.id = channels.channelgroupId 
INNER JOIN `markets` ON markets.id = reservations.marketId 
LEFT JOIN `profiles` ON profiles.id = reservations.profileId 
ORDER BY `reservation_nights`.`dateStayed` DESC 
LIMIT 100

Attached is the EXPLAIN from PHPMYADMIN. Where should I start optimizing this query?

enter image description here

Best Answer

First, please remove the parentheses from DISTINCT(reservations.id).
DISTINCT is not a function.
About performance, why do you join reservation_nights? You only seem to use that table in the ORDER BY. Assuming that a reservation can span several nights, you probably mean to use the last date (from the many that a reservation has) for the ordering and nothing else. This however, requires a join on reservation_nights and thus many rows for each reservation are created in the result set. So, you had to use DISTINCT to get rid of the duplicate rows. DISTINCT is often inefficient, especially when performed for big results sets and not in small/indexed tables.

Try this rewriting, that gets rid of the DISTINCT and pushes the ORDER BY / LIMIT 100 in a derived table where only base table is used. This technique which I call "LIMIT first, JOIN later" can be more efficient as the order by is done on less data (single table vs a large join) and then the joins are done on less data as well (max 100 rows from each of the joined tables):

SELECT 
    r.id, 
    r.referenceId, r.referenceStatus, r.status, 
    r.firstname, r.lastname, 
    r.dateArrival, r.daysToCheckIn, 
    r.rateCode, r.roomCode, 
    r.totalNights, r.rateRevenue, 
    r.dailyRate, r.totalRevenue, 
    r.foodBeverageRevenue, r.additionalRevenue, 
    c.name AS channelName, 
    cg.name AS channelGroupName, 
    m.name AS marketName, 
    p.totalReservations 
FROM 
    ( SELECT reservationId, MAX(dateStayed) AS lastDateStayed
      FROM reservation_nights
      GROUP BY reservationId
      ORDER BY MAX(dateStayed) DESC 
      LIMIT 100 
    ) AS rn
  INNER JOIN reservations AS r ON r.id = rn.reservationId 
  INNER JOIN channels AS c ON c.id = r.channelId 
  INNER JOIN channelgroups AS cg ON cg.id = c.channelgroupId 
  INNER JOIN markets AS m ON m.id = r.marketId 
  LEFT JOIN profiles AS p ON p.id = r.profileId 
ORDER BY 
    rn.lastDateStayed DESC ;

If you don't already have one, add an index on reservation_nights (reservationId, dateStayed) to improve the efficiency of the subquery.

Related Solutions

MySql – How to improve this query

What are you trying to achieve here? I am not a MySQL programmer; however, as I understand SQL in general, every column that is not listed in the GROUP BY clause must be part of an aggregate like

SELECT
    MAX(id) AS max_id,
    chain, subchain, job_type
FROM
    `job_logs`
WHERE
    user_id = ? AND
    workflow_state = ?
GROUP BY
    chain, subchain, job_type

SELECT * in a grouping query will not work in most (if not all) SQL implementations.

UPDATE

In order to get the row with the highest id for each group you would have to embed the query above in an "outer" query.

SELECT *
FROM `job_logs`
WHERE id IN (
    SELECT
        MAX(id) AS max_id
    FROM
        `job_logs`
    WHERE
        user_id = ? AND
        workflow_state = ?
    GROUP BY
        chain, subchain, job_type 
)

This query is deterministic and should work with most SQL dialects.

Some query engines perform better with joins than with "IN subquery". You can give this a try

SELECT A.*
FROM
    `job_logs` A
    INNER JOIN (
        SELECT
            MAX(id) AS max_id
        FROM
            `job_logs`
        WHERE
            user_id = ? AND
            workflow_state = ?
        GROUP BY
            chain, subchain, job_type) B
    ON A.id = B.max_id;

Mysql – How to improve this query

First, since you already have a UNIQUE index that contains the user_id, you should be able to get rid of the id field, and use the UNIQUE index as the PRIMARY KEY:

CREATE TABLE `twitter_relationships` (
  `user_id` int(11) NOT NULL,
  `source_twitter_id` bigint(20) NOT NULL,
  `target_twitter_id` bigint(20) NOT NULL,
  `relationship_status` tinyint(1) NOT NULL,
  `status_change_date` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`,`source_twitter_id`,`target_twitter_id`),
  KEY `target_status_and_change_date_index`
    (`user_id`,`target_twitter_id`,`relationship_status`,`status_change_date`),
  KEY `user_id_index` (`user_id`,`status_change_date`)
) ENGINE=InnoDB AUTO_INCREMENT=116597775 DEFAULT CHARSET=latin1
PARTITION BY HASH (user_id) PARTITIONS 1000;

Unfortunately, while this removes an index, it may increase storage requirements, due to the way that InnoDB indexes data. See "How Secondary Indexes Relate to the Clustered Index" in http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html

Second, while the source_and_target index has two of the three fields in your WHERE clause, MySQL will have to do an additional read to find the relationship_status.

Therefore, to improve performance, create an index that includes all three fields in your WHERE clause:

CREATE INDEX user_source_status ON twitter_relationships
    (`user_id`,`source_twitter_id`,`relationship_status`);

Then, if MySQL doesn't use this index automatically, you can force using it, with:

SELECT target_twitter_id 
 FROM `twitter_relationships` FORCE INDEX (user_source_status)
WHERE (`twitter_relationships`.`user_id` = ? 
   AND `twitter_relationships`.`source_twitter_id` = ?
   AND `twitter_relationships`.`relationship_status` = ?) 
LIMIT ?, ?

Lastly, you're missing the UNSIGNED attribute on the id, user_id, source_twitter_id, and target_twitter_id fields. I'm guessing these fields will never store negative values, so it would make sense to make them UNSIGNED.

Best Answer

Related Solutions

MySql – How to improve this query

Mysql – How to improve this query

Related Question