From e08f3f94a54dbef7b5951d2598eaf6996c38e70d Mon Sep 17 00:00:00 2001 From: Razvan Deaconescu Date: Sun, 7 Aug 2011 12:36:02 +0300 Subject: [PATCH] ppf: Add MySQL sample queries. Useful for determining the proper indexes that should be added. --- ppf/db-mysql/sample-queries.sql | 125 ++++++++++++++++++++++++++++++++ 1 file changed, 125 insertions(+) create mode 100644 ppf/db-mysql/sample-queries.sql diff --git a/ppf/db-mysql/sample-queries.sql b/ppf/db-mysql/sample-queries.sql new file mode 100644 index 0000000..8486aeb --- /dev/null +++ b/ppf/db-mysql/sample-queries.sql @@ -0,0 +1,125 @@ +-- +-- Sample SQL queries to acknowledge the better positioning of indexes. +-- Queries are tested against the p2p_logs database. +-- +-- 2011, Razvan Deaconescu, razvan.deaconescu@cs.pub.ro +-- + +-- select client sessions in a swarm +select count(*) from client_sessions where swarm_id=1; + +-- select status messages for a given client session +select count(*) from status_messages where cs_id=1; + +-- select status messages in a given time range +select count(*) from status_messages where cs_id=1; +timestamp<'2009-07-01 00:00:00' and timestamp>'2009-06-10 00:00:00'; + +-- select status messages for a given swarm +select count(*) from status_messages S, client_sessions C where S.cs_id=C.id and C.swarm_id=1; + +-- select status messages whose download speed is above a +-- certain threshold +select count(*) from status_messages where download_speed>100 and download_speed<1000; + +-- select status messages whose upload speed is above a +-- certain threshold +select count(*) from status_messages where upload_speed>100 and upload_speed<1000; + +-- select peer status messages in a given client session +select count(*) from peer_status_messages where cs_id=1; + +-- select peer status messages in a given time range +select count(*) from peer_status_messages where timestamp<'2009-07-01 00:00:00' and timestamp>'2009-06-10 00:00:00'; + +-- select peer status messages for a given peer +select count(*) from peer_status_messages where peer_ip='89.245.110.136' and peer_port=49887; + +-- select peer status messages for a given swarm +select count(*) from peer_status_messages P, client_sessions C where P.cs_id=C.id and C.swarm_id=1; + +-- select peer status messages whose download speed is above a certain threshold +select count(*) from peer_status_messages where download_speed>100 and download_speed<1000; + +-- select peer status messages whose upload speed is above a certain threshold +select count(*) from peer_status_messages where upload_speed>100 and upload_speed<1000; + +-- select verbose messages for a given client session +select count(*) from verbose_messages where cs_id=1; + +-- select verbose messages of a given type +select count(*) from verbose_messages where message_type=2; + +-- select verbose messages in a given direction +select count(*) from verbose_messages where direction=1; + +-- select verbose messages in a given time range +select count(*) from verbose_messages where timestamp<'2009-07-01 00:00:00' and timestamp>'2009-06-10 00:00:00'; + +-- select verbose messages for a given peer +select count(*) from verbose_messages where peer_ip='89.245.110.136' and peer_port=49887; + +-- select all peers in verbose messages +-- without an index, query takes around 2.0 seconds (on p2p-next-10) +select distinct peer_ip,peer_port from verbose_messages; + +-- select all peers for a given client session +select distinct peer_ip,peer_port from verbose_messages where cs_id=1; + +-- select all peers for a given swarm +select distinct peer_ip,peer_port from verbose_messages V, client_sessions C where V.cs_id=C.id and swarm_id=1; + +-- select number of verbose messages exchanged with each peer +-- with no index, query takes around 4.5 seconds (on p2p-next-10) +select peer_ip,peer_port,count(*) from verbose_messages group by peer_ip,peer_port; + +-- select verbose messages in a swarm +select count(*) from verbose_messages V, client_sessions C where C.id=V.cs_id and C.swarm_id=1; + +-- select verbose messages of a given type for a given client session +select count(*) from verbose_messages where cs_id=1 and message_type=2; + +-- select verbose messages of a given direction for a given client session +select count(*) from verbose_messages where cs_id=1 and direction=1; + +-- select verbose messages in a given time range for a given client session +select count(*) from verbose_messages where cs_id=1 and message_type=2; +timestamp<'2009-07-01 00:00:00' and timestamp>'2009-06-10 00:00:00'; + +-- select verbose messages for a given peer in a client session +select count(*) from verbose_messages where cs_id=1 and peer_ip='89.245.110.136' and peer_port=49887; + +-- select verbose messages of a given type in a given direction +select count(*) from verbose_messages where direction=1 and message_type=2; + +-- select verbose messages of a given type in a given time range +select count(*) from verbose_messages where message_type=2 and timestamp<'2009-07-01 00:00:00' and timestamp>'2009-06-10 00:00:00'; + +-- select verbose messages of a given direction in a given time range +select count(*) from verbose_messages where direction=1 and timestamp<'2009-07-01 00:00:00' and timestamp>'2009-06-10 00:00:00'; + +-- select verbose messages of a given type for a given peer +select count(*) from verbose_messages where peer_ip='89.245.110.136' and peer_port=49887 and message_type=2; + +-- select verbose messages in a given direction for a given peer +select count(*) from verbose_messages where direction=1 and peer_ip='89.245.110.136' and peer_port=49887; + +-- select verbose messages of a given type in a swarm +select count(*) from verbose_messages V, client_session C where C.id=V.cs_id and message_type=2 and swarm_id=1; + +-- select verbose messages of a given swarm in a given direction +select count(*) from verbose_messages V, client_session C where C.id=V.cs_id and direction=1 and swarm_id=1; + +-- select verbose messages of a given type in a given direction for a given +-- client session +select count(*) from verbose_messages where cs_id=1 and direction=1 and message_type=2; + +-- select verbose messages of a given type in a given direction for a given +-- swarm +select count(*) from verbose_messages V, client_session C where C.id=V.cs_id and direction=1 and message_type=2 and swarm_id=1; + +-- select verbose messages of a given type for a given peer in a client session +select count(*) from verbose_messages where cs_id=1 and peer_ip='89.245.110.136' and peer_port=49887 and message_type=2; + +-- select verbose messages of a given type in a given direction for a given peer +select count(*) from verbose_messages where cs_id=1 and direction=1 and peer_ip='89.245.110.136' and peer_port=49887; -- 2.20.1