Indexing table with duplicates MySQL/SQL Server with millions of records

Posted by Tesnep on Stack Overflow See other posts from Stack Overflow or by Tesnep
Published on 2010-02-05T13:16:30Z Indexed on 2010/05/30 19:52 UTC
Read the original article Hit count: 175

Filed under:
|
|
|

I need help in indexing in MySQL. I have a table in MySQL with following rows:

ID Store_ID Feature_ID Order_ID Viewed_Date Deal_ID IsTrial
The ID is auto generated. Store_ID goes from 1 - 8. Feature_ID from 1 - let's say 100. Viewed Date is Date and time on which the data is inserted. IsTrial is either 0 or 1.
You can ignore Order_ID and Deal_ID from this discussion.

There are millions of data in the table and we have a reporting backend that needs to view the number of views in a certain period or overall where trial is 0 for a particular store id and for a particular feature.

The query takes the form of:

select count(viewed_date) 
from theTable
where viewed_date between '2009-12-01' and '2010-12-31'
and store_id = '2' 
and feature_id = '12'
and Istrial = 0

In SQL Server you can have a filtered index to use for Istrial. Is there anything similar to this in MySQL? Also, Store_ID and Feature_ID have a lot of duplicate data. I created an index using Store_ID and Feature_ID. Although this seems to have decreased the search period, I need better improvement than this. Right now I have more than 4 million rows. To search for a particular query like the one above, it looks at 3.5 million rows in order to give me the count of 500k rows.

PS. I forgot to add view_date filter in the query. Now I have done this.

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql