>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