how to simplify sql query

Question

i need to simplify this query because the server that hold mysql can't do it in the time limit setted.

SELECT `B`.*,
       `C`.`name`,
       DATE_ADD(`B`.`SAILING-DATE`, INTERVAL `B`.`NIGHTS` DAY) AS dataEnd 

FROM `table1` AS B 
INNER JOIN `table2` AS A 
INNER JOIN `table3` AS C

WHERE `B`.`ID`=`A`.`ID` 
AND `B`.`CAT`=`A`.`CAT` 
AND `C`.`code`=`A`.`code`
AND (`B`.`NIGHTS`>=$aNumber AND `B`.`NIGHTS`<=$anotherNumber) 
// where are other AND like this on `table1`

Where table1 and table2 has about 25.000 rows.

I thought to suddivide the query executing a first query on table1, than loop all the result and make a single query for every result, but i think is too inefficient.

can someone help me? thanks!!!

EDIT:

THIS IS THE EXPLAIN:

EDIT2:

I CAN'T ADD INDEX CAUSE THE FIELD ID, CAT and code are not unique in every table.. in each there are more rows with same ID CAT and code

EDIT3:

I think is better to divide all into subquery more simple.. can someone give me an idea? thanks!

Answer 1

One thing you can try is the following:

SELECT `B`.*, 
       `C`.`name`, 
       DATE_ADD(`B`.`SAILING-DATE`, INTERVAL `B`.`NIGHTS` DAY) AS dataEnd  

FROM (SELECT * 
         FROM `table1` 
         WHERE table1.NIGHTS >=$aNumber AND table1.NIGHTS <=$anotherNumber) AS B  
INNER JOIN `table2` AS A  ON `B`.`ID`=`A`.`ID` AND `B`.`CAT`=`A`.`CAT`
INNER JOIN `table3` AS C  ON `A`.`code` = `C`.`code`

This will effectively prefilter the rows in table1 by only selecting those with Nights fulfilling your required criteria reducing the number involved in joins on the other tables.

Also note that your JOIN conditions should be after the INNER JOIN using ON - you effectively have no need of the WHERE clause in this instance.

One other thing you should investigate is the query plan - see the EXPLAIN command for more information.

In this instance, you may benefit from an index on the ID, CAT, and CODE columns, but, rather than just add an index to everything, examine the output from EXPLAIN first to see where the best savings are - adding indexes will have a cost when you insert data.

EDIT:

Okay, so the subquery performs well, but you are worried about the situation where you can't prefilter table1. First things first, let's revise your original query, and try that:

SELECT B.*, 
       C.name, 
       DATE_ADD(B.SAILING-DATE, INTERVAL B.NIGHTS DAY) AS dataEnd  

FROM table1 AS B  
INNER JOIN table2 AS A  ON (B.ID=A.ID AND B.CAT=A.CAT)
INNER JOIN table3 AS C  ON (A.code = C.code)

I wonder if the odd formatting of your original query (the join conditions should really follow the table you are joining on) are confusing MySql in some way. I've also removed the back-ticks as they aren't needed and, in my opinion, make the query much harder to read (and type).

If performance is not what you expect, please post the results of SHOW INDEX on table1 and table2. It may be that adding an index on the combination of ID and CAT in table1 and ID and CAT in table2 (a composite index) may improve your performance here.

Answer 2

Start by putting the join conditions in the joins, to help the database make the correct interpretation of the query. This will probably not make a performance difference, but at least the query will be easier to interpret for humans.

You don't need backticks around the alias names in the query. You don't have them everywhere now, so be consistent and add them everywhere, or remove them.

SELECT B.*,
       C.`name`,
       DATE_ADD(B.`SAILING-DATE`, INTERVAL B.`NIGHTS` DAY) AS dataEnd 

FROM `table1` AS B 
INNER JOIN `table2` AS A ON B.`ID`=A.`ID` AND B.`CAT`=A.`CAT`
INNER JOIN `table3` AS C ON C.`code`=A.`code`
WHERE (B.`NIGHTS`>=$aNumber AND B.`NIGHTS`<=$anotherNumber)

Then make sure that you have indexes that the query can use, like on table2.NIGHTS. You can use the explain command to determine which indexes are used by the query.