>Description:
We are trying to run some fairly complex joins over large tables. The queries
can take up to a day or more to finish. We feel that there must be something
wrong with either the table structure, or the join query itself. There are two
tables involved, and the join requires each of them twice (see below).
The 'explain select' below shows that indexes are being used, so I don't think
its a full table scan.
Is there any way we can optimise the query itself? Or is there something wrong
with the table structure?
Thanks in advance,
Paddy
>How-To-Repeat:
Table structures:
mysql> show create W;
| W | CREATE TABLE `W` (
`S` int(11) NOT NULL default '0',
`P` int(11) NOT NULL default '0',
`C` int(11) NOT NULL default '0',
`D` smallint(6) NOT NULL default '0',
PRIMARY KEY (`S`,`P`,`C`,`D`),
KEY `P`(`P`),
KEY `C`(`C`),
KEY `D`(`D`)
) TYPE=MyISAM |
mysql> show create HID;
| HID | CREATE TABLE `HID` (
`H` int(11) NOT NULL default '0',
`U` int(11) NOT NULL default '0',
PRIMARY KEY (`H`,`U`),
UNIQUE KEY `U`(`U`)
) TYPE=MyISAM |
Table sizes:
mysql> select count(*) from W;
+----------+
| count(*) |
+----------+
| 19138781 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from H;
+----------+
| count(*) |
+----------+
| 37460033 |
+----------+
1 row in set (0.00 sec)
Query:
select count(distinct (t1.C)) from W t1
join W t2
join HID t3
join HID t4
where t3.H = t4.H
and
t1.C = t3.U
and
t2.C = t4.U
and
t1.D = 2
and
t2.D < 2
and
t1.S = t2.S
and
t1.S = 10;
Explain query:
mysql> explain select count(distinct (t1.C)) from W t1
join W t2
join HID t3
join HID t4
where t3.H = t4.H
and
t1.C = t3.U
and
t2.C = t4.U
and
t1.D = 2
and
t2.D < 2
and
t1.S = t2.S
and
t1.S = 10;
+-------+--------+----------------+---------+---------+---------------+--------+---------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+-------+--------+----------------+---------+---------+---------------+--------+---------------+
| t1 | ref | PRIMARY,C,D | D | 2 | const | 901873 | where
|used |
| t2 | range | PRIMARY,C,D | D | 2 | NULL | 37863 | where
|used |
| t3 | eq_ref | PRIMARY,U | U | 4 | t1.C | 1 |
| |
| t4 | eq_ref | PRIMARY,U | PRIMARY | 8 | t3.H,t2.C | 1 | Using
|index |
+-------+--------+----------------+---------+---------+---------------+--------+---------------+
4 rows in set (0.26 sec)
>Fix:
>Submitter-Id: <submitter ID>
>Originator: Paddy Doyle
>Organization:
>MySQL support: none
>Synopsis: Slow joins over large tables
>Severity: serious
>Priority: medium
>Category: mysql
>Class: support
>Release: mysql-3.23.32 (Source distribution)
>Environment:
System: Linux localhost 2.2.14 #2 SMP Wed Mar 8 13:31:33 GMT 2000 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/local/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/i686-pc-linux-gnu/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Nov 23 1999 /lib/libc.so.6 -> libc-2.1.2.so
-rwxr-xr-x 1 root root 4118299 Sep 20 1999 /lib/libc-2.1.2.so
-rw-r--r-- 1 root root 20020054 Sep 20 1999 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 20 1999 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql --with-charset=latin1
--with-extra-charsets=none
Perl: This is perl, version 5.005_03 built for i386-linux
---------------------------------------------------------------------
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
