CODEWITHSUNDEEP
mysqlquery-optimizationinner-join
Anil Bhaskar
Anil Bhaskar

Reputation: 3978

Inner Join on three tables takes too long in mysql

I am querying 3 tables in mysql.

SELECT t1.some_col
,t2.some_col
,t3.some_col

FROM t1, t2, t3
WHERE t1.a_col = t2.a_col AND t2.a_col = t3.a_col
AND a filter on t1
AND a filter on t2
AND a filter on t3

Which is taking too much time. (Not even giving results after 10 mins). Any optimization suggesation would be great.

Table t1 (.3m rows), t2 (1.1m) and t3 (258 rows). No tables has indexes, even I am not allowed to create one.

Edit (btw, huh?):

SELECT t2.parent_customerID ,
aggregate(t1.some_columns) 
FROM t1, t2, t3 
WHERE t1.customerID = t2.customerID 
AND t2.parent_customerID = t3.parent_customerID 

AND t1_where_entity_type_customer 
AND t2_parent_customer_belonging_a_region_filter 
AND t3_a_flag_check_on_parent_customer 
GROUP BY t2.parent_customer

Upvotes: 1

Views: 1038

Answers (2)

Varun Selva
Varun Selva

Reputation: 58

Try to change the order of the where conditions.

SELECT t1.some_col
      ,t2.some_col
      ,t3.some_col
FROM t1, t2, t3
WHERE a filter on t1
  AND a filter on t2
  AND a filter on t3
  AND t1.a_col = t2.a_col 
  AND t2.a_col = t3.a_col;

Upvotes: 0

japzdivino
japzdivino

Reputation: 1746

Try this approach.

SELECT t1.some_col
,t2.some_col
,t3.some_col
FROM t1 
INNER JOIN t2 ON t1.a_col = t2.a_col
INNER JOIN t3 ON t2.a_col = t3.a_col
WHERE a filter on t1 AND a filter on t2 AND a filter on t3

Upvotes: 1

Related Questions