[Solved] Is this the right and fastest query?

EverSQL Database Performance Knowledge Base

Is this the right and fastest query?

I need the optimal SQL query in the following job. I have these tables:

host: idhost
table:  idtable, idhost
reservation: idtable, start, stop

And I need those hosts which has any free table at a period (2014-03-03 12:00 - 2014-03-03 14:00).

I use this query (i'm sure this gives back right result)

SELECT idhost FROM idhost WHERE idhost NOT IN (
    SELECT idhost FROM host INNER JOIN table USING (idhost) INNER JOIN reservation USING (idtable)
        WHERE :start BETWEEN start AND stop OR :stop BETWEEN start AND stop OR :start < start AND stop > :stop)

But somebody said the following query is faster and gives back the same host, but I'm not sure:

SELECT * FROM host  
    JOIN table USING (idhost)  
        LEFT JOIN reservation ON reservation.idtable = table.idtable AND  
             :start >= reservation.start AND :stop <= reservation.stop  
WHERE reservation.idtable IS NULL

Is it correct and faster? Or is there a better query?

Thank you in advance!

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid OR Conditions By Using UNION (modified query below): In mosts cases, filtering using the OR operator cannot be applied using indexes. A more optimized alternative will be to split the query to two parts combined with a UNION clause, while each query holds one part of the original OR condition.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  3. Use UNION ALL instead of UNION (query line: 33): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.
Optimal indexes for this query:
ALTER TABLE `idhost` ADD INDEX `idhost_idx_idhost` (`idhost`);
The optimized query:
SELECT
        idhost.idhost 
    FROM
        idhost 
    WHERE
        idhost.idhost NOT IN (
            SELECT
                idhost 
            FROM
                ((SELECT
                    idhost AS idhost 
                FROM
                    host 
                INNER JOIN
                    table USING (idhost) 
                INNER JOIN
                    reservation USING (idtable) 
                WHERE
                    :start < start 
                    AND stop > :stop) 
            UNION
            DISTINCT (SELECT
                idhost AS idhost 
            FROM
                host 
            INNER JOIN
                table USING (idhost) 
            INNER JOIN
                reservation USING (idtable) 
            WHERE
                :stop BETWEEN start AND stop) 
        UNION
        DISTINCT (SELECT
            idhost AS idhost 
        FROM
            host 
        INNER JOIN
            table USING (idhost) 
        INNER JOIN
            reservation USING (idtable) 
        WHERE
            :start BETWEEN start AND stop)
    ) AS union1
)

Related Articles



* original question posted on StackOverflow here.