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
- Joins are damn slow. . . Steve Quezadas
- Re: Joins are damn slow. . . Stefan Hinz, iConnect \(Berlin\)
- Re: Joins are damn slow. . . Michael T. Babcock
- RE: Joins are damn slow. . . Jennifer Goodie
- Re: Table joins are slow things to d... Steve Quezadas
- Re: Table joins are slow things ... Stefan Hinz, iConnect \(Berlin\)
- Re: Table joins are slow thi... Benjamin Pflugmann
- Re: Table joins are slow things ... Michael T. Babcock