01.random-node (3205B)
1 // Select random node : 2 // Comparaison de plusieurs méthodes : 3 4 // Note : calculer un count(*) prend très longtemps, il vaut mieux faire un max(eid). 5 6 // rapide, mais ne renvoie rien si on tombe sur un trou dans les eid, probabilité 105/229894 ≈ 1/2000 7 select * from node where eid = (abs(random()) % (select max(eid) from node))+1; 8 // assez rapide, renvoie le noeud 1 si on tombe sur un trou dans les eid 9 select * from node where eid = (abs(random()) % (select max(eid) from node))+1 or eid = (select min(eid) from node where eid > 0) order by eid desc limit 1; 10 // peu rapide, trouve toujours un row, aléatoire 11 select * from node limit 1 offset (abs(random()) % (select count(*)+1 from node)); 12 13 // Comparaison des temps d'exécution des méthodes ci-dessus 14 // Note : les deux premiers sont sur 10000 exécutions, le dernier sur 100 15 16 > time yes "select * from node where eid = (abs(random()) % (select max(eid) from node));" | head -n 10000 | sqlite3 db > /dev/null 17 yes 0,01s user 0,00s system 0% cpu 3,272 total 18 head -n 10000 0,00s user 0,02s system 0% cpu 3,283 total 19 sqlite3 db > /dev/null 2,70s user 0,52s system 89% cpu 3,586 total 20 > time yes "select * from node where eid = (abs(random()) % (select max(eid) from node)) or eid = (select min(eid) from node where eid > 0) order by eid desc limit 1;" | head -n 10000 | sqlite3 db > /dev/null 21 yes 0,01s user 0,01s system 0% cpu 7,324 total 22 head -n 10000 0,01s user 0,02s system 0% cpu 7,340 total 23 sqlite3 db > /dev/null 5,94s user 0,62s system 85% cpu 7,645 total 24 > time yes "select * from node limit 1 offset (abs(random()) % (select count(*) from node));" | head -n 100 | sqlite3 db > /dev/null 25 yes 0,01s user 0,00s system 33% cpu 0,024 total 26 head -n 100 0,00s user 0,00s system 37% cpu 0,021 total 27 sqlite3 db > /dev/null 7,40s user 5,99s system 89% cpu 14,984 total 28 29 // Avec un index sur node(eid), nettement meilleur pour les deux premières méthodes 30 // Les deux premières méthodes ne bougent pas, la troisième est pas mal améliorée. 31 create index i_node_eid on node(eid); 32 33 > time yes "select * from node where eid = (abs(random()) % (select max(eid) from node));" | head -n 10000 | sqlite3 db > /dev/null 34 yes 0,01s user 0,01s system 0% cpu 3,421 total 35 head -n 10000 0,00s user 0,02s system 0% cpu 3,439 total 36 sqlite3 db > /dev/null 2,82s user 0,53s system 89% cpu 3,752 total 37 > time yes "select * from node where eid = (abs(random()) % (select max(eid) from node)) or eid = (select min(eid) from node where eid > 0) order by eid desc limit 1;" | head -n 10000 | sqlite3 db > /dev/null 38 yes 0,02s user 0,01s system 0% cpu 7,059 total 39 head -n 10000 0,02s user 0,02s system 0% cpu 7,076 total 40 sqlite3 db > /dev/null 6,10s user 0,54s system 89% cpu 7,390 total 41 > time yes "select * from node limit 1 offset (abs(random()) % (select count(*) from node));" | head -n 100 | sqlite3 db > /dev/null 42 yes 0,00s user 0,01s system 25% cpu 0,031 total 43 head -n 100 0,00s user 0,00s system 27% cpu 0,029 total 44 sqlite3 db > /dev/null 5,71s user 3,78s system 86% cpu 10,917 total