MySQL Forums
Forum List  »  Newbie

Re: Question about indexes
Posted by: Rick James
Date: January 31, 2014 07:59PM

> I will give it a shot... but isn't indexing the month/day/year going to add a lot more overhead then say, just the year?

It's more overhead to have
INDEX(lastname, firstname)
than to have just INDEX(lastname), but the former will find me faster in
WHERE lastname = 'James' and firstname = 'Rick'
much faster than having to scan through all the James's.

> No idea, just a guess. Even if only the year is indexed, why would that NOT help? It seems like it would.

First, note that MySQL almost never uses two INDEXes in one query. That is, if you had
INDEX(userid),
INDEX(year)
the optimizer would pick one of them and ignore the other. `userid` sounds like it is more selective, hence more likely to be picked.

When a field has very few values (such as `year`), the optimizer _probably_ will ignore an index on just that field -- it is _likely_ to be faster to scan all 250K rows and skip any that don't satisfy "AND year=...". (There is no fixed cutoff. If the table has more than ~20% of the rows with the desired `year`, the index will be ignored.)

A common mistake by newbies is to INDEX each field individually. I have just given you two reasons why such indexes may be useless.

INDEXes should be designed based on your queries (mostly SELECTs, but also UPDATE, as in your example, and DELETE). There are a lot more guidelines on how to make good indexes; show us some more queries; we can discuss further.

> indexes are only going to help the WHERE part of it?

Mostly. In _some_ cases, they help with GROUP BY and ORDER BY.

> indexes are only about FINDING the records

Well said !

> For this table, I don't have an auto_increment. What are you talking about here with regard to the auto_increment? Something I should be adding to the table? I new field?

Sorry to distract you... `id` is _conventionally_ (but not always) the name of a field declared thus:
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

A table _should_ (some say "must") have a PRIMARY KEY. The main two candidates for a PK are:
1. The "natural PK": Whatever field (or combination of fields) will uniquely identify each row of the table.
2. An artificial AUTO_INCREMENT.
There are further details on choosing between the two, but I don't want to overwhelm you yet.

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
Re: Question about indexes
January 31, 2014 07:59PM
January 31, 2014 10:14PM
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.