Web lists-archives.com

# 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

```