CODEWITHSUNDEEP
mysqldatabaseperformance
Roc Yu
Roc Yu

Reputation: 103

mysql multiple joins with conditions on multiple table query performance

suppose tables:

main (1M rows): id, a_id, b_id, c_id, d_id, amounts, year, main_col

a(500 rows): id, a_col1, a_col2

b(2000 rows): id, b_col1, b_col2, b_col3

c(500 rows): id, c_col1, c_col2

d(1000 rows): id, d_col1, d_col2

And we have query like:

select sum(amounts), main_col 
   join a on a.id = main.a_id 
   join b on b.id = main.b_id 
   join c on c.id = main.c_id 
   join d on d.id = main.d_id 
   where a.a_col1 in (...) 
     and a.a_col2 in (..) 
     and b.b_col1 in (...) 
     and b.b_col2 in (...) 
     and b.b_col3 in (..) 
     and c.c_col1 in (..) 
     and c.c_col2 in (..) 
     and d.d_col1 in (..) 
     and d.d_col2 in (..)
     and year = 2011

   group by main.main_col

any idea who to create index on the main table to improve the query performance ?

thanks

Update: indexes are added to a,b,c,d tables for the columns show up in where i've tried multiple column indexes on main table (a_id, b_id, c_id, d_id, main_col) which have the best performance than others like add individual indexes on the a_id, b_id... , but it still not fast enough for requirement, on query will take 7 seconds to run for the maximum situation

Upvotes: 0

Views: 165

Answers (4)

McGarnagle
McGarnagle

Reputation: 102753

It really depends on specifics like how selective each join is, how big each table is, etc. In general it might be wise to add one or more indices on the foreign keys of your main table, but who could say from that limited info?

It might also help to get an idea of how your query is executing-- try looking at MySQL's "explain" or "explain extended".

Upvotes: 1

haltabush
haltabush

Reputation: 4528

You should also have an index on your group_by I think

Upvotes: 0

barsju
barsju

Reputation: 4446

Since the primary keys are already indexed by default, the join cannot be optimized by adding more indexes. So that leaves your where statements. So it could help adding indexes to the x.x_colN columns.

Upvotes: 0

Darren
Darren

Reputation: 70728

CREATE INDEX id_main ON main(id)

You could create multiple indexes on additional columns and see how that works for you.

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

Upvotes: 1

Related Questions