The basic architecture is an index page, a result page and several includes - view_tables.php which constructs the display, search_form.php which takes in the user selected data using POST and search_functions.php which constructs the queries based on the user selected data. View_tables is an include on the results page and calls the displayTables() function from search_functions.
Here's the db schema:
Code: Select all
CREATE TABLE `argData` (
`ts` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`seq` INT (12) UNSIGNED NOT NULL,
`stime` CHAR (18) NOT NULL,
`saddr` VARCHAR (15),
`sport` SMALLINT (5) UNSIGNED NOT NULL,
`daddr` VARCHAR (15),
`dport` SMALLINT (5) UNSIGNED NOT NULL,
`pkts` MEDIUMINT (8) UNSIGNED NOT NULL,
`bytes` BIGINT (20) UNSIGNED NOT NULL,
`state` TINYTEXT ASCII NOT NULL,
`proto` VARCHAR (15),
`record` BLOB,
PRIMARY KEY (`seq`,`stime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;Code: Select all
SELECT * FROM argData WHERE daddr='98.139.112.123' AND saddr BETWEEN '10.0.0.0' AND '10.255.255.255' AND stime BETWEEN 1349740800.000000 AND 1350950400.000000; - 0.09 secondsCode: Select all
SELECT * FROM argData WHERE daddr='98.139.112.123' AND saddr BETWEEN '10.0.0.0' AND '10.255.255.255' AND stime like '13497%'; - 0.09 secondsCode: Select all
SELECT * FROM argData WHERE stime like '13497%'; - 0.35 secondsI've run EXPLAIN on the queries. All three are identical:
Code: Select all
mysql> EXPLAIN SELECT * FROM argData WHERE saddr='10.21.22.2' and stime like '13497%';
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | argData | ALL | NULL | NULL | NULL | NULL | 91226 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)Code: Select all
121023 19:27:09 20 Query SELECT * FROM argData WHERE stime like '13497%'
21 Query SELECT * FROM argData WHERE stime like '13497%'
121023 19:41:35 20 Query EXPLAIN SELECT * FROM argData WHERE stime like '13497%'
121023 19:42:38 20 Query EXPLAIN EXTENDED SELECT * FROM argData WHERE stime like '13497%'
121023 19:46:24 24 Query SELECT * FROM argData WHERE daddr='98.139.112.123' AND saddr BETWEEN '10.0.0.0' AND '10.255.255.255' AND stime like '13497%'
26 Query SELECT * FROM argData WHERE daddr='98.139.112.123' AND saddr BETWEEN '10.0.0.0' AND '10.255.255.255' AND stime like '13497%'
121023 19:54:54 24 Query explain select stime from argData where stime like '13497%'
121023 20:19:37 24 Query EXPLAIN SELECT * FROM argData WHERE stime like '13497%'
121023 20:20:45 24 Query EXPLAIN SELECT * FROM argData WHERE saddr='10.21.22.2' and stime like '13497%'
121023 20:21:34 24 Query EXPLAIN SELECT * FROM argData WHERE daddr='98.139.112.123' AND saddr BETWEEN '10.0.0.0' AND '10.255.255.255' AND stime like '13497%'
121023 20:22:09 24 Query EXPLAIN EXTENDED SELECT * FROM argData WHERE daddr='98.139.112.123' AND saddr BETWEEN '10.0.0.0' AND '10.255.255.255' AND stime like '13497%'
121023 20:23:25 29 Query SELECT * FROM argData WHERE stime like '13497%'
121023 20:24:51 29 Query SELECT * FROM argData WHERE daddr='98.139.112.123' AND saddr BETWEEN '10.0.0.0' AND '10.255.255.255' AND stime like '13497%'
121023 20:39:51 32 Query EXPLAIN SELECT * FROM argData WHERE saddr='10.21.22.2' and stime like '13497%'Code: Select all
# Dislay a table of the results of the query
if ($timeClause && $rangeStr && $whereStr) {
echo "SELECT * FROM $dbtable WHERE $whereStr AND $rangeStr AND $timeClause";
$query = mysql_query("SELECT * FROM {$dbtable} WHERE {$whereStr} AND {$rangeStr} AND {$timeClause}");
}elseif ($timeClause && $whereStr) {
echo "SELECT * FROM $dbtable WHERE $wherestr AND $timeClause";
$query = mysql_query("SELECT * FROM {$dbtable} WHERE {$whereStr} AND {$timeClause}");
}elseif ($rangeStr && $whereStr) {
echo "SELECT * FROM $dbtable WHERE $whereStr AND $rangeStr";
$query = mysql_query("SELECT * FROM {$dbtable} WHERE {$whereStr} AND {$rangeStr}");
}elseif ($imeClause && $rangeStr) {
echo "SELECT * FROM $dbtable WHERE $rangeStr AND $timeClause";
$query = mysql_query("SELECT * FROM {$dbtable} WHERE {$rangeStr} AND {$timeClause}");
}elseif ($rangeStr) {
echo "SELECT * FROM $dbtable WHERE $rangeStr";
$query = mysql_query("SELECT * FROM {$dbtable} WHERE {$rangeStr}");
}elseif ($timeClause) {
echo "SELECT * FROM $dbtable WHERE $timeClause";
$query = mysql_query("SELECT * FROM {$dbtable} WHERE {$timeClause}");
}else{
echo "SELECT * FROM {$dbtable} WHERE {$whereStr}";
$query = mysql_query("SELECT * FROM {$dbtable} WHERE {$whereStr}");
}
if (!$query) {
die("Query {$query} failed");
}
return $query;