Soborno Soborno - 14 days ago 9
MySQL Question

Asterisk cdr mysql query performance issue

I'm having a problem that I'm not able to understand..
I have two queries (the database is MySQL, with only one table with all the cdr data):

SELECT * FROM cdr WHERE userfield LIKE 'audio:%'
AND (calldate >= '2016-10-26 00:00:00'
AND calldate <= '2016-10-26 23:59:59')
AND duration > 8
ORDER BY uniqueid DESC LIMIT 200 OFFSET 0;


which runs in 0.05 secs and another one:

SELECT * FROM cdr WHERE userfield LIKE 'audio:%'
AND (calldate >= '2016-10-26 15:00:00'
AND calldate <= '2016-10-26 16:00:00')
AND duration > 8
ORDER BY uniqueid DESC LIMIT 200 OFFSET 0;


which runs in 39.12 secs!! (note that the only difference are in the calldate fields)

I'm really not know much about performance in MySQL so I don't know where to start, maybe an index issue?

Answer

1) create index for calldate field(or do "show index from cdr" to see if you already have one

http://dev.mysql.com/doc/refman/5.7/en/show-index.html

2) Create query which use index.

http://dev.mysql.com/doc/refman/5.7/en/index-hints.html