Mariadb – Performance impact field-order

mariadbperformancequery-performance

I'm building a Go-based system, with MariaDB as the storage back-end. As the responses from MariaDB/MySQL are the slowest part of the whole application I was wondering a specific thing;

Does the field order in a query matter? Say I have this simpler query (but might be much bigger):

SELECT u.*, cu.FirstName, cu.Surname, uu.FirstName, uu.Surname
FROM UserContact u
LEFT JOIN User cu ON cu.ID = u.CreatedByID
LEFT JOIN User uu ON uu.ID = u.UpdatedByID
WHERE u.UserID = ?

Would u.* be faster before or after the custom fields? Or would the query optimiser tweak it correctly?

It might be trivial speed differentials, but I really care about speed, and as a SQL query easily has up to 1ms response time in my app's requests that normally are only a few microseconds themselves; every tweak in the usage of the SQL queries might be a big help in the overall performance of the system. I'm already buffering a lot of queried data with (in)validated cache, but sometimes in the back-end still need to run custom logic.

I can't test the performance in a Go benchmark at the moment, as in the latest macOS High Sierra the whole OS kernel panics on a filesystem crash if the mysqld gets called too excessively.

Best Answer

The order of things in SELECT ... does not matter.

The main performance killers:

  1. Having to fetch from disk.
  2. Fetching more rows than needed.
  3. Fetching more columns than needed. That is, SELECTing * when you don't need all the columns.
  4. Everything else is relatively insignificant -- expressions, ordering, etc.

In particular...

FROM UserContact u
LEFT JOIN User cu ON cu.ID = u.CreatedByID
LEFT JOIN User uu ON uu.ID = u.UpdatedByID
WHERE u.UserID = ?

Needs these indexes to tackle #2, above (unless they are already the PRIMARY KEYs)

UserContact: INDEX(UserID)
User:  INDEX(ID)

Please provide SHOW CREATE TABLE if you need to discuss further.

Related Question