Order of condition execution in MySQL

Suppose I have a MySQL query with two conditions:

SELECT * FROM `table` WHERE `field_1` = 1 AND `field_2` LIKE '%term%';

The first condition is obviously going to be a lot cheaper than the second, so I'd like to be sure that it runs first, limiting the pool of rows which will be compared with the LIKE clause. Do MySQL query conditions run in the order they're listed or, if not, is there a way to specify order?

Answers


MySQL has an internal query optimizer that takes care of such things in most cases. So, typically, you don't need to worry about it.

But, of course, the query optimizer is not foolproof. So...

Sorry to do this to you, but you'll want to get familiar with EXPLAIN if you suspect that a query may be running less efficiently than it should.

http://dev.mysql.com/doc/refman/5.0/en/explain.html


The optimiser will evaluate the WHERE conditions in the order it sees fit.

SQL is declarative: you tell the optimiser what you want, not how to do it.

In a procedural/imperative language (.net, Java, php etc) then you say how and would choose which condition is evaluated first.

Note: "left to right" does apply in certain expressions like (a+b)*c as you'd expect


Need Your Help

Session Variables not saved when page is in an iFrame

asp.net session iis-7

I have an aspx page with a listbox control. The listbox is populated from a collection that is retrieved from a service when the page loads. When the user selects an item from the listbox the page ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.