shaf81
asked on
MySQL Query Performance
Hello,
I have a problem with one of my MySQL queries. The query takes about 2 seconds to return a result, where I would expect it to execute in about 100ms or around.
The table `routes` contains about 10 million entries.
Here is the Query, and the Explain Select output:
Here is the Create Table statement, and the Indexes for the Table:
And finally, here is some example data.
The question is, what can I do, to make this query execute faster and give faster output. I'm sure the query could be tuned, and probably some indexes can be tuned as well.
I have a problem with one of my MySQL queries. The query takes about 2 seconds to return a result, where I would expect it to execute in about 100ms or around.
The table `routes` contains about 10 million entries.
Here is the Query, and the Explain Select output:
mysql> SELECT * FROM routes WHERE '8801856812240' RLIKE pattern AND status = 1 AND pricelist_id = '87' And `start_date` < NOW() ORDER BY `start_date` DESC,LENGTH(pattern) DESC LIMIT 1 ;
+----------+----------+------------------------------------+--------------+-----------------+--------------+--------------+------+-------------+------------+--------+---------------------+-------------+
| id | pattern | comment | connectcost | includedseconds | cost | pricelist_id | inc | reseller_id | precedence | status | start_date | import_flag |
+----------+----------+------------------------------------+--------------+-----------------+--------------+--------------+------+-------------+------------+--------+---------------------+-------------+
| 13733365 | ^88018.* | Bangladesh Other Carriers Mobile | 0.0000000000 | 0 | 0.0260000000 | 87 | 1 | 242 | 0 | 1 | 2014-03-05 12:45:00 | 1 |
+----------+----------+------------------------------------+--------------+-----------------+--------------+--------------+------+-------------+------------+--------+---------------------+-------------+
1 row in set (2.49 sec)
mysql> EXPLAIN SELECT * FROM routes WHERE '8801856812240' RLIKE pattern AND status = 1 AND pricelist_id = '87' And `start_date` < NOW() ORDER BY `start_date` DESC,LENGTH(pattern) DESC LIMIT 1;
+----+-------------+--------+-------------+------------------+------------------+---------+------+-------+----------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------------+------------------+------------------+---------+------+-------+----------------------------------------------------------------+
| 1 | SIMPLE | routes | index_merge | pricelist,status | pricelist,status | 4,1 | NULL | 38940 | Using intersect(pricelist,status); Using where; Using filesort |
+----+-------------+--------+-------------+------------------+------------------+---------+------+-------+----------------------------------------------------------------+
1 row in set (0.02 sec)
mysql>
Here is the Create Table statement, and the Indexes for the Table:
CREATE TABLE `routes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pattern` varchar(40) NOT NULL,
`comment` char(80) DEFAULT '',
`connectcost` decimal(20,10) NOT NULL DEFAULT '0.0000000000',
`includedseconds` int(4) NOT NULL,
`cost` decimal(20,10) NOT NULL DEFAULT '0.0000000000',
`pricelist_id` int(4) NOT NULL DEFAULT '0',
`inc` int(4) DEFAULT NULL,
`reseller_id` int(11) DEFAULT '0',
`precedence` int(4) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL DEFAULT '1',
`start_date` datetime NOT NULL,
`import_flag` tinyint(4) NOT NULL DEFAULT '1' COMMENT '0 Means delete this records 1 for active records.',
PRIMARY KEY (`id`),
KEY `pattern` (`pattern`),
KEY `pricelist` (`pricelist_id`),
KEY `reseller` (`reseller_id`),
KEY `status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=14203084 DEFAULT CHARSET=utf8
mysql> show index from routes;
+--------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| routes | 0 | PRIMARY | 1 | id | A | 9141225 | NULL | NULL | | BTREE | |
| routes | 1 | pattern | 1 | pattern | A | 79488 | NULL | NULL | | BTREE | |
| routes | 1 | pricelist | 1 | pricelist_id | A | 1952 | NULL | NULL | | BTREE | |
| routes | 1 | reseller | 1 | reseller_id | A | 18 | NULL | NULL | YES | BTREE | |
| routes | 1 | status | 1 | status | A | 18 | NULL | NULL | | BTREE | |
+--------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.14 sec)
And finally, here is some example data.
mysql> select * from routes limit 10;
+--------+------------+----------------------------+--------------+-----------------+--------------+--------------+------+-------------+------------+--------+---------------------+-------------+
| id | pattern | comment | connectcost | includedseconds | cost | pricelist_id | inc | reseller_id | precedence | status | start_date | import_flag |
+--------+------------+----------------------------+--------------+-----------------+--------------+--------------+------+-------------+------------+--------+---------------------+-------------+
| 440145 | ^1.* | USA Proper | 0.0000000000 | 0 | 0.0071000000 | 22 | 1 | 0 | 0 | 1 | 2013-10-31 18:05:00 | 0 |
| 440146 | ^1201555.* | USA Proper | 0.0000000000 | 0 | 0.0071000000 | 22 | 1 | 0 | 0 | 1 | 2013-10-31 18:05:00 | 0 |
| 440147 | ^1202555.* | USA Proper | 0.0000000000 | 0 | 0.1716000000 | 22 | 1 | 0 | 0 | 1 | 2013-10-31 18:05:00 | 0 |
| 440148 | ^1203555.* | USA Proper | 0.0000000000 | 0 | 0.0071000000 | 22 | 1 | 0 | 0 | 1 | 2013-10-31 18:05:00 | 0 |
| 440149 | ^1204.* | Canada 204-Manitoba Proper | 0.0000000000 | 0 | 0.0029000000 | 22 | 1 | 0 | 0 | 1 | 2013-10-31 18:05:00 | 0 |
| 440150 | ^1205555.* | USA Proper | 0.0000000000 | 0 | 0.0129000000 | 22 | 1 | 0 | 0 | 1 | 2013-10-31 18:05:00 | 0 |
| 440151 | ^1206555.* | USA Proper | 0.0000000000 | 0 | 0.0071000000 | 22 | 1 | 0 | 0 | 1 | 2013-10-31 18:05:00 | 0 |
| 440152 | ^1207555.* | USA Proper | 0.0000000000 | 0 | 0.0071000000 | 22 | 1 | 0 | 0 | 1 | 2013-10-31 18:05:00 | 0 |
| 440153 | ^1208555.* | USA Proper | 0.0000000000 | 0 | 0.1305000000 | 22 | 1 | 0 | 0 | 1 | 2013-10-31 18:05:00 | 0 |
| 440154 | ^1209555.* | USA Proper | 0.0000000000 | 0 | 0.0071000000 | 22 | 1 | 0 | 0 | 1 | 2013-10-31 18:05:00 | 0 |
+--------+------------+----------------------------+--------------+-----------------+--------------+--------------+------+-------------+------------+--------+---------------------+-------------+
10 rows in set (0.00 sec)
The question is, what can I do, to make this query execute faster and give faster output. I'm sure the query could be tuned, and probably some indexes can be tuned as well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.