Re: Question about indexes

[Love MySQL]

Thanks for all the responses. A couple of you have asked for a sample slow query. Here is an example of an UPDATE that was kind of slow (0.6 sec)

UPDATE users_data
SET food = 'MEAT', food_id = 2393, weight = 179, bmi = 25, feel = 0
WHERE userid = 435155 AND
month = 11 AND
day = 14 AND
year = 2013;

In this example, there are about 1000 different foods to choose from, and corresonding ID's. The weight and bmi are self explanatory. The feel is a flag, 0 or 1.

The userid is the user, and the date is in there too.

There are about 250,000 rows in this table storing this info. Before I started trying to optimize this table, there was no index (yikes! I know). I was getting a lot of slow queries from this database, even SELECTS.

I made userid in to an index, and that cut down on the SELECT queries. I haven't seen one since. Now I am working on the UPDATE and the INSERT's... I've seen some UPDATES appear in the slow log (anything over 0.5 sec currently).

I know adding more indexes can hurt your UPDATE and INSERT times, but I don't think this is a large enough table and I only have one index right now. Should I add another? If so, which? The year? The food? The food ID?

Thanks for help!