Hello ppl, [long e- follows] I'm running a large set of queries on mysql and am trying to fine-tune a specific type of them. I have the following tables: table alpha ----------- id auto_increment a1 mediumint (other rows not of interest) table beta ---------- id auto_increment b1 char(1) (other rows not of interest) table gamma ---------- id auto_increment g1 char(8) (other rows not of interest) table delta ----------- id auto_increment d1 char(10) (other rows not of interest) table epsilon ----------- id auto_increment e1 smallint (other rows not of interest) table theta ----------- id auto_increment t1 fload(6,2) (other rows not of interest) table lamda ----------- id auto_increment l1 enum('y','n') (other rows not of interest) and I want to perform lots of queries that are 5-way joins between the 5 out of these 7 tables where I want equality on the auto_increment field apart from extra conditions. E.g. SELECT COUNT(*) FROM beta,alpha,lamda,gamma,epsilon WHERE beta.id=alpha.id AND alpha.id=lamda.id AND lamda.id=gamma.id AND gamma.id=epsilon.id AND b1='f' AND a1 BETWEEN 45 AND 55 AND e1 BETWEEN 7000 AND 7999 GROUP BY g1,l1; At the moment I have only one index on the id (auto_increment) of each table. Then I thought of creating an index on both (id,a1),(id,e1) etc. so that the extra field would be in the key_buffer and avoid the extra disk access. But it proved to decrease speed :-( Why? EXPLAIN gives me: +----------+--------+---------------+------+---------+-------------+---------+-----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | | +----------+--------+---------------+------+---------+-------------+---------+-----------------------------+ | beta | ALL | id | NULL | NULL | NULL | 1000000 | where |used; Using temporary | | alpha | eq_ref | id | id | 3 | beta.id | 1 | where used | | | lamda | eq_ref | id | id | 3 | alpha.id | 1 | | | | gamma | eq_ref | id | id | 3 | lamda.id | 1 | | | | epsilon | eq_ref | id | id | 3 | gamma.id | 1 | where used | | +----------+--------+---------------+------+---------+-------------+---------+-----------------------------+ The first table will be scanned fully (no index used) i.e. all 1M rows. For each of the next tables, the index on (id) which is a primary key will be used so only 1 rows will be retrieved. What is really killing me is the GROUP BY which causes a tmp table to be created. Any way I can help this? Any suggestions on how I could improve the performance of these 5-way joins in general? thanks in advance, thalis --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php