Web lists-archives.com

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
 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