- 1 Messages
- Collapse All
- Expand All
- Subscribe

Apr 22

Apr 22, 2016; 12:42

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

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

This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!

©LassoSoft Inc 2013 | Web Development and Lasso Programming by Treefrog

Privacy | Legal terms and Shipping | Contact LassoSoft