Using table index

[janjini.10]

Is it required to use index when the MySQL table has only 100 rows? Justify your answer.

[RomelynCastillo]

Hi,Index is used to speed up query.If particular column is used often in queries and table has lot of data.Then it is recommended to create index for that column of the table. It will speed up the query.

[cereal]

In addition, check the documentation: http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html

Especially:

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.

Anyway it's the query optimizer that will decide if the query will use the available indexes or not, you can force it to use a specific index or to ignore them all, read these links for more information:

• http://dev.mysql.com/doc/refman/5.6/en/index-hints.html
• http://dev.mysql.com/doc/refman/5.6/en/how-to-avoid-table-scan.html

So, at end, test your queries, use the explain select ... and see when you get better results.