On 2001 Apr 13, Maciek Dobrzanski <[EMAIL PROTECTED]> wrote:
> | fd_10 | varchar(20) | | MUL | | |
> | fd_11 | varchar(20) | | | | |
>
> Now when I do this update:
> UPDATE test SET fd_11='value' WHERE fd_10='some_value'
> it usually executes in 0.00 sec
>
> But when the query looks like this:
> UPDATE test SET fd_11='value' WHERE fd_10=some_value
> It takes about 2 seconds to execute.
This is because with the first query it can use the index. With
the second query, it has to check the whole table. Why? Because
obviously you're using numbers. And let's make some_value == 10.
In the second query it has to check for rows where fd_10 is:
'10'
'010'
'0010'
...
If you're storing numbers, use a numerical column. If you're
testing a varchar field, use a string.
Tim
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Tim Smith <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Development Team
/_/ /_/\_, /___/\___\_\___/ Boone, NC USA
<___/ www.mysql.com
---------------------------------------------------------------------
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
