1

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.

1
  • The vast majority of database systems are disk limited! Check out your execution plan with the field order changed. I don't think it will make much of a difference. You could take a timestamp before and after query execution to "factor out" the database? Not much comfort I know, but "yae cannae beat the laws o' physics Jim!" :-) – Vérace Oct 13 '17 at 18:44
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.