Joins are slow

[Steve Quezadas]

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