Inner Join on three tables takes too long in mysql

Question

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

where do you need the data from? can you show me the structure, I think nested queries would do the job. — Danyal Sandeelo, Aug 24 '15 at 07:48
@DanyalSandeelo edited the question, more specific query is provided. — Anil Bhaskar, Aug 24 '15 at 10:22
Do not say `blah_blah_filter`; we need to see the filter in order help you! Also provide `SHOW CREATE TABLE` so we can see the Engine, the indexes, and the datatypes. — Rick James, Aug 24 '15 at 22:42
Also, what are the data types used in `WHERE` clause? While debatable, it is advised to [join tables with integers](http://sqlinthewild.co.za/index.php/2011/02/15/are-int-joins-faster-than-string-joins-2/) instead of long strings/varchars/texts. — Parfait, Aug 25 '15 at 00:57

score 1 · Answer 1 · edited Aug 24 '15 at 07:58

1

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

edited Aug 24 '15 at 07:58

Drew

24,851
10
43
78

answered Aug 24 '15 at 07:50

japzdivino

1,736
3
17
25

I don't think it makes any diference. I am using the same query. – Anil Bhaskar Aug 24 '15 at 07:53
did you try it ? , also if posible try to add indexes in tables. – japzdivino Aug 24 '15 at 07:55
OP is using older syntax with `WHERE`, the implicit join, which is equivalent to `INNER JOIN`. See [WHERE vs JOIN](http://stackoverflow.com/questions/121631/inner-join-vs-where). – Parfait Aug 25 '15 at 00:36

score 0 · Answer 2 · edited Aug 24 '15 at 08:31

0

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;

edited Aug 24 '15 at 08:31

Arun Palanisamy

5,281
6
28
53

answered Aug 24 '15 at 07:45

Varun Selva

46
1
8

The Order of the `where` condition doesn't matter after implementing [Cost Based Optimiser](https://dev.mysql.com/doc/refman/5.1/en/optimizer-issues.html) – Arun Palanisamy Aug 24 '15 at 07:53

Inner Join on three tables takes too long in mysql

Edit (btw, huh?):

2 Answers2