Optimize sql query with multiple joins on single table

Question

tbl_picklist table

id     value
1      John
2      Mumbai
3      San Diego
4      CA
5      Jerry

tbl_user table

id     picklist_id_name    email
1      1                   [email protected]
2      8                   [email protected]

tbl_profile table

id     user_id   picklist_id_addr    picklist_id_addr2   designation
1       1         3                   5                   Dev
3       2         7                   3                   QA

Picklist is table where you will get values of address, address2 and name, I wrote query which give me desired result

SELECT
    u.email,
    p1.value AS username,
    p2.value AS addr1,
    p3.value AS addr2
FROM tbl_user u
LEFT JOIN tbl_picklist AS p1
    ON u.picklist_id_name = p1.id
LEFT JOIN tbl_profile pr
    ON pr.user_id = u.id
LEFT JOIN tbl_picklist AS p2
    ON p2.id = pr.picklist_id_addr
LEFT JOIN tbl_picklist AS p3
    ON p3.id = pr.picklist_id_addr2

Result:

email            username      addr1        addr2
[email protected]    John          Mumbai       San Diego
[email protected]  Jerry         CA           Mumbai

To get above result, i needed to write 3 joins on picklist table, is there any other way in which i can get result only with the one join on picklist table? I have this kind of table structure in application, many values are stored in picklist table, its taking to much time to execute this kind of queries.

The problem aren't the joins, it is that you haven't tuned your query. Have you applied any index yet? — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 22, 2017 at 12:49
please update your post with the output from explain analyze and provide the exact index definitions that are applied to those tables. — Haleemur Ali
– Haleemur Ali, Commented Aug 22, 2017 at 13:21

N.Dov · Accepted Answer · 2017-08-22 13:19:21Z

2

The current Database structure isn't correct. If you have rights, you should rebuild it to optimize your queries and to follow normalization forms. For example, picklist_id_addr, picklist_id_addr2 and their values should be in one table, which you would join on picklist_id_name or user_id.

answered Aug 22, 2017 at 13:19

N.Dov

662 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2017-08-22 13:21:46Z

I was going to suggest adding a series of indices to your tables with the hopes of speeding up the query, until I realized that the bigger problem is your current table design. The biggest offender is the following table tbl_picklist:

id     value
1      John
2      Mumbai
3      San Diego
4      CA
5      Jerry

You are storing all sorts of information about a user in a single unnormalized column. Instead, I would recommend just having two tables, one for user information and the other for address information. Link users to addresses via a primary-foreign key relationship. For example:

tbl_user
id | email           | designation | address_id
1  | [email protected]   | dev         | 1
2  | [email protected] | QA          | 2

and

tbl_address
id | city      | state     | country
1  | San Diego | CA        | USA
2  | Mumbai    | Bollywood | India

Under this simplified schema, you would only need a single query to build out entire user records:

SELECT
    t1.email,
    t1.designation,
    t2.city,
    t2.state,
    t2.country
FROM tbl_user t1
INNER JOIN tbl_address t2
    ON t1.address_id = t2.id

As an example, if you still needed to optimize the above query, or something similar to it, you would need only worry about a single join, and no WHERE clause. In this contrived example, an index on tbl_user.address_id should do the trick.

Collectives™ on Stack Overflow

Optimize sql query with multiple joins on single table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related