Index table in mysql

Question

I used MySQL and here's my table

|Col A(Primary)| Col B  | Col C      | Col D |
|1             | SOCCER | 2012-12-01 | P |
|2             | VOLLEY | 2012-12-14 | P |
|3             | SOCCER | 2012-12-01 | L |
|4             | VOLLEY | 2012-12-10 | P |
|3             | SOCCER | 2012-12-13 | L |

This table contain million rows. Frequently i used Col B and Col C as condition in my query.

I want to index that table. What column that must indexed? what type of index(primary, unique, fulltext)?

Nothing MUST be indexed on anything. This question is really basic (and asked a lot - do a search). There's lots of info about it around and only you can decide what would be useful for you and what is not. — sashkello
– sashkello, Commented May 26, 2013 at 1:36
@guunita . . . To answer this question, more informaiton is needed. How many different values do columns B and C take? What are examples of the filtering conditions? Are the two columns always used together or typically used separately? — Gordon Linoff
– Gordon Linoff, Commented May 26, 2013 at 1:41
Thx for response all. @Gordon Linoff Col B just have 5 varian data and Col C have lot of varian. yes its always used together. Anyway, is index works on agregate function? — guurnita
– guurnita, Commented May 26, 2013 at 1:49
You can read about indexes and group by'sin MySQL here dev.mysql.com/doc/refman/5.5/en/group-by-optimization.html. — Gordon Linoff
– Gordon Linoff, Commented May 26, 2013 at 1:54

hjpotter92 · Accepted Answer · 2013-05-26 02:21:44Z

1

Column A is already PRIMARY KEY, so no more indexing it needed. You certainly can index the DATE column with normal KEY or INDEX.

As to Col. B, I'd suggest you use normalized form and store the text values in a different table and reference those using the INT id from that table in this table. It'll definitely save both the storage space and time taken to search the database. A good example of normalized table can be found on Wikipedia.

As for last column, since you don't filter results for that, I don't think you need an index there.

After 1NF form has been applied an easy INDEX method will work.

answered May 26, 2013 at 2:21

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Natalie Adams Over a year ago

Would an enum type offer the same efficiency improvements? Since technically MySQL stores the value as a number.

hjpotter92 Over a year ago

@NathanAdams For column D, yeah. As for Col B, I'd need a little more details.

Gordon Linoff · Accepted Answer · 2013-05-26 02:27:01Z

0

You are going to have a hard time with indexing this query. ColB has only five values, which is a very high selectivity. Unfortunately, indexes are not very useful on such a column.

You should put in a compound index on (ColB, ColC). This will work for queries where ColB is used with an = or in clause and you have any condition on ColC (except perhaps for <>). However, if a typical query is going to be processing many rows in the table, say 5% or something like that, then an index may not help at all.

answered May 26, 2013 at 2:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Rick James Over a year ago

Cookbook for building such an index.

Collectives™ on Stack Overflow

Index table in mysql

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related