0

I have a query to get data between current time and INTERVAL 15 minutes

Table calls have 39790720 items;

SELECT src,unique,dstchannel,chan,calldate 
from calls 
WHERE calldate BETWEEN (NOW() - INTERVAL 15 MINUTE) AND NOW() 
AND (dstchannel LIKE '%TEXT1/%' 
OR dstchannel LIKE '%TEXT2%' 
OR dstchannel LIKE '%TEXT3%' 
OR dstchannel REGEXP '^SIP/[[:digit:]]{10}-' 
OR dstchannel LIKE '%TEXT4%' 
OR dstchannel LIKE '%TEXT5%' 
OR dstchannel LIKE '%TEXT6%' 
OR dstchannel LIKE '%TEXT7%') 
AND lastdata NOT LIKE '%TEXT8%' 
LIMIT 39780720,39790720
Query 1 row in set (1 min 7.38 sec)

+-------------+--------------+------+-----+---------------------+-------+
| Field       | Type         | Null | Key | Default             | Extra |
+-------------+--------------+------+-----+---------------------+-------+
| calldate    | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| colum1      | varchar(80)  | NO   |     |                     |       |
| colum11     | varchar(80)  | NO   |     |                     |       |
| src         | varchar(80)  | NO   |     |                     |       |
| colum12     | varchar(80)  | NO   |     |                     |       |
| chan        | varchar(80)  | NO   |     |                     |       |
| dstchannel  | varchar(80)  | NO   |     |                     |       |
| colum2      | varchar(80)  | NO   |     |                     |       |
| colum3      | varchar(80)  | NO   |     |                     |       |
| colum4      | int(11)      | NO   |     | 0                   |       |
| colum5      | int(11)      | NO   |     | 0                   |       |
| colum6      | varchar(45)  | NO   |     |                     |       |
| colum7      | int(11)      | NO   |     | 0                   |       |
| colum8      | varchar(20)  | NO   |     |                     |       |
| colum9      | varchar(32)  | NO   |     |                     |       |
| colum10     | varchar(255) | NO   |     |                     |       |
+-------------+--------------+------+-----+---------------------+-------+

+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | calls | ALL  | NULL          | NULL | NULL    | NULL | 39791545 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+

When %TEXT1% is a variable only need contain like '%Sip/phone%' in value.

How to improve query?

closed as unclear what you're asking by Philᵀᴹ, Andriy M, dezso, mustaccio, Tom V Oct 21 '16 at 18:29

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Improve in what way? – mustaccio Oct 21 '16 at 14:41
2

To enhance the performance:

  • First of all, add an index on calldate. It will avoid you scanning the whole table.

In the query:

  • Not sure what is unique in the query.
  • LIMIT 39780720,39790720 means, "Retrieve 39790720 rows starting from the row number 39790720 in the result". I think you want to revise it.

About the table structure:

  • It is recommended to have primary key

Not the answer you're looking for? Browse other questions tagged or ask your own question.