3.23.41 on Solaris
I have an indexed table of cusips (9 character identifiers)
which I am comparing against a lookup tables of over
1,000,000 securities, also keyed by cusip, both fields are
char(9) fields.
My query is taking over 3 seconds, which may be the best I
can do, but I'm hoping I am missing something. Also, unless
I use STRAIGHT_JOIN, it takes MUCH longer, since the optimizer
is starting with the 1,000,000 record table (which is a MERGE
table, BTW).
The explains look like:
mysql> explain select lid from TEST_1006199805 AS A,
pool_lookup.lkup AS L WHERE A.cusip = L.cusip;
+-------+--------+---------------+---------+---------+---------+---------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
||
+-------+--------+---------------+---------+---------+---------+---------+-------------+
| L | ALL | cusip | NULL | NULL | NULL | 1296422 |
||
| A | eq_ref | PRIMARY | PRIMARY | 9 | L.cusip | 1 | Using index
||
+-------+--------+---------------+---------+---------+---------+---------+-------------+
mysql> explain select lid from TEST_1006199805 AS A
STRAIGHT_JOIN pool_lookup.lkup AS L WHERE A.cusip = L.cusip;
+-------+-------+---------------+---------+---------+---------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+---------+---------+---------+------+-------------+
| A | index | PRIMARY | PRIMARY | 9 | NULL | 1995 | Using index |
| L | ref | cusip | cusip | 9 | A.cusip | 768 | |
+-------+-------+---------------+---------+---------+---------+------+-------------+
I'm not sure what the 768 rows in L is, since all but 4 of the
1995 records in A have matches L... though sometimes there will
be more than one match, but not more than 4 or so...
Any suggestions on speeding this up?
--
___ __ __ __ _ _ ____ _ _ ____ ____
/ __)( )( ) /__\( \/ )( ___) ( \( )( ___)(_ _)
\__ \ )(__)( /(__)\\ / )__) ) ( )__) )(
(___/(______)(__)(__)\/ (____)()(_)\_)(____) (__)
An attacker must vanquish, a defender need only survive.
---------------------------------------------------------------------
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
