Fetching records from the MySQL database from 10 tables

Question

I have a database with 20 tables inside it. I want to fetch records from 10 related tables at a time, and I am using Hibernate. What is the best solution: to write a single query using join with select, or write 2 or 3 simple queries? I want to select the better solution for my service.

score 1 · Accepted Answer · answered May 05 '11 at 05:46

1

If the tables are related to each other, I would try using JOINS, they provide better (much better) performance than just using nested queries.

answered May 05 '11 at 05:46

PachinSV

3,570
2
28
40

score 1 · Answer 2 · edited May 23 '17 at 12:25

1

Perform Inner joins as often as possible when looking to combine data from multiple tables. From what I understand they are more efficient than outer joins.

INNER JOIN vs LEFT JOIN performance in SQL Server

This post goes in depth to explaining the reasons why.

GL

edited May 23 '17 at 12:25

Community

1
1

answered May 05 '11 at 05:50

Matthew Cox

13,232
9
52
72

score 0 · Answer 3 · edited Jan 11 '12 at 13:35

Try to use, like

        select *
        from producer
        inner join director on director .entityId = producer.producerId
        left outer join name on director .entityId = name.entityId
        left outer join address on director .entityId = address.entityId
        left outer join phone on director .entityId = phone.entityId
        left outer join email on director .entityId = email.entityId
        where producerId = 1

Fetching records from the MySQL database from 10 tables

3 Answers3