MySQL Dispelling The Where 1 Myth

Recently a fellow developer explained to me that MySQL queries could be optimised, or made fast by appending "WHERE 1" to them. Essentially this would provide a condition that is always boolean true. True to the PHPRO.ORG style, we put this to the test.

The database used here is the test database from the Examples here on PHPRO.ORG. In particular, the first table. This first table was created three times and named table1, table2 and table3. Each table contains 99,999 rows.

MySQL has many internal methods for optimizing and caching queries, as does the operating system. It is these caches that need to be by-passed when checking. Rather than dismantling the caches, the three tables will prevent any possible caching of the queries, as each query will be unique.

Test 1

The first query run is on table1 and is a simple SELECT of all data

SELECT * FROM table1;
99999 rows in set (0.74 sec)

Not too shabby or about 100k rows. Now the same query with the "WHERE 1" is added:

SELECT * FROM table1 WHERE 1;
99999 rows in set (0.67 sec)

The query has run much faster. To all appearences, the internal select parser would seem to be optimised to look for a condition, and if it is not provided, one needs to be created internally. Or does it?

Test 2

This time, the second table is used and the query with the "WHERE 1" is run first. The table is identical in every way to the initial table, except for the table name, to avoid caching.

SELECT * FROM table2 WHERE 1;
99999 rows in set (0.70 sec)

Now, to run the qury once agian without the WHERE 1.

SELECT * FROM table2;
99999 rows in set (0.65 sec)

So what is happening is that the MySQL database is simply caching the query. Depending on which is run first, will affect the speed of the second query. Subsequent SELECT queries with either method show them to be about the same speed, no net gain.

Test 3

In this test, the same query is run twice, with the "WHERE 1" condition. Once again the cache is created the first time, and the second query runs faster.

SELECT * FROM table3;
99999 rows in set (0.69 sec)

And the second time through..

SELECT * FROM table3;
99999 rows in set (0.65 sec)

Extending the query

It is left as an exercise for the reader to invent new and exiting ways to test this, but tests on the local dev machine have been consistent. If the first query is run without the "WHERE 1" condition, the database is set to task in creating the cache, and any subsequent queries will benifit from this. When the "WHERE 1" condition is applied, the illusion is that the qury has run faster.