I am using
mysql> select version() as version; +-------------+ | version | +-------------+ | 3.23.48-log | +-------------+ 1 row in set (0.00 sec) on Suse Linux 8.0 and Solaris 8. The problem exists on both platforms. I have mysql> select count(*) from auth_kn; +----------+ | count(*) | +----------+ | 95000 | +----------+ 1 row in set (0.00 sec) with kk@kris:~> mysqldump --no-data test auth_kn -- MySQL dump 8.21 -- -- Host: localhost Database: test --------------------------------------------------------- -- Server version 3.23.48-log -- -- Table structure for table 'auth_kn' -- CREATE TABLE auth_kn ( login varchar(16) NOT NULL default '', alias varchar(16) NOT NULL default '', pw varchar(16) NOT NULL default '', PRIMARY KEY (login), UNIQUE KEY alias (alias) ) TYPE=MyISAM; The table data is synthetically generated for performance testing and looks like this: mysql> select * from auth_kn limit 3; +-------+-------+----+ | login | alias | pw | +-------+-------+----+ | 0 | a0 | 0 | | 1 | a1 | 1 | | 2 | a2 | 2 | +-------+-------+----+ 3 rows in set (0.05 sec) I want to build a login that accepts either the user name (which is a 16 digit number that is hard to remember) or the user aliass (which is a unique name the user created for himself) and a password. If I fire a simple query such as mysql> select * from auth_kn where ( login = "1000" and pw = "1000" ); +-------+-------+------+ | login | alias | pw | +-------+-------+------+ | 1000 | a1000 | 1000 | +-------+-------+------+ 1 row in set (0.00 sec) mysql> explain select * from auth_kn where ( login = "1000" and pw = "1000" ); +---------+-------+---------------+---------+---------+-------+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+-------+---------------+---------+---------+-------+------+-------+ | auth_kn | const | PRIMARY | PRIMARY | 16 | const | 1 | | +---------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec) all is well. The result for alias is exactly the same: mysql> explain select * from auth_kn where ( alias = "1000" and pw = "1000" ); +-----------------------------------------------------+ | Comment | +-----------------------------------------------------+ | Impossible WHERE noticed after reading const tables | +-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> explain select * from auth_kn where ( alias = "a1000" and pw = "1000" ); +---------+-------+---------------+-------+---------+-------+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+-------+---------------+-------+---------+-------+------+-------+ | auth_kn | const | alias | alias | 16 | const | 1 | | +---------+-------+---------------+-------+---------+-------+------+-------+ 1 row in set (0.00 sec) My problems start as soon as I combine both statements with an or clause. It does not matter if I multiply the operands out or not: mysql> select * from auth_kn where ( login = "1000" or alias = "1000" ) and pw = "1000"; +-------+-------+------+ | login | alias | pw | +-------+-------+------+ | 1000 | a1000 | 1000 | +-------+-------+------+ 1 row in set (0.23 sec) mysql> explain select * from auth_kn where ( login = "1000" or alias = "1000" ) and pw = "1000"; +---------+------+---------------+------+---------+------+-------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+------+---------------+------+---------+------+-------+------------+ | auth_kn | ALL | PRIMARY,alias | NULL | NULL | NULL | 95000 | where used | +---------+------+---------------+------+---------+------+-------+------------+ 1 row in set (0.00 sec) And multiplying the operands out giving mysql> explain select * from auth_kn where ( login= "1000" and pw = "1000" ) or ( alias = "1000" and pw = "1000" ); +---------+------+---------------+------+---------+------+-------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+------+---------------+------+---------+------+-------+------------+ | auth_kn | ALL | PRIMARY,alias | NULL | NULL | NULL | 95000 | where used | +---------+------+---------------+------+---------+------+-------+------------+ 1 row in set (0.00 sec) yields the same result. What is the reason for this loss on performance on such a simple query? How would I handle this situation? The current workaround is to have two simple queries, which performs much better than the combined query, but incurs the double RTT and twice the context switches and page faults. Recommendations? Kristian --------------------------------------------------------------------- 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
