Edit Question - KnowledgeHub

Title Body

Here is my issue, I am selecting and doing multiple joins to get the correct items...it pulls in a fair amount of rows, above 100,000. This query takes more than 5mins when the date range is set to 1 year. I don't know if it's possible but I am afraid that the user might extend the date range to like ten years and crash it. Anyone know how I can speed this up? Here is the query. SELECT DISTINCT t1.first_name, t1.last_name, t1.email FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.CU_id = t2.O_cid INNER JOIN table3 AS t3 ON t2.O_ref = t3.I_oref INNER JOIN table4 AS t4 ON t3.I_pid = t4.P_id INNER JOIN table5 AS t5 ON t4.P_cat = t5.C_id WHERE t1.subscribe =1 AND t1.Cdate >= $startDate AND t1.Cdate <= $endDate AND t5.store =2 I am not the greatest with mysql so any help would be appreciated! Thanks in advance! UPDATE Here is the explain you asked for id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ref PRIMARY,C_store_type,C_id,C_store_type_2 C_store_type_2 1 const 101 Using temporary 1 SIMPLE t4 ref PRIMARY,P_cat P_cat 5 alphacom.t5.C_id 326 Using where 1 SIMPLE t3 ref I_pid,I_oref I_pid 4 alphacom.t4.P_id 31 1 SIMPLE t2 eq_ref O_ref,O_cid O_ref 28 alphacom.t3.I_oref 1 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 alphacom.t2.O_cid 1 Using where Also I added an index to table5 rows and table4 rows because they don't really change, however the other tables get around 500-1000 entries a month... I heard you should add an index to a table that has that many new entries....is this true?

Tags (comma-separated) Cancel