If you have proper indexes on alias, the optimizer should not make a table scan. You can try to add an index on pw too. Or you could combine the two queries with an UNION.
But beside this point you have a possible problem with the basic design. It is possible that someone adds an alias that is the same as an existing login chosen by the system. This is a potential (but not very probable as they would have to have the same passwords too) security problem that could give access to wrong people. You have to check that the alias does not exist in alias (done with UNIQUE) nor in login while adding a new alias. Toni On Wed, 11 Sep 2002, Kristian Koehntopp wrote: > > 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 > --------------------------------------------------------------------- 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
