How to optimize this query?

MySQL & MariaDB Other MySQL® Questions
1

Hi, I’m struggling to find a way to speed up this query"

SELECT DISTINCT hd.hotel AS hotelID, r1.contractName, r1.availToken, r1.contractOffice, r1.supplierDest, r1.hotelCurr, r1.hotel, r1.boardName AS boardName1, r1.roomName AS roomName1, r1.price AS price1, r1.roomCode AS roomCode1, r1.roomTypeCode AS roomTypeCode1, r1.roomTypeChar AS roomTypeChar1, r1.boardCode AS boardCode1, r2.boardName AS boardName2, r2.roomName AS roomName2, r2.price AS price2, r2.roomCode AS roomCode2, r2.roomTypeCode AS roomTypeCode2, r2.roomTypeChar AS roomTypeChar2, r2.boardCode AS boardCode2, r3.boardName AS boardName3, r3.roomName AS roomName3, r3.price AS price3, r3.roomCode AS roomCode3, r3.roomTypeCode AS roomTypeCode3, r3.roomTypeChar AS roomTypeChar3, r3.boardCode AS boardCode3
FROM searchRoomOptions r1
JOIN hotelDetails hd ON hd.code=r1.hotel AND hd.source=2
JOIN searchRoomOptions r2 ON r2.hotel=r1.hotel AND r2.search=r1.search AND r2.adults=2 AND r2.children=0
JOIN searchRoomOptions r3 ON r3.hotel=r1.hotel AND r3.search=r1.search AND r3.adults=2 AND r3.children=0
WHERE r1.search=498853 AND r1.adults=2 AND r1.children=0
ORDER BY r1.hotel

EXPLAIN returns this:

“id”,“select_type”,“table”,“type”,“possible_keys”,"key ",“key_len”,“ref”,“rows”,“Extra”
1,“SIMPLE”,“r1”,“index_merge”,“hotel,adults,children,search “,“search,adults,children”,“4,5,5”,””,94,“Using intersect(search,adults,children); Using where; Using temporary; Using filesort”
1,“SIMPLE”,“hd”,“ref”,“source,code”,“code”,“5”,"testdb.r1.hotel ",2,“Using where”
1,“SIMPLE”,“r3”,“ref”,“hotel,adults,children,search”, “hotel”,“5”,“testdb.hd.code”,7,“Using where”
1,“SIMPLE”,“r2”,“ref”,“hotel,adults,children,search”, “hotel”,“5”,“testdb.r3.hotel”,7,“Using where”

Table definition:

Code:
“Field”,“Type”,“Null”,“Key”,“Default”,“Extra”
“id”,“int(10) unsigned”,“NO”,“PRI”,“”,“auto_increment”
“search”,“int(10) unsigned”,“NO”,“MUL”,“”,“”
“boardName”,“varchar(50)”,“YES”,“”,“”,“”
“roomname”,“varchar(50)”,“YES”,“”,“”,“”
“price”,“float”,“YES”,“”,“0”,“”
“roomCode”,“varchar(40)”,“YES”,“”,“”,“”
“roomTypeChar”,“varchar(20)”,“YES”,“”,“”,“”
“roomTypeCode”,“varchar(20)”,“YES”,“”,“”,“”
“boardCode”,“varchar(20)”,“YES”,“”,“”,“”
“adults”,“int(10) unsigned”,“YES”,“MUL”,“0”,“”
“children”,“int(10) unsigned”,“YES”,“MUL”,“0”,“”
“minadults”,“int(10) unsigned”,“YES”,“”,“0”,“”
“maxadults”,“int(10) unsigned”,“YES”,“”,“0”,“”
“minchildren”,“int(10) unsigned”,“YES”,“”,“0”,“”
“maxchildren”,“int(10) unsigned”,“YES”,“”,“0”,“”
“occupancy”,“int(10) unsigned”,“YES”,“”,“0”,“”
“childage1”,“int(10) unsigned”,“YES”,“”,“0”,“”
“childage2”,“int(10) unsigned”,“YES”,“”,“0”,“”
“childage3”,“int(10) unsigned”,“YES”,“”,“0”,“”
“hotel”,“int(10) unsigned”,“YES”,“MUL”,“0”,“”
“contractName”,“varchar(20)”,“YES”,“”,“”,“”
“availToken”,“varchar(45)”,“YES”,“”,“”,“”
“supplierDest”,“varchar(10)”,“YES”,“”,“”,“”
“contractOffice”,“varchar(10)”,“YES”,“”,“”,“”
“hotelCurr”,“varchar(10)”,“YES”,“”,“”,“”

Any ideas?

P.S. It’s pretty difficult to explain the purpose of the query here, but suffice to say I’m expecting it to return thousands of results. I just need to ensure it’s as fast as it can be.

2

Hi,

Without seeing the data, or what you are expecting to return, here are a couple of points which i think will help you eliminate the bad things (such as temp tables).

  1. For starters, is there a reason why you are joining the table on itself, but making all the where clauses equal?

For instance:
JOIN searchRoomOptions r2 ON r2.hotel=r1.hotel AND r2.search=r1.search AND r2.adults=2 AND r2.children=0
JOIN searchRoomOptions r3 ON r3.hotel=r1.hotel AND r3.search=r1.search AND r3.adults=2 AND r3.children=0

while the where clause is:

WHERE r1.search=498853 AND r1.adults=2 AND r1.children=0

and simply returning the same columns:

r1.roomCode AS roomCode1, r1.roomTypeCode AS roomTypeCode1, r1.roomTypeChar AS roomTypeChar1
r2.roomCode AS roomCode2, r2.roomTypeCode AS roomTypeCode2, r2.roomTypeChar AS roomTypeChar2

It appears that you could have the same results by removing these two extra joins.

If you must have all those values repeated in the result set, can’t you just do something like:

r1.roomCode AS roomCode1, r1.roomTypeCode AS roomTypeCode1, r1.roomTypeChar AS roomTypeChar1
r1.roomCode AS roomCode2, r1.roomTypeCode AS roomTypeCode2, r1.roomTypeChar AS roomTypeChar2

  1. Secondly, I would add an index on the ‘searchRoomOptions’ table to the hotel (questionable), search, adults and children columns.

The order of the indexes would depend on what the data is (how varied the various columns are) how else the data is use in other queries. You wanna minimize the number of indexes, but you can make the indexes extensive at the same time.

  1. Not sure how many rows are in the table, but you might want to add an index to the ‘hotelDetails’ on the two columns being used in the where clause.

  2. Make sure you need to use the DISTINCT in the select. That alone will slow down your query, even if there is no need for it. It seems that if you are doing a straight JOIN then the DISTINCT may not be needed.

Hope this helps.