>Description:
SELECT fullTextMatches.realname, fullTextMatches.address,
fullTextMatches.address2, fullTextMatches.city,
fullTextMatches.state,
fullTextMatches.zip, fullTextMatches.country,
fullTextMatches.email,
fullTextMatches.homephone, fullTextMatches.workphone,
fullTextMatches.userid
FROM tums.fullTextMatches, orders.header USE INDEX (userId)
WHERE header.storeId="/stores/eyarn" AND
header.userId=fullTextMatches.userId
ORDER BY realname
generates different results and is much faster than the same query
without "USE INDEX(userId):
Here is the explain with the USE INDEX:
+-----------------+------+--------------------------------------+------------+---------+------------------------------+------+----------------+
| table | type | possible_keys | key
| key_len | ref | rows | Extra |
+-----------------+------+--------------------------------------+------------+---------+------------------------------+------+----------------+
| fullTextMatches | ALL | NULL | NULL
| NULL | NULL | 15 | Using filesort |
| header | ref | storeId,userid,storeId_2,storeStatus | userid
| 4 | fullTextMatches.userid | 3 | where used |
+-----------------+------+--------------------------------------+------------+---------+------------------------------+------+----------------+
and without:
+-----------------+-------+--------------------------------------+-------------+---------+------------------------------+--------+----------------+
| table | type | possible_keys | key
| key_len | ref | rows | Extra |
+-----------------+-------+--------------------------------------+-------------+---------+------------------------------+--------+----------------+
| fullTextMatches | ALL | NULL | NULL
| NULL | NULL | 15 | Using filesort |
| header | range | storeId,userid,storeId_2,storeStatus |
storeStatus | 64 | NULL | 116907 | where
used |
+-----------------+-------+--------------------------------------+-------------+---------+------------------------------+--------+----------------+
Without the USE INDEX zero records match. With the USE INDEX I get 13
matches.
>How-To-Repeat:
mysql> show fields from orders.header;
+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+----------------+
| Field | Type
| Null | Key | Default | Extra |
+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+----------------+
| storeId | varchar(64)
| | MUL | | |
| shiptoOrganization | varchar(64)
| YES | | NULL | |
| shippingAddress | text
| YES | | NULL | |
| shippingCity | varchar(128)
| YES | | NULL | |
| shippingState | varchar(20)
| YES | | NULL | |
| shippingZipCode | varchar(10)
| YES | | NULL | |
| shippingCountry | varchar(30)
| YES | | NULL | |
| billtoOrganization | varchar(64)
| YES | | NULL | |
| billingAddress | text
| YES | | NULL | |
| billingCity | varchar(128)
| YES | | NULL | |
| billingState | varchar(20)
| YES | | NULL | |
| billingZipCode | varchar(10)
| YES | | NULL | |
| billingCountry | varchar(30)
| YES | | NULL | |
| businessName | varchar(128)
| YES | | NULL | |
| customerName | varchar(128)
| YES | | NULL | |
| shipType | varchar(128)
| YES | | NULL | |
| paymentType | varchar(128)
| YES | | NULL | |
| homePhone | varchar(20)
| YES | | NULL | |
| workPhone | varchar(20)
| YES | | NULL | |
| referringPage | text
| YES | | NULL | |
| customerEmail | varchar(128)
| YES | MUL | NULL | |
| transactionTime | datetime
| | MUL | 0000-00-00 00:00:00 | |
| storeEmail | varchar(128)
| YES | | NULL | |
| creditCardRefNum | varchar(128)
| YES | | NULL | |
| billpointMerchantName | varchar(128)
| YES | | NULL | |
| billpointMerchantId | varchar(128)
| YES | | NULL | |
| mailingList | varchar(10)
| YES | | NULL | |
| comments | text
| YES | | NULL | |
| cookie | varchar(128)
| YES | MUL | NULL | |
| transNum | int(11)
| | PRI | NULL | auto_increment |
| statusReason | text
| YES | | NULL | |
| shipping | decimal(6,2)
| YES | | 0.00 | |
| insurance | decimal(6,2)
| YES | | 0.00 | |
| taxes | decimal(6,2)
| YES | | 0.00 | |
| transactionType |
enum('tias','auction','store','otherFixedPrice','test','auctionChase')
| YES | | NULL | |
| ccvsReturnCount | int(2)
| YES | | 0 | |
| ccvsStatus | int(2)
| YES | | NULL | |
| ccvsStatusText | varchar(200)
| YES | | NULL | |
| orderStatus |
enum('inProcess','ordered','confirmed','paymentReceived','paymentCleared','shipped','productReceived','onHold','void','return','paymentOverdue','backOrdered','paymentAuthorized','paymentDeclined')
| YES | | NULL | |
| service | varchar(32)
| YES | | NULL | |
| shipper | varchar(32)
| YES | | NULL | |
| shipperTrackingNumber | varchar(64)
| YES | | NULL | |
| shipToName | varchar(128)
| YES | | NULL | |
| howIfoundTIAS | varchar(30)
| YES | | NULL | |
| serviceId | varchar(64)
| YES | MUL | NULL | |
| recurringPayment | int(1)
| YES | | NULL | |
| relatedTransNum | varchar(32)
| YES | | NULL | |
| authCode | varchar(32)
| YES | | NULL | |
| userId | int(10) unsigned
| | MUL | 0 | |
| shippingSuite | text
| YES | | NULL | |
| billingSuite | text
| YES | | NULL | |
| shipToPhone | varchar(20)
| YES | | NULL | |
| creditCardType | varchar(32)
| YES | | NULL | |
| otherComment | varchar(40)
| YES | | NULL | |
| saleType |
enum('webSite','phoneOrder','bulkOrder','overCounter')
| YES | | webSite | |
| resaleNo | varchar(16)
| YES | | NULL | |
| newsletter | tinyint(1)
| YES | | NULL | |
| dealerLogin | varchar(64)
| YES | | NULL | |
| paKey | int(8) unsigned
| YES | | NULL | |
| audited | tinyint(1)
| YES | | 0 | |
| requestedShipDate | date
| YES | | NULL | |
| amountReceived | decimal(8,2)
| YES | | NULL | |
| checkNumber | int(10) unsigned
| YES | | NULL | |
+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+----------------+
63 rows in set (0.00 sec)
mysql> show keys from orders.header;
+--------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part | Packed | Comment |
+--------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+---------+
| header | 0 | PRIMARY | 1 | transNum
| A | 590350 | NULL | NULL | |
| header | 1 | storeId | 1 | storeId
| A | 1565 | NULL | NULL | |
| header | 1 | storeId | 2 | transactionTime
| A | 590350 | NULL | NULL | |
| header | 1 | userid | 1 | userId
| A | 196783 | NULL | NULL | |
| header | 1 | cookie | 1 | cookie
| A | 295175 | NULL | NULL | |
| header | 1 | transactionTime | 1 | transactionTime
| A | 590350 | NULL | NULL | |
| header | 1 | customerEmail | 1 | customerEmail
| A | 196783 | NULL | NULL | |
| header | 1 | storeId_2 | 1 | storeId
| A | 1565 | NULL | NULL | |
| header | 1 | storeId_2 | 2 | relatedTransNum
| A | 118070 | NULL | NULL | |
| header | 1 | serviceId | 1 | serviceId
| A | 73793 | NULL | NULL | |
| header | 1 | storeStatus | 1 | storeId
| A | 1565 | NULL | NULL | |
| header | 1 | storeStatus | 2 | orderStatus
| A | 5903 | NULL | NULL | |
+--------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+---------+
mysql> show fields from tums.fullTextMatches;
+--------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+-------+
| userid | int(10) unsigned | | | 0 | |
| name | varchar(64) | | | | |
| passwd | varchar(64) | YES | | NULL | |
| email | varchar(64) | | | | |
| active | enum('Y','N') | YES | | Y | |
| homedir | varchar(64) | YES | | NULL | |
| pwtype | int(10) unsigned | | | 0 | |
| realname | varchar(64) | YES | | NULL | |
| address | varchar(128) | YES | | NULL | |
| city | varchar(64) | YES | | NULL | |
| state | varchar(32) | YES | | NULL | |
| zip | varchar(16) | YES | | NULL | |
| country | varchar(16) | YES | | NULL | |
| homePhone | varchar(20) | YES | | NULL | |
| workPhone | varchar(20) | YES | | NULL | |
| pastOrders | tinyint(1) | YES | | 0 | |
| uid | smallint(2) unsigned | YES | | NULL | |
| gid | smallint(2) unsigned | YES | | NULL | |
| shell | varchar(64) | YES | | NULL | |
| address2 | text | YES | | NULL | |
| organization | varchar(128) | YES | | NULL | |
+--------------+----------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
Why is this? I don't like using USE INDEX, because for some data values
it may be non optimal.
Note that REPAIR TABLE on orders.header doesn't fix the problem.
>Fix:
>Submitter-Id: randy6839
>Originator: Mike Wexler
>Organization:
TIAS.COM
>MySQL support: extended login
>Synopsis: non optimal selects with wrong results
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.44 (Source distribution)
>Environment:
System: Linux adams.tias.com 2.4.2-2jsh #1 SMP Fri May 11 16:51:18 PDT 2001 i686
unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/local/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/i686-pc-linux-gnu/3.0/specs
Configured with: ../gcc-3.0/configure
Thread model: single
gcc version 3.0
Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686' CXX='c++'
CXXFLAGS='-O2 -march=i386 -mcpu=i686' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Dec 17 14:09 /lib/libc.so.6 -> libc-2.2.4.so
-rwxr-xr-x 1 root root 1283964 Dec 8 06:14 /lib/libc-2.2.4.so
-rw-r--r-- 1 root root 27314296 Dec 8 06:02 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Dec 8 06:02 /usr/lib/libc.so
Configure command: ./configure i386-redhat-linux --prefix=/usr --exec-prefix=/usr
--bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share
--includedir=/usr/include --libdir=/usr/lib --libexecdir=/usr/libexec
--localstatedir=/var --sharedstatedir=/usr/com --mandir=/usr/share/man
--infodir=/usr/share/info --without-debug --without-readline --enable-shared
--with-extra-charsets=complex --with-bench --localstatedir=/var/lib/mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --with-mysqld-user=mysql
--with-extra-charsets=all --with-berkeley-db
---------------------------------------------------------------------
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
