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
