Hello.
On Wed 2003-01-22 at 09:13:20 +0100, [EMAIL PROTECTED] wrote:
> 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 |
Good check list. :-)
Two things I noticed: Using a 30-byte wide index (CaseNumber) is not
fastest. Probably it would help to create an additional INT
AUTO_INCREMENT with key and join via that. That reduces the amount of
data to be read from disk for the index by the factor 4.25
((30+4)/(4+4)), and also cuts down the time for comparisions and
improves relative effictivness of the key cache. Whether this the main
reason for the slowness, I don't know, but it cannot hurt to try.
Another thing that I would try is to to create a combined index on
(Filed,CaseNumber) and (CaseNumber,Filed). Drop the one that does not
get used afterwards. This should enable MySQL to resolve the join only
using the index: currently it has to load the data records in order to
check whether Filed is in range (i.e. it has to load 34,000 records
although only 10,500 are returned afterwards that is about 24,000
seeks and reads without need). An index on (Defendend,CaseNumber) will
also help. Of course, if you introduce an id (the INT) column as
suggested above, use that instead of CaseNumber in the combined
indexes.
And at last, be sure to run OPTIMIZE and ANALYZE on the tables (of,
course, you can also start with that :-).
Please check speed after each change and report back. I am very
interested to hear whether any of the suggestion had real effect in
your case (the EXPLAIN for each would also be nice).
Btw, what size is your key_cache? And how many reads/misses does it
have?
HTH,
Benjamin.
> Okay, I leave this one to the gurus :-/
Not sure that I'd call me like this, but I chose to add my 2 cents
anyhow. ;)
[...]
> > 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.
[...]
> > ([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 | |
> >
> +-------+------------+-------------+--------------+-------------+-------
> ----+-------------+----------+--------+---------+
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
