MySQL Forums
Forum List  »  Newbie

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.

Options: ReplyQuote


Subject
Written By
Posted
January 29, 2014 11:08PM
January 30, 2014 12:01AM
January 30, 2014 12:26AM
January 30, 2014 06:38AM
January 31, 2014 10:40AM
January 31, 2014 05:31PM
January 31, 2014 07:12PM
January 31, 2014 07:59PM
January 31, 2014 10:14PM
Re: Question about indexes
February 01, 2014 09:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.