1

I have a mysql innodb table where I'm performing a lot of selects using different columns. I thought that adding an index on each of those fields could help performance, but after reading a bit on indexes I'm not sure if adding an index on a column you select on always helps.

I have far more selects than inserts/updates happening in my case.

My table 'students' looks like:

id | student_name | nickname | team | time_joined_school | honor_roll

and I have the following queries:

# The team column is varchar(32), and only has about 20 different values.
# The honor_roll field is a smallint and is only either 0 or 1.
1. select from students where team = '?' and honor_roll = ?;

# The student_name field is varchar(32).
2. select from students where student_name = '?';

# The nickname field is varchar(64).
3. select from students where nickname like '%?%';

all the results are ordered by time_joined_school, which is a bigint(20).

So I was just going to add an index on each of the columns, does that make sense in this scenario?

Thanks

user1219278
  • 1,859
  • 5
  • 22
  • 27

2 Answers2

1

Yes indexes help on accerate your querys. In your case you should have index on:

1) Team and honor_roll from query 1 (only 1 index with 2 fields)

2) student_name

3) time_joined_school from order

For the query 3 you can't use indexes because of the like statement. Hope this helps.

ericpap
  • 2,917
  • 5
  • 33
  • 52
1

Indexes help the database more efficiently find the data you're looking for. Which is to say you don't need an index simply because you're selecting a given column, but instead you (generally) need an index for columns you're selecting based on - i.e. using a WHERE clause (even if you don't end up including the searched column in your result).

Broadly, this means you should have indexes on columns that segregate your data in logical ways, and not on extraneous, simply informative columns. Before looking at your specific queries, all of these columns seem like reasonable candidates for indexing, since you could reasonably construct queries around these columns. Examples of columns that would make less sense would be things phone_number, address, or student_notes - you could index such columns, but generally you don't need or want to.

Specifically based on your queries, you'll want student_name, team, and honor_roll to be indexed, since you're defining WHERE conditions based on the values of these columns. You'll also benefit from indexing time_joined_school if, as you suggest, you're ORDER BYing your queries based on that column. Your LIKE query is not actually easy for most RDBs to handle, and indexing nickname won't help. Check out How to speed up SELECT .. LIKE queries in MySQL on multiple columns? for more.

Note also that the ratio of SELECT to INSERT is not terribly relevant for deciding whether to use an index or not. Even if you only populate the table once, and it's read-only from that point on, SELECTs will run faster if you index the correct columns.

Community
  • 1
  • 1
dimo414
  • 47,227
  • 18
  • 148
  • 244