16 Sep
2012
16 Sep
'12
2:07 p.m.
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