[sr-dev] db version of nat pinger

[Juha Heinanen]

Juha Heinanen writes:

> one more thing.  the all contacts query that usrloc makes looks like
> this:
> 
> 120915 21:48:04	 1397 Query	select received, contact, socket, cflags, path, ruid from location where expires > '2012-09-15 21:48:04' and cflags & 0 = 0 and id % 20 = 0
> 
> there is currently no index for expires and cflags.  should i add one or
> both of them?

i did some tests on location table with 10000 records.  i tried with and
without index on expires.  i also tried the same with integer expires
field that i called stamp.  in all cases i repeated the above select
five times and calculated average execution time.

here are results without index on expires or stamp:

# ./location.php 
expires 0.02223800 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0
stamp   0.01464875 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0
expires 0.01949225 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0
stamp   0.01462175 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0
expires 0.01813800 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0
stamp   0.01471850 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0
expires 0.02328850 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0
stamp   0.01501425 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0
expires 0.02514450 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0
stamp   0.01482900 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0
expires ave 0.02166025
stamp   ave 0.01476645

and here are result with index on expires and stamp:
# ./location.php 
expires 0.01195225 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0
stamp   0.00652925 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0
expires 0.01188325 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0
stamp   0.00680575 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0
expires 0.01177150 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0
stamp   0.00694525 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0
expires 0.01189250 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0
stamp   0.00696700 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0
expires 0.01188350 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0
stamp   0.00690475 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0
expires ave 0.0118766
stamp   ave 0.0068304

the conclusion is that without index, select takes about twice as much
time as with index.  another conclusion is that integer stamp is about two times
as efficient as datetime expires.

i'll thus add in location table an index on expires.  at some point it
would make sense to replace datetime type of expires field with unsigned
int.

-- juha