All queries very fast, except one
Posted: Tue Oct 23, 2012 3:55 pm
I'm designing an app that does nothing but selects from a pre-populated db. (FreeBSD 8.3, mysql 5.5.22, php 5.4.7). I'm constructing queries based on user selected data from a form.
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:
Running queries directly against the db is very fast. For example:
Running this query takes four times as much time, but still very fast.
The problem is, all of these queries display in the browser almost instantly except the last one. It takes 95 seconds to complete.
I've run EXPLAIN on the queries. All three are identical:
I've looked at the query log to see if the query is being altered somehow:
The stime column is an index and a primary key. This problem doesn't appear to be with the db. It seems to be something in my code, but I can't figure out what. Here's how I'm constructing the queries. The three key var names are $whereStr, $rangeStr and $timeClause. $whereStr is a concatenated string of the saddr, sport, daddr and dport columns like this saddr='x.x.x.x' and daddr='x.x.x.x'. The $rangeStr is a range of network addresses (e.g. BETWEEN 10.0.0.0 and 10.255.255.255.) The $timeClause is a range of dates in unixtime format.
I'm not sure where to go from here. Is there a performance measurement tool that can analyze what's going on under the sheets and tell me where the delay is being introduced?
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;