Steve, > ([Defendant] Query WITH a join - 8.79 seconds! > EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE > Cases.CaseNumber = Defendants.CaseNumber AND Filed <= "1999-01-01" AND > (Defendant LIKE "owen%" OR Defendant LIKE "pitts%" OR Defendant LIKE > "general%");
First thing that comes into mind is: You said you indexed the join fields (i.e. CaseNumber), but what about Defendants.Defendant? Okay, you did. Hmm. Next thing would be to check if the appropriate keys were used (CaseNumber, Defendant). Yes, they were used. Next thing would be to check if the optimizer chose a good join type: > | Defendants | range | CaseNumber,Defendant | Defendant | 30 | > | Cases | eq_ref | CasesNumber,Filed | CasesNumber | 30 | The join types are "range" and "eq_ref", that's fine. The number of examined rows in Defendants seems okay, too: > rows | Extra | > 82756 | where used | Okay, I leave this one to the gurus :-/ Regards, -- Stefan Hinz <[EMAIL PROTECTED]> Geschäftsführer / CEO iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 ----- Original Message ----- From: "Steve Quezadas" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, January 22, 2003 12:32 AM Subject: Table joins are slow things to deal with. . . > Maybe I'm dumb for saying this, but sql joins seems expensive to do in > terms of performance (yes, I indexed the joined fields). If I do a query > search of a 2,600,000 record defendant table WITHOUT a join (SELECT > DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE "owen%" OR > Defendant LIKE "pitts%" OR Defendant LIKE "general%";). Performance is > generally zippy at .53 seconds (which ends up pulling about 34,000 rows). > > HOWEVER, once I join this table with the much smaller [Cases] table > (about 140,000 rows), performance plummets to 8.79 seconds! Quite a > drop! The SQL statement is: 'SELECT Defendants.CaseNumber FROM > Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND > Filed <= "1999-01-01" AND (Defendant LIKE "owen%" OR Defendant LIKE > "pitts%" OR Defendant LIKE "general%");'. I get about 10,500 rows > returned here, but man, what an increase in time. > > So my main question is: Is this degradation in performance to be > expected with a join? What I am thinking about doing is perhaps doing > two smaller queries instead of one larger one. My first query can just > query the very large Defendants table without a join and create a > temporary table of CaseNumbers (takes about .53 seconds). Then I can > join that temporary table into the much smaller [Cases] table and then > run that part of the query (which takes about 1.23 seconds). > > So bottom line: > Results of big query with one join: 8.79 seconds > Results of creating two smaller queries: 1.76 seconds (.53 seconds + > 1.23 seconds). > > So I am thinking about optimizing the query in my PHP program to make > two smaller queries instead of one bigger query. Is this a standard > thing to do to increase speed? Or am I doing something stupid and > inefficient in the join that I am not aware of? > > - Steve > > PS Here is some information about my tables and indexes: > > ([Defendant] Query WITHOUT a join) - .53 seconds. > EXPLAIN SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE > "owen%" OR Defendant LIKE "pitts%" OR Defendant LIKE "general%"; > > +------------+-------+---------------+-----------+---------+------+----- --+-----------------------------+ > | table | type | possible_keys | key | key_len | ref | > rows | Extra | > +------------+-------+---------------+-----------+---------+------+----- --+-----------------------------+ > | Defendants | range | Defendant | Defendant | 30 | NULL | > 82756 | where used; Using temporary | > +------------+-------+---------------+-----------+---------+------+----- --+-----------------------------+ > > ([Defendant] Query WITH a join - 8.79 seconds! > EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE > Cases.CaseNumber = Defendants.CaseNumber AND Filed <= "1999-01-01" AND > (Defendant LIKE "owen%" OR Defendant LIKE "pitts%" OR Defendant LIKE > "general%"); > +------------+--------+----------------------+-------------+---------+-- ---------------------+-------+------------+ > | table | type | possible_keys | key | key_len | > ref | rows | Extra | > +------------+--------+----------------------+-------------+---------+-- ---------------------+-------+------------+ > | Defendants | range | CaseNumber,Defendant | Defendant | 30 | > NULL | 82756 | where used | > | Cases | eq_ref | CasesNumber,Filed | CasesNumber | 30 | > Defendants.CaseNumber | 1 | where used | > +------------+--------+----------------------+-------------+---------+-- ---------------------+-------+------------+ > > Table and index information > Cases Table - 140,000 records > +---------------+---------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +---------------+---------------+------+-----+---------+-------+ > | CaseNumber | char(30) | | PRI | | | > | County | char(30) | | | | | > | CaseName | char(120) | | | | | > | Court | char(30) | | | | | > | Filed | datetime | YES | MUL | NULL | | > | NumPlaintiffs | tinyint(4) | YES | | NULL | | > | DateAdded | timestamp(14) | YES | | NULL | | > +---------------+---------------+------+-----+---------+-------+ > > Defendants table - 2,600,000 records > +-------------+---------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +-------------+---------------+------+-----+---------+-------+ > | CaseNumber | char(30) | | MUL | | | > | Defendant | char(30) | | MUL | | | > | DateEntered | timestamp(14) | YES | | NULL | | > +-------------+---------------+------+-----+---------+-------+ > > Index of Defendants > +------------+------------+------------+--------------+-------------+--- --------+-------------+----------+--------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Comment | > +------------+------------+------------+--------------+-------------+--- --------+-------------+----------+--------+---------+ > | Defendants | 1 | CaseNumber | 1 | CaseNumber | > A | 37337 | NULL | NULL | | > | Defendants | 1 | Defendant | 1 | Defendant | > A | 6840 | NULL | NULL | | > +------------+------------+------------+--------------+-------------+--- --------+-------------+----------+--------+---------+ > > Index of Cases > +-------+------------+-------------+--------------+-------------+------- ----+-------------+----------+--------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Comment | > +-------+------------+-------------+--------------+-------------+------- ----+-------------+----------+--------+---------+ > | Cases | 0 | CasesNumber | 1 | CaseNumber | > A | 37311 | NULL | NULL | | > | Cases | 1 | Filed | 1 | Filed | > A | 23 | NULL | NULL | | > +-------+------------+-------------+--------------+-------------+------- ----+-------------+----------+--------+---------+ > > > > --------------------------------------------------------------------- > 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 > --------------------------------------------------------------------- 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
