CODEWITHSUNDEEP
mysqlsqlsqlperformance
Vishy
Vishy

Reputation: 1352

Regarding Performance Benefits of WHERE clause conditions order

Consider 3 tables (* for primary key, + for foreign):

User {*id:INTEGER, name:VARCHAR(45)}
Flight {*id:INTEGER, service:VARCHAR(45), departureDate:DATETIME}
Reservation { +userId, +flightId }

Data:

User:

User

Flight:

Flight

Reservation:

Reservation

The following Query will get the list of passengers travelling on 2013-05-06:

SELECT *
FROM user u, flight f, reservation r
WHERE u.id=f.id AND r.userid=u.id AND r.flightid=f.id AND f.departureDate='2013-05-06'

Questions I have are:

  1. How does SQL engines treat WHERE clause? With reference to above query, will it first form temp data table T1 by combining r and f by r.userid=u.id, then another temp table T2 combining T1 and f by r.flightid=f.id and finally filtering for departureData in T2?

  2. Will there be a change in performance if the clause changes to: WHERE f.departureDate='2013-05-06' AND u.id=f.id AND r.userid=u.id AND r.flightid=f.id in which case f.departureDate='2013-05-06' will reduce the rows to form temp tables?

Upvotes: 0

Views: 366

Answers (2)

yakya
yakya

Reputation: 5210

1) No, order is not important. It is all about DB engines. DB engines can make smart choices like executing basic commands first and leaving other complex ones later. So don't think about reordering statements, query optimizer will do it for you. Just to give a basic example of what query optimizer does is converting the statement of:

(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)

to

B=5 OR B=6

2)Again, there will be no difference.

There is no complete list of what MySql Query Optimizer does but you can find some of them here

Upvotes: 2

xQbert
xQbert

Reputation: 35323

Each engine is different based on provider, version, build etc. So the only real way to know is to test using query analysis tools and find out what's best in your situation.

Generally speaking it is better to always reduce the Cartesian product the system must generate thereby reducing the data it has to parse.

To answer your questions directly though:

1) it may generate the user, flight, reservation data first then limit the results based on your where clause. So if user flight and reservation have (100,1000,10000) rows each it will generate 100*1000*10000 rows then limit the data. Or it May limit the data first (getting all depattures then then do the join again it depends on the engine) Engines are intended to be optimized for performance so it tries to do the best it can for you; thus the need to use query analyzer and find out for your engine.

2) again it depends on your version RDMBS. it may query analyzer is your friend learn to use it!

Now, if you don't have an indiex on departureDate, having one would give you the best benefit along with ones on u.id, f.id and r.userid

Upvotes: 1

Related Questions