I am using the php framework Laravel for my application, together with mysql 5.6, in order to have full-text search with innoBD.
But I am not sure if I have too many indexes.
My application allows users to upload products for sale. So the table that will be queried the most, Select / inserts is my products table.
The Table looks like this, (Removed a few rows in this example).
Product
p_id - (int) Primary key
Used to filter products, new/old – asc/desc
state_id - (int) index key & foreign key
Links to primary key in state Table in order to get the state name, also used for filtering when searching products
city_id - (int) index key & foreign key
Links to primary key in city Table in order to get the city name, also used for filtering when searching products
cat_id - (int) index key & foreign key
Links to primary key in category Table in order to get the category name, also used for filtering when searching products
subcat_id - (int) index key & foreign key
Links to primary key in subcategory Table in order to get the subcategory name, also used for filtering when searching products
title - (varchar) FULLTEXT search
Used for text search, when searching/filtering products
seller_id - (int) index key & foreign key
Links to primary key in users table in order to get the sellers username
product_type - (tinyint) index key
Used when searching/filtering products, if a product is an ad or auction
price - (int) index key
Used when searching/filtering products
end_time - timestamp
is_active - (tinyint) index key
Used when searching/filtering products
So when doing a search for products there will be a total of 4 inner joins
, I am not displaying the sellers name in search results. Only inner joining, state / city / category / subcategory.
As for now I have a total of 8 index keys and 5 foreign keys.
My first question: Do I need to put a index key on all columns that has a foreign key?
I am also thinking of adding a few composite index.
composite index 1: (title, state_id, city_id, cat_id, subcat_id, product_type , is_active)
composite index 2: (title, state_id, city_id, product_type, is_active)
As for now it looks like most users only do a text search. So the most common query is a fulltext search where is_active = 1
Best Answer
Rather than try to guess, you should download Percona Tools.
You should use the tool pt-duplicate-key-checker. It will compare all the indexes for you and give your the ALTER TABLE commands to drop the ones you do not need and still maintain fully compliance with all your index search needs. Here is the code to do it:
If you not sure, look at the content of the index removal SQL script first
If the resulting script has no ALTER TABLE commands, you don't need anything done.
If there are ALTER TABLE commands and if you are ready, run the script with this
GIVE IT A TRY !!!