From: Michael Widenius Date: March 29 1999 11:58am Subject: RE: Question about query List-Archive: http://lists.mysql.com/mysql/1135 Message-Id: <14079.27067.454424.667813@monty.pp.sci.fi> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi! >>>>> "Jin" == Jin Hui 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