StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I was curious about this myself. It's all fine to read documentation and theoretical answers, but I like to balance those with empirical evidence.</p> <p>I have a MySQL table (InnoDB) that has 5,607,997 records in it. The table is in my own private sandbox, so I know the contents are static and nobody else is using the server. I think this effectively removes all outside affects on performance. I have a table with an auto_increment Primary Key field (Id) that I know will never be null that I will use for my where clause test (WHERE Id IS NOT NULL).</p> <p>The only other possible glitch I see in running tests is the cache. The first time a query is run will always be slower than subsequent queries that use the same indexes. I'll refer to that below as the cache Seeding call. Just to mix it up a little I ran it with a where clause I know will always evaluate to true regardless of any data (TRUE = TRUE).</p> <p>That said here are my results:</p> <p>QueryType </p> <pre><code> | w/o WHERE | where id is not null | where true=true </code></pre> <p>COUNT()</p> <pre><code> | 9 min 30.13 sec ++ | 6 min 16.68 sec ++ | 2 min 21.80 sec ++ | 6 min 13.34 sec | 1 min 36.02 sec | 2 min 0.11 sec | 6 min 10.06 se | 1 min 33.47 sec | 1 min 50.54 sec </code></pre> <p>COUNT(Id)</p> <pre><code> | 5 min 59.87 sec | 1 min 34.47 sec | 2 min 3.96 sec | 5 min 44.95 sec | 1 min 13.09 sec | 2 min 6.48 sec </code></pre> <p>COUNT(1)</p> <pre><code> | 6 min 49.64 sec | 2 min 0.80 sec | 2 min 11.64 sec | 6 min 31.64 sec | 1 min 41.19 sec | 1 min 43.51 sec </code></pre> <p>++This is considered the cache Seeding call. It is expected to be slower than the rest.</p> <p>I'd say the results speak for themselves. COUNT(Id) usually edges out the others. Adding a Where clause dramatically decreases the access time even if it's a clause you know will evaluate to true. The sweet spot appears to be COUNT(Id)... WHERE Id IS NOT NULL.</p> <p>I would love to see other peoples' results, perhaps with smaller tables or with where clauses against different fields than the field you're counting. I'm sure there are other variations I haven't taken into account.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload