CODEWITHSUNDEEP
mysqlsql
user14352137
user14352137

Reputation:

MySQL Joining multiple tables with where clause result is too slow

Let's say I have the following structure of tables;

-----------------------------------------------------------
-                       table1                            -
- id(PK) | date_created | column1 | t2_id | t3_id | t4_id -
- 1        ...            sample       1       1      1   -
- 2        ...            sample       2       2      2   -
- 3        ...            sample       3       3      3   -
-----------------------------------------------------------

-------------------     --------------------    -------------------- 
-     table2       -    -     table3       -    -     table4       -
- id(PK) | column1 -    - id(PK) | column1 -    - id(PK) | column1 -
- 1        sample  -    - 1        sample  -    - 1        sample  -
- 2        sample  -    - 2        sample  -    - 2        sample  -
- 3        sample  -    - 3        sample  -    - 3        sample  -
--------------------    --------------------    --------------------

and I want a result of;

------------------------------------------------------------------------------
-date_created | column1 | table2.column1 | table3.column1  | table4.column1  -
-   ...         sample        sample           sample             sample     -
-   ...         sample        sample           sample             sample     -
-   ...         sample        sample           sample             sample     -
------------------------------------------------------------------------------

So I used the following query;

SELECT 
table1.date_created, table1.column1, 
table2.column1,
table3.column1, 
table4.column1
FROM 
table1, table2, table3, table4
WHERE
table2.id = table1.t2_id AND
table3.id = table1.t3_id AND
table4.id = table1.t4_id
ORDER BY table1.date_created DESC

I still have other tables that are connected to table1. The problem is the result is too slow even though there are only three results. I'm not that good at databases maybe I'm doing it wrong.

Upvotes: 0

Views: 72

Answers (4)

Polar
Polar

Reputation: 3537

First of all, always remember that using JOIN on a large table will make your query very slow. Your database structure is already considered large since you have 16 tables that are related to the table1(That makes them 17) plus the number of data.

You only have four rows in every table at the moment, it is worth it if you mention the span time of the result of your query so we have a baseline on how slow is it and we don't just assume.

Anyway, dividing those tables into multiple to avoid duplicate entries is a good normalization. However, you must think again about the table1. This is the core of the relation between your tables so you must know how to handle this properly.

The solution is to create an index of every table, see Gordon Linoff answer. Then use LEFT JOIN so you get every row even the corresponding table is empty or the ID didn't match.

SELECT 

t1.date_created, 
t1.column1, 
t2.column1, 
t3.column1, 
t4.column1

FROM table1 t1 

LEFT JOIN table2 t2 ON t2.id = t1.t2_id 
LEFT JOIN table3 t3 ON t3.id = t1.t3_id 
LEFT JOIN table4 t4 ON t4.id = t1.t4_id

ORDER BY t1.date_created DESC;

Then finally, pick only the column from the specific table that you really need. This is how you can enhance your query. Don't just query it all, be smart on which data you just need.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

First, rewrite the query using explicit JOINs. There is no need whatsoever to use archaic syntax:

SELECT t1.date_created, t1.column1, 
       t2.column1, t3.column1, t4.column1
FROM table1 t1 JOIN
     table2 t2
     ON t2.id = t1.t2_id JOIN
     table3 t3
     ON t3.id = t1.t3_id JOIN
     table4 t4
     ON t4.id = t1.t4_id
ORDER BY t1.date_created DESC;

Then you want indexes on all the tables apart from table1 for the joins:

create index idx_table2_id_column1 on table2(id, column1);
create index idx_table3_id_column1 on table3(id, column1);
create index idx_table4_id_column1 on table4(id, column1);

These indexes are not necessary if the id is the primary key of the table.

Finally, you can use an index on table1 as well to handle the order by:

create index idx_table1_date_created_t2id_t3id_t4id_column1 on table1(date_created desc, t2_id, t3_id, t4_id, column1);

The first key is the most important here. The rest just allow the index to cover the query which can be a modest win for performance.

Upvotes: 1

Rick James
Rick James

Reputation: 142298

For starters, do

ALTER TABLE table1
    ADD INDEX(date_created);

Upvotes: 0

Dean Taler
Dean Taler

Reputation: 763

SELECT 
table1.date_created, table1.column1, 
table2.column1,
table3.column1, 
table4.column1
FROM 
table1
inner join table2 on table2.id = table1.t2_id 
inner join table3 on table3.id = table1.t3_id
inner join table4 on table4.id = table1.t4_id
ORDER BY table1.date_created DESC

Upvotes: 0

Related Questions