List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:April 1 1999 6:27pm
Subject:Re: multiple table joins, speed question
View as plain text  
Dima Chernyshenko wrote:
> 
> Hello,
> 
> I have got a database with the following structure:
> table attribs: num int, ... (other fields)
> table facts: num int, fact int
> table objects: num int, object int
> 
> The table attribs contains about 3400 entries, 'num' is the primary key
> (i.e. all num's are unique). The tables facts and objects contain about
> 16000 entries each, around 5 entries for each value of 'num'. What i need
> is to do a query like:
> 
> select attribs.num from attribs,facts,objects where
>   attribs.num = facts.num and attribs.num = objects.num and
>   fact in (1,2,4,6) and
>   object in (2,67)
> ;
> 
> And though the tables are quite small, this query takes too much time
> (over 2 seconds). In fact I've got 2 more databases, when I try to join
> all 5, it takes forever to finish, while MS Access can execute all those
> queries in 1-2 seconds. Maybe someone can give advice how to optimize the
> queries/reorganize the structure to get the optimal performance?
> 
> Thanks,
> Dima Chernyshenko

Hi Dima

Do you have KEY's on all num fields?
Do you have a KEY on facts field?
Do you have a KEY on object field?

If you have, use the EXPLAIN syntax to see what's happening and read the manual chapter
'10: Getting maximum performance from MySQL'

Tschau
Christian

Thread
multiple table joins, speed questionDima Chernyshenko1 Apr
  • Re: multiple table joins, speed questionChristian Mack1 Apr