Hi!
>>>>> "Jin" == Jin Hui <JinHui@stripped> writes:
Jin> 1. The first query was wrong, it should be
Jin> select id from atable where truncate(c1/1000000, 0) = 321
Jin> 2. I cannot do a testing. Because I am running the database on a web
Jin> hoster's shared server. The testing time results are very different for the
Jin> same query. I cannot compare them.
You can always make 10 runs and use the lowest value! Alternatively
you can download a MySQL version of your own and test on your own machine.
Jin> 3. I also want to know if there's any other faster way to do integer
Jin> division like the / in c. I am using truncate.
Forget the speed of '/'; In database applications, one can usually
forget the speed of most arithmetic operations compared to key usage.
(You can easily test this with the benchmark() function !)
Jin> 4. Because the server space is limited. I only can make necessary index. I
Jin> want to know if the index on c1 or c1/1000000 is helpful to speed.
A index on C1 is good enough.
Jin> I need some suggestion from someone has such experience. Thanks for any
Jin> help!
Why not solve this the easy 'obvious way':
select id from atable where c1 between 321000000 and 321999999;
The above will even use indexes (your original query didn't use indexes)
Regards,
Monty
