CODEWITHSUNDEEP
mysql
guurnita
guurnita

Reputation: 457

Index table in mysql

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)?

Upvotes: 0

Views: 63

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80629

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.

Upvotes: 1

Related Questions