# using indices with SMALL tables

*Date*: Fri, 22 Apr 2016 12:42:56 +0200 (CEST)*From*: Lucio Chiappetti <lucio@xxxxxxxxxxxxxxxx>*Subject*: 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

- Prev by Date:
**Re: utf8 options under Mysql** - Next by Date:
**ANN: DAC for MySQL 3.0.5 meets RAD Studio 10.1 Berlin!** - Previous by thread:
**utf8 options under Mysql** - Next by thread:
**ANN: DAC for MySQL 3.0.5 meets RAD Studio 10.1 Berlin!** - Index(es):