List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 29 1999 11:58am
Subject:RE: Question about query
View as plain text  
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
Thread
Question about queryJin Hui29 Mar
Re: Question about queryEd Carp29 Mar
RE: Question about queryJin Hui29 Mar
  • RE: Question about queryMichael Widenius29 Mar