MySQL Optimization testing "WHERE (X=0 or X=1)" vs. "WHERE X<2"

Is there a performance benefit to removing OR statements?

Which compiles faster

SELECT id FROM mytable where (x=0 or x=1) 

or

SELECT id FROM mytable where x<2

I ran the test myself and the second one is a bit faster. BUT I am not sure if that is due to caching that happens when I开发者_如何学JAVA run the first command. Is there a good way to evaluate two competing queries without them caching and affecting the others?

EXPLAIN produces the same output:

+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | mytable | ALL  | NULL          | NULL | NULL    | NULL | 1407715 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+

Use SQL_NO_CACHE :

SELECT SQL_NO_CACHE id FROM mytable where x<2

When you did your test, did you run the query one time or a thousand time ? Because the time for one run isn't relevant at all. You can also use query explaining to help you decide.

Also, your second query as the problem to accept all values under 0, which can cause problems.

There's also another possibility :

SELECT id FROM mytable WHERE x BETWEEN 0 AND 1

It doesn't make a lot of sense when you have such a short interval, but can be used when the range is greater.

Performance wise, I really think you shouldn't worry about this kind of optimization, you'll gain nothing in the long run, this is not where your DBMS will lose his time. I really don't think this is worst benchmarking, just use the syntax you're the more comfortable with.

One of the most basic way to evaluate a query is to take a look at its execution with EXPLAIN.

You can also take a look at profiling, although it's nowhere as useful in most cases and it's more situational.

As it turns out, EXPLAIN shows that your query doesn't use any index (the "possible keys" column says "NULL" which is the fancy way to say there's nothing.) In your case, adding an index of the column x would drastically effect its execution.

I would say that you could get a fairly decent comparison by ensuring you stop and start the DBMS before each query. It may even be worthwhile shutting down the whole PC between runs.

Another possibility is to alternate a larger number of samples. Run the two queries A and B in the sequence A,B,A,B,A,B,A,B,A,B and average the results.

Also (although I don't know this about MySQL) most DBMS' have a query explainer which you should be using to check this sort of thing.

Those two queries aren't the same if id can go negative, by the way.

Based on your update, the EXPLAIN output, you get the same result for both queries. That means there should be no difference between them, all other things being equal.

However, since there's no mention of a key or index being used (or usable), you almost certainly don't have an index on x. If you add that, you should find that the query will be faster (this still depends on the cardinality of x but, as a general rule, it should improve).