Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an InnoDB table with around ~3.7m rows in it. Here are my current indexes:

+-------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+-
| Table       | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment 
+-------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+-
| hotel_avail |          0 | PRIMARY       |            1 | id            | A         |     3720035 |     NULL | NULL   |      | BTREE      |         |               |
| hotel_avail |          0 | PRIMARY       |            2 | nights        | A         |     3720035 |     NULL | NULL   |      | BTREE      |         |               |
| hotel_avail |          1 | hotelname     |            1 | hotelname     | A         |        5914 |     NULL | NULL   |      | BTREE      |         |               |
| hotel_avail |          1 | destair       |            1 | destair       | A         |         347 |     NULL | NULL   |      | BTREE      |         |               |
| hotel_avail |          1 | boardbasis    |            1 | boardbasis    | A         |        2436 |     NULL | NULL   | YES  | BTREE      |         |               |
| hotel_avail |          1 | iso           |            1 | iso           | A         |         347 |     NULL | NULL   | YES  | BTREE      |         |               |
| hotel_avail |          1 | cheapestprice |            1 | cheapestprice | A         |      372003 |     NULL | NULL   |      | BTREE      |         |               |
| hotel_avail |          1 | dates         |            1 | checkindate   | A         |        6262 |     NULL | NULL   | YES  | BTREE      |         |               |
| hotel_avail |          1 | dates         |            2 | checkoutdate  | A         |        6262 |     NULL | NULL   | YES  | BTREE      |         |               |
| hotel_avail |          1 | engineid      |            1 | engineid      | A         |         347 |     NULL | NULL   |      | BTREE      |         |               |
| hotel_avail |          1 | itemcode      |            1 | itemcode      | A         |       28182 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+-

Does it look like overkill? Can I merge some of these indexes further? I've seen in InnoDB you can create a PRIMARY KEY with several fields?

I'm always selecting on several of these fields and filtering the results using WHERE and JOIN a few of them as well.

share|improve this question
    
Your best bet would be to evaluate the queries that are being performed and create (possibly composite) indexes best on high traffic queries. –  Kermit Mar 26 '13 at 1:34
    
You also want to create indexes on columns with similar data which can be grouped together to make the indexes more efficient –  ssmusoke Mar 26 '13 at 6:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.