List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:September 11 2002 2:44pm
Subject:Re: Why are OR-Clauses handled so badly?
View as plain text  
Put an index on password.
That should speed it up for you.

Kristian Koehntopp wrote:

>Am Mittwoch, 11. September 2002 15:19 schrieb Toni Strandell:
>
>>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.
>>
>
>Sorry, UNION is not available, as it is implemented only in MySQL 
>4.x and we cannot upgrade the system to this MySQL version (it 
>is a production system).
>
>But based on your hint I have searched the documentation again, 
>and found
>
>http://www.mysql.com/doc/en/Searching_on_two_keys.html
>
>which is exactly my problem (but unfortunately offers no 
>solution).
>
>You also reminded me of
>
>>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. 
>>
>
>Thus I have chosen to normalize the design to
>
>login (either "1000", the login name in the previous example, or 
>"a1000", the alias name in the previous example) and make this a 
>primary key.
>
>password (just like before)
>
>subscriber (the login name in the previous example) and make this 
>a key, and mark it as a foreign key into an external customer 
>table. This customer table does not actually exist in the MySQL 
>web frontend, but only in the subscriber database in the 
>backend, but I need this link for metering purposes.
>
>I now have at most 190.000 records instead of 95.000 previously, 
>but the performance has improved greatly.
>
>Thank you for your quick assistance and for providing the proper 
>search keyword for the documentation (searching for "union" 
>leads directly to the URL "Two Keys" URL cited above).
>
>Kristian
>


Thread
Why are OR-Clauses handled so badly?Kristian Koehntopp11 Sep
  • Re: Why are OR-Clauses handled so badly?Toni Strandell11 Sep
    • Re: Why are OR-Clauses handled so badly?Kristian Koehntopp11 Sep
    • Re: Why are OR-Clauses handled so badly?Gerald Clark11 Sep