Re: Question about indexes
Posted by:
Rick James
Date: February 01, 2014 09:41AM
> I should have is the combined userid, year
Absolutely.
> I guess if you set your indexes correct, you really don't need one. Is that a true statement?
It depends. If the 'natural' PK is long, it may be more efficient to add an auto-inc. If rows can be duplicated entirely (eg, for some kind of log), then there is nothing that is naturally UNIQUE.
Many of the details about indexes (including PK) I am discussing with you are quite important when a table has millions of rows. It is good to apply the techniques to all tables -- to get into the habit, and to prepare for tables that may grow. It is more painful to change indexes when the table is huge.
> INDEX(userid, year, month, day)
> and just to confirm, from my understanding I wouldn't also need the index just for USERID.
Correct. In a query with "WHERE USERID=..." without mentioning year, etc, the optimizer will use that INDEX. Run EXPLAIN SELECT ... to see that it does so.
On the other hand, "WHERE year=..." without userid will not use that index. Only the first field(s) of an index can be used.
> Will this help with INSERTs as well?
An INSERT must update both the data and any indexes. More indexes -> slower. Random indexes -> slower. You still haven't shown us SHOW CREATE TABLE. So, I don't know whether you are using MyISAM or InnoDB. With MyISAM, a slow SELECT can block an INSERT, thereby landing it in the slowlog. Ordinarily, if you are not doing hundreds (or more) INSERTs per second, INSERT speed (including INDEX overhead) is not an issue.
Also, that 250K-row UPDATE without an index would block INSERTs on either MyISAM or InnoDB, thereby landing the INSERT in the slowlog. In that sense, INDEXes help INSERTs -- but only indirectly.
Thanks for being such a receptive student.