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!