From 0061282de9ed044225d74f05811b2dc12c1a7f90 Mon Sep 17 00:00:00 2001 From: Mariana Marasoiu Date: Mon, 8 Aug 2011 15:58:13 +0300 Subject: [PATCH] ppf: Add index support to MySQL tables. --- ppf/sql/p2p-log-mysql.sql | 23 ++++++++++++++++++++++- 1 file changed, 22 insertions(+), 1 deletion(-) diff --git a/ppf/sql/p2p-log-mysql.sql b/ppf/sql/p2p-log-mysql.sql index 32fe4a8..9b5a0e8 100644 --- a/ppf/sql/p2p-log-mysql.sql +++ b/ppf/sql/p2p-log-mysql.sql @@ -64,7 +64,7 @@ CREATE TABLE client_sessions ( CREATE TABLE status_messages ( id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, - client_session_id INTEGER NOT NULL REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE, + client_session_id INTEGER UNSIGNED NOT NULL REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE, timestamp DATETIME NOT NULL, num_peers SMALLINT UNSIGNED, num_dht_peers SMALLINT UNSIGNED, @@ -120,6 +120,27 @@ CREATE TABLE verbose_messages ( listen_port SMALLINT UNSIGNED ) ENGINE=InnoDB; +-- create indexes +CREATE INDEX swarm_id_index ON client_sessions(swarm_id); +CREATE INDEX btclient_id_index ON client_sessions(btclient_id); + +CREATE INDEX client_session_id_index ON status_messages(client_session_id); +CREATE INDEX timestamp_index ON status_messages(timestamp); +CREATE INDEX download_speed_index ON status_messages(download_speed); +CREATE INDEX upload_speed_index ON status_messages(upload_speed); + +CREATE INDEX client_session_id_index ON peer_status_messages(client_session_id); +CREATE INDEX timestamp_index ON peer_status_messages(timestamp); +CREATE INDEX download_speed_index ON peer_status_messages(download_speed); +CREATE INDEX upload_speed_index ON peer_status_messages(upload_speed); +CREATE INDEX peer_ip_port_index ON peer_status_messages(peer_ip, peer_port); + +CREATE INDEX client_session_id_index ON verbose_messages(client_session_id); +CREATE INDEX timestamp_index ON verbose_messages(timestamp); +CREATE INDEX direction_id_index ON verbose_messages(transfer_direction_id); +CREATE INDEX peer_ip_port_index ON verbose_messages(peer_ip, peer_port); +CREATE INDEX message_type_id_index ON verbose_messages(message_type_id); + -- insert BitTorrent clients in `btclients` table INSERT INTO btclients(name, language, url, dht_support, streaming_support) -- 2.20.1