Is there something else I can do to optimize this MySQL query?
I have two tables, table A with 700,000 records and table B with 600,000 records. The structure looks like this:
Table A:
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| number | bigint(20) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
Table B:
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| number_s | bigint(20) unsigned | YES | MUL | NULL | |
| number_e | bigint(20) unsigned | YES | MUL | NULL | |
| source | varchar(50) | YES | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
I am trying to find if any of the values in table A are present in table B using the following code:
$sql = "SELECT number from TableA";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
$number = $row['number'];
$sql = "SELECT source, count(source) FROM TableB WHERE number_s < $number AND number_e > $number GROUP BY source";
$re = mysql_query($sql) or die(mysql_error);
while($ro = mysql_fetch_array($re)) {
echo $number."\t".$ro[0]."\t".$ro[1]."\n";
}
}
I was hoping the request would go quickly, but for some reason, it's not scary fast. My clarification of choice (with a specific value for "number") gives me the following:
mysql> explain SELECT source, count(source) FROM TableB WHERE number_s < 1812194440 AND number_e > 1812194440 GROUP BY source;
+----+-------------+------------+------+-------------------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+-------------------------+------+---------+------+--------+----------------------------------------------+
| 1 | SIMPLE | TableB | ALL | number_s,number_e | NULL | NULL | NULL | 696325 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+-------------------------+------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)
Is there any optimization I can squeeze out of this?
I tried writing a stored procedure for the same task, but it doesn't even work in the first place ... It doesn't give any syntax errors ... I tried to run it during the day and it was still working, which was weird ...
CREATE PROCEDURE Filter()
Begin
DECLARE number BIGINT UNSIGNED;
DECLARE x INT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT number FROM TableA;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
CREATE TEMPORARY TABLE IF NOT EXISTS Flags(number bigint unsigned, count int(11));
OPEN cur1;
hist_loop: LOOP
FETCH cur1 INTO number;
SELECT count(*) from TableB WHERE number_s < number AND number_e > number INTO x;
IF done = 1 THEN
LEAVE hist_loop;
END IF;
IF x IS NOT NULL AND x>0 THEN
INSERT INTO Flags(number, count) VALUES(number, x);
END IF;
END LOOP hist_loop;
CLOSE cur1;
END
a source to share
You are trying to find intervals that contain a point. It's not that fast with a B-tree index (the default type of index in most databases), however R-tree will work well for this kind of queries. MySQL does not allow you to directly change the index type, but you can force MySQL to use an R-tree using the GEOMETRY column type.
Quassnoi describes this in his article on Nested Sets in MySQL.Although not exactly the same, it is very similar. Quoting from the article:
There is also a certain class of problems that require a search for all ranges containing a known value:
* Searching for an IP address in the IP range ban list * Searching for a given date within a date range
and several others. These tasks can be improved using the R-Tree MySQL capabilities
a source to share
It looks to me like you have separate indexes on the columns number_e
and number_s
probably created with separate columns ADD INDEX(number_e)
and ADD INDEX(number_s)
.
You will probably get much better performance if you add an index that spans both of these columns as they are both used in your query and MySQL clearly does not want to use either of the single column indexes, judging by the fact that the entire table scan it would be faster (not uncommon if your query spans a large range of values).
ALTER TABLE tblB ADD INDEX(number_s,number_e);
You won't need a separate index number_s
after that, as MySQL can only use the one you just created for queries, only with number_s
, so you can opt out of that as well.
a source to share
First, I assume the desired result is to group the entire "source" where the input lies between number_e and number_s, and their count.
I'm partial to the syntax, but you might consider the "BETWEEN" clause instead, instead of an explicit comparison using less / more than operators
Edit: what Zombat says, too; indexes will also help.
a source to share