>Description:
Here is the speedy query...
SELECT empl
, tender_media
, SUM(tender_media_total) AS tender_media_total
, SUM(tender_media_count) AS tender_media_count
FROM closed_chk_totals, closed_chk_tender_media_detail
WHERE closed_chk_totals.number = closed_chk_tender_media_detail.check
AND closed_chk_totals.rvc = closed_chk_tender_media_detail.revenue_center
AND closed_chk_totals.chk_opn_time = closed_chk_tender_media_detail.open_time
GROUP BY empl, tender_media;
Here is the EXPLAIN:
table type possible_keys key key_len ref rows Extra
closed_chk_totals ALL k_revenue_center,k_number NULL NULL NULL 9055
closed_chk_tender_media_detail ref k_check,k_open_time,k_revenue_center k_open_time
3 closed_chk_totals.chk_opn_time 13 where used
real 0m1.068s
And then I add
--> AND closed_chk_totals.game_date = closed_chk_tender_media_detail.game_date
to the WHERE clause and the query now looks like:
SELECT empl
, tender_media
, SUM(tender_media_total) AS tender_media_total
, SUM(tender_media_count) AS tender_media_count
FROM closed_chk_totals, closed_chk_tender_media_detail
WHERE closed_chk_totals.number = closed_chk_tender_media_detail.check
AND closed_chk_totals.rvc = closed_chk_tender_media_detail.revenue_center
AND closed_chk_totals.chk_opn_time = closed_chk_tender_media_detail.open_time
AND closed_chk_totals.game_date = closed_chk_tender_media_detail.game_date
GROUP BY empl, tender_media;
Here is the EXPLAIN:
table type possible_keys key key_len ref rows Extra
closed_chk_totals ALL k_revenue_center,k_number,k_game_date NULL NULL NULL 9055
closed_chk_tender_media_detail ref
k_check,k_open_time,k_game_date,k_revenue_center k_game_date 4 closed_chk_totals.game_date 10 where
used
But the execution time is now 11m4.575s!!! Yikes.
I tried using the STRAIGHT_JOIN option without any good results.
>How-To-Repeat:
See previous
>Fix:
I don't know. Is it in the JOIN optimizer routine?
>Submitter-Id: <submitter ID>
>Originator: Richard Ayotte
>Organization: Casino Niagara
>MySQL support: none
>Synopsis: slow join when many conditions
>Severity: non-critical
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.22.25 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 7.11 Distrib 3.22.25, for pc-linux-gnu on i586
TCX Datakonsult AB, by Monty
Server version 3.22.25-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 2 days 20 hours 39 min 6 sec
Threads: 10 Questions: 3386 Slow queries: 40 Opens: 102 Flush tables: 1 Open tables:
35
>Environment:
System: Linux puffin 2.2.12 #10 Mon Sep 13 23:14:19 EDT 1999 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.2/specs
gcc version 2.95.2 19990906 (prerelease)
Compilation info: CC='gcc' CFLAGS='-O2 -fomit-frame-pointer' CXX='c++' CXXFLAGS='-O2
-fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS=''
Configure command: ./configure --enable-shared --enable-assembler
--with-mysqld-user=mysql --with-unix-socket-path=/var/run/mysqld/mysqld.sock
--prefix=/usr --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc/mysql
--datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/share/info
--includedir=/usr/include --mandir=/usr/share/man
Perl: This is perl, version 5.005_03 built for i386-linux
