vendredi 2 janvier 2015

Mysql might have too many indexes


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





Aucun commentaire:

Enregistrer un commentaire