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