Search code examples
mysqlquery-performance

MySQL query performance with reference tables

For the following 2 tables structures, assuming the data volume is really high:

cars table
Id | brand name | make year | purchase year | owner name

Is there any query performance benefit with structuring it this way and joining the 2 tables instead?

cars table
Id | brand_id | make year | purchase year | owner name

brands table
Id | name

Also, if all 4 columns fall in my where clause, does it make sense indexing any?

Solution

  • I would at least have INDEX(owner_name) since that is very selective. Having INDEX(owner_name, model_year) won't help enough to matter for this type of data. There are other cases where I would recommend a 4-column composite index.

    "data volume is really high". If you are saying there are 100K rows, then it does not matter much. If you are saying a billion rows, then we need to get into a lot more details.

    "data volume is really high". 10 queries/second -- Yawn. 1000/second -- more details, please.

    2 tables vs 1.

    • Data integrity - someone could mess up the data either way
    • Speed -- a 1-byte TINYINT UNSIGNED (range 0..255) is smaller than an average of about 7 bytes for VARCHAR(55) forbrand. But it is hardly enough smaller to matter on space or speed. (And if you goof and makebrand_idaBIGINT`, which is 8 bytes; well, oops!)

    Indexing all columns is different than having no indexes. But "indexing all" is ambiguous:

    • INDEX(user), INDEX(brand), INDEX(year), ... is likely to make it efficient to search or sort by any of those columns.
    • INDEX(user, brand, year), ... makes it especially efficient to search by all those columns (with =), or certain ORDER BYs.
    • No index implies scanning the entire table for any SELECT.

    Another interpretation of what you said (plus a little reading between the lines): Might you be searching by any combination of columns? Perhaps non-= things like year >= 2016? Or make IN ('Toyota', 'Nissan')?

    Study http://mysql.rjweb.org/doc.php/index_cookbook_mysql

    An argument for 1 table

    If you need to do 

    WHERE brand = 'Toyota'
  AND year  = 2017

    Then INDEX(brand, year) (in either order) is possible and beneficial.

    But... If those two columns are in different tables (as with your 2-table example), then you cannot have such an index, and performance will suffer.