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
