MySQL select on indexed columns

Question

I'm new to MySQL and have a few questions that I haven't been able to find any information on. Here is what I'm trying to do:

I have a database with 5 columns: id, c1, c2, c3, c4 id is PRIMARY KEY each row is unique across c1, c2, c3, c4 (if there is terminology you guys use for this let me know that too :))

I need to be able to search for an existing row in the table so I thought to put an INDEX on c1 which will reduce the number of rows from maybe 10,000 (100,000 at most) to a couple. My questions are:

1. How many rows do you estimate before the performance benefit of an index becomes "worth it"? Ballpark/anecdotal answers are fine.

2. Do I need any special syntax to tell MySQL to use the INDEX? Right now I am doing a "SELECT id FROM table WHERE c1='blah' AND c2='blah' AND c3='blah' AND c4='blah'" and I'm not sure if the INDEX is helping the query at all.

3. Any other suggestions for better ways to query the table welcome, just keep in mind that I'm a newb!! :)

Thanks in advance!

Answer 1

I guess an index being "worth it" depends on how fast you need the query to run. With only hundreds of records, it'll be "pretty" fast either way. But in any case, an index should be faster because you avoid having to read all (in some cases any) records on disk. Even a query that takes 0.02s without an index might execute in 0.0004s with one. Once you get into the thousands of rows, you'll definitely want an index. So, you may as well always use an index for any columns used in JOINs or WHERE clauses, no matter how many rows.

MySQL should use your index. You can check by running an EXPLAIN query. (Just add EXPLAIN in front of your query, like EXPLAIN SELECT * FROM....) It will show what index is being used, if any. Keep in mind that you have to use the columns in the order they're specified in the composite index. So if your index is c1-c2-c3-c4, you can't just use it for c4 without using the other columns too.

So, SELECT * FROM table WHERE c1 = 'foo' AND c4 = 'bar' would use your index for c1 = 'foo', but it couldn't use it for c4 = 'bar' because you aren't using c2 or c3. Since you're using all four columns in the index, the index should be used for all of them. Make sense?