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