I am having a table named "Society"
And 5 master tables "BlockA","BlockB","BlockC","BlockD","BlockE".

The "Society" table contains the foreign key columns of all the 5 master tables.

Now when i write the select query it takes a lot of time to execute, as 5 tables are referencing one table and joining those tables cause in delay result.

select Society.*,BlockA.*,BlockB.*,BlockC.*,BlockD.*,BlockE.* 
from Society 
inner join BlockA on Society.ABlock=BlockA.ID
inner join BlockB on Society.BBlock=BlockB.ID
inner join BlockC on Society.CBlock=BlockC.ID
inner join BlockD on Society.DBlock=BlockD.ID
inner join BlockE on Society.EBlock=BlockE.ID

So how to optmise my table structure or query

5 Years
Discussion Span
Last Post by BitBlt

Not knowing what your table structures are, or what your data looks like, I can only give generic advice, or things to look for.
1. Using "select *" will bring back every column from a table. You can increase speed by only bringing back the columns you actually need.
2. What are the datatypes and sizes of the columns? Large VARCHAR columns are more expensive to return than INTEGER or other numeric columns.
3. Are the ID columns on the joined tables indexed?
4. Are the foreign key columns on the Society table indexed?
5. How many rows are in the Society table? You are bringing back the entire table from Society. Is there any possibility of including a "WHERE" clause to limit the amount of data you're retrieving? As in "Select ... FROM Society inner join...WHERE Society.ID = 5" or something like that?
6. How many rows from the joined tables are you expecting? More than one? You might consider the impact on potential row-inflation.
7. Are you certain there will always be a joinable row in each of the joined tables? If not, you should use LEFT JOIN where appropriate.

Other than that, I don't know what else to tell you. More detail about your specific scenario would help.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.