MySQL Forums
Forum List  »  Newbie

Re: Question about indexes
Posted by: Love MySQL
Date: January 31, 2014 07:12PM

Rick James Wrote:
-------------------------------------------------------
> Well, let's think it through.
> * When you INSERT one row into a table with one
> extra INDEX, you need one extra unit of extra
> effort (to update that INDEX).
> * When you UPDATE one row in a table with 250,000
> rows but no INDEX, it takes 250,001 units of work
> (250,000 to find the row, 1 to update it.)
> * When you UPDATE one row into a table with one
> extra INDEX, but that INDEX lets you find the
> index immediately, you need only two units of
> effort -- 1 to find the row, 1 to update it.
>
> That is, the tradeoff is often a little work
> versus a _lot_ of work.

Makes sense, thanks.

> WHERE userid = 435155 AND
> month = 11 AND
> day = 14 AND
> year = 2013;
>
> would benefit from a 'compound' index on those 4
> fields, in any order. Example:
> INDEX(userid, year, month, day)

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 is _usually_ a bad idea to split up dates.
> DATETIME and TIMESTAMP would simplify that WHERE
> clause, and might help other queries.

I was pretty new to mysql when I programmed this app, made a lot of inefficient code such as this. Now that it's getting popular the database is filling up and I am noticing the poor queries. It's on my "to do list" to convert the m/d/y to a datetime. I can only imagine it would help with the performance of the queries.

> > If so, which?
> > The year? -- by itself? why?

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

> > The food? -- won't help the UPDATE you
> presented

So to help an UPDATE or INSERT, the indexes are only going to help the WHERE part of it? Big concept I was missing, thanks for cleaing that up. So the portions your updating don't matter... indexes are only about FINDING the records. Light bulb went off.

> > The food ID? -- Let's see SHOW CREATE TABLE.

Like you said above, the food ID isn't going to matter, isn't that correct? Since it's not part of the WHERE?

> If you have an AUTO_INCREMENT, it should _usually_
> be the PRIMARY KEY, by itself:
> PRIMARY KEY(ID)
> Keep in mind that the PRIMARY KEY is an INDEX, and
> it is UNIQUE.

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?

Thanks a lot for your help, it's appreciated!

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