CODEWITHSUNDEEP
mysqldatabasedatabase-design
Nyxynyx
Nyxynyx

Reputation: 63599

MySQL Index: 1 keyname multiple columns OR 1 keyname per column?

I am trying to speed up my MySQL tables by using indices. When I add an index using PHPmyadmin, there are 2 columns, keyname and column. I have a few table columns that I want indexed.

Problem: What is the difference between having 1 keyname per column, and 1 keyname for all the columns I want indexed? I notice that using 1 keyname for all the indexed columns work the best. What is happening?

The first column in the keyname with multiple columns is the first column that appears in my MySQL query. I think this has something to do with using 1 keyword multiple columns, but I'm not sure why this is required?

Upvotes: 2

Views: 797

Answers (1)

Hugo
Hugo

Reputation: 2601

Multi-column indexes ("1 keyname for all the columns") are different than single-column indexes for each column. It all depends on your queries.

If you're querying by multiple columns frequently, you want to index that combination. For example if you query:

SELECT * FROM users WHERE (age > 18 AND name = "Johnnie Walker")

you need to add the index on two columns: (age,name)

Upvotes: 1

Related Questions