I have a product table where I have created the following indexes:
product_category column full text index: I want to be able to retrieve products by category faster. Example of a field value: ["Clothing >> Women's Clothing >> Women_Tops >> Women_TShirtsTops >> Candies by Pantaloons Shirts"]
title full-text index: Allow user to search the product by title
price index normal: Allow for faster sorting by price
Primary index is automatically generated on product_id
Everything is working fine but I just wanted to get advice on is a bad design choice to have so many indexes in one table? As the table size grows up to 5 million, would it cause any performance issues just because of having multiple indexes?
Sure, it is OK to have multiple indexes. But, except for rare situations, only one index will be used for one SELECT.
An FT index, if appropriate, will be used first. It mostly does not matter if you have any other indexes.
If the query is using FT index, the index on price for ordering will not be used.
An INSERT has some extra overhead if there are extra indexes. But usually the indexes are worth having. That is, don't avoid adding indexes just because of the table size of Insert activity. Do avoid adding indexes that won't be used.
If we could see a sampling of your queries, we might be able to provide more details.
I use MySQL's slowlog for identifying queries that are having the most impact on the system. But no tool will tell you how to speed up a slow query.