Finding Duplicates Of Table data : Optimized Way?

Question

I have a main table with 80lacs record (say TABLE : MAIN_TABLE) I want to fire many queries in which i need to find all records matching approx 10 criteria that are register in 6 months(11 lacs)

I have created a composite index on 2 column (col1 , col2) , however still queries is talking time in executing approx 30-50 seconds.

there are approx 12 index on this table and approx 60columns. when i use explain , it shows 5102 rows will be examined and its using index.

Solution I use : i decide to create a trigger which will insert into this a new table(MAIN_TABLE_ACTIVE) with only one index and last 6 months records and limited columns(12) when i fire query on this tables results are coming in order of 2-6 seconds.

Question : is this best approach as i use table of 11lacs instead of 80lacs ?

Drawbacks : overhead for triggeer :-(

suggest any new approach or please comment on my approach of this problem.

score 0 · Answer 1 · answered Oct 04 '12 at 17:54

0

If you don't need to show data in real time,you can use another table which will have desired data(similar to the table which you are creating with trigger).You can populate this mini table by some daily cron.

If you need to show data in real time then you might have to relook your schema because real time computation on this table is not a good idea.

answered Oct 04 '12 at 17:54

WordsWorth

872
1
11
23

Because of my requirement , i cant use cron. what the problem if i want real time data ? – chicharito Oct 04 '12 at 17:58
don't you think triggers will affect your write performace. – WordsWorth Oct 04 '12 at 18:04
I am thkinking to write a trigger only on slave and not on master – chicharito Oct 04 '12 at 18:05
then i don't think you will have any problem. – WordsWorth Oct 04 '12 at 18:08
is there any better approach ? – chicharito Oct 04 '12 at 18:12

score 0 · Accepted Answer · answered Oct 05 '12 at 19:37

0

I have used the same table making sure i fetch the records on order of index

answered Oct 05 '12 at 19:37

chicharito

1,047
3
12
41

Finding Duplicates Of Table data : Optimized Way?

2 Answers2