General Question on Indexing

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I would like to hear your opinion on indexing all of the fields inside a table. The case is I found a “SELECT * from table where id=” query in one of our application. After some investigation, I found out that 1) the table does not grow nor shrink and is not updated 2) all of the data are really needed so it is impossible to simplify the selected fields and 3) currently id is the only index. the table has 60 records and 6 fields all smallint type. The current query works fine and really fast. I am just wondering if it is safe to add all of the fields in an index so that I could squeez a little bit more performance. The thing that I do not know is if there are hidden drawbacks to this.

Thanks!

2

Is the table MyISAM or InnoDB?

If it’s InnoDB then the records are already ordered by primary key since that is how InnoDB physically store data.

If it’s MyISAM then yes you could create a compound index of all columns, especially since you have so few rows and so little data that the memory consumption will be negligible. At the same time there are so few rows that I wonder how much performance you will get out of it.

But do try and please report back, it’s interesting to hear.