using indices with SMALL tables

I am regularly using indices on medium-big tables (1000 to > 50000 
entries), and even on temporary tables (which I use a lot) in joins 
(EXPLAIN SELECT is your friend).

But I'd never thought indices were needed for small tables (100-200 
entries). I recently found they are useful too, and I'd like to share.

I have one largish table (~50000 entries) for which I have to compute some 
probabilities and likelihoods which depend on two columns, distance d and 
magnitude mag. While the dependency on d is given by a simple formula, the 
dependency on mag requires a lookup and a linear interpolation in another 
SMALL table. The small table has 190 elements.

I created a stored function to do the lookup and interpolation.

create function lookup (x float)
returns float
  begin
  declare yr float default -1.0;
  declare y1 float default 0;
  declare y2 float default -1.0;
  declare x1 float default 0;
  declare x2 float default 0;
  select mag,y from xyview where mag > x limit 1 into x2,y2;
  select mag,y from xyview where mag < x order by mag desc limit 1 into x1,y1;
  set yr=y1 ;
  if x1 <> x2 then
   set yr = yr + (x-x1)*(y2-y1)/(x2-x1) ;
  end if;
  return yr;

Then I attempted to update the big table with statements like

  update t set lr1  = lookup(mag)*exp(-0.5*d*d)/2/pi() ;

This was taking a long time, despite the fact that an
  explain select t.*,lookup(mag)
shows nothing peculiar.

I found that a single lookup call takes 0.05 sec, and scaling that for
50000 elements would take 38 min. And at the end, I'd have to repeat the 
process for 48 times (each time changing the table xyview, since prepared 
statements are not allowed in stored functions).

Well, it is enough to add an index on mag on the small table xyview, to 
cut the processing time BY A FACTOR 736.

Now what had taken 38 minutes takes 3.02 sec !!! Great !

-- 
------------------------------------------------------------------------ 
Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) For 
more info : http://www.iasf-milano.inaf.it/~lucio/personal.html 
------------------------------------------------------------------------ 
Do not like Firefox >=29 ?  Get Pale Moon !  http://www.palemoon.org

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql