Elena Stepanova added a comment - 2015-09-01 01:10 Hi, Have you found a version or a plan (e.g. with forced indexes) where the query would go considerably faster?

Dmitry added a comment - 2015-09-01 16:21 Yes, i found solution width stored procedure

Elena Stepanova added a comment - 2015-09-01 16:34 Do you mean that putting this query inside a stored procedure made it faster? Or that you re-wrote your code to avoid the query completely, and to achieve the result in some other way, using a stored procedure? My point is, we can only say that a query is slow when we have something to compare it with. It can be either the same query executed on a different version (of MariaDB, MySQL, Percona Server, another MySQL-based server), or the same query somehow forced to use a different execution plan, e.g. via USE/FORCE/IGNORE INDEX . In some simple cases, it can be claimed that a query must use a different execution plan, if there is a good explanation why it is so. So, I'm trying to understand the nature of your initial report. When you are saying that the query is not using right indexes automatically and it makes it slow, does it mean you made it use them manually and observed an improvement? Or did you see them used by a different server, resulting in a better performance? Or do you have a good reason to think that using a particular index will make the query considerably faster? In either case, it would be very helpful if you provided execution plan that you currently have and the desired execution plan.

Dmitry added a comment - 2015-09-01 16:42 - edited stored procedure BEGIN DECLARE maxstart int(7); DECLARE res int(15); SELECT max(cdir.start) FROM codes cdir WHERE cdir.abcdef = prefix AND cdir.start <= num LIMIT 1 INTO maxstart; SELECT uid FROM codes WHERE codes.abcdef = prefix and codes.start=maxstart AND codes.end >= num LIMIT 1 INTO res; RETURN res; END query SELECT SQL_NO_CACHE b.calldate, b.src, CONCAT(b.prefix, b.number) AS dst, b.duration, b.billsec, b.billmin, b.trunk, td.price, ROUND(CEIL(b.billmin)*td.price, 2) as bill, i.ispname,s.subregionname,r.regionname, s.subregionname, r.regionname, (b.prefix=c.abcdef AND b.number BETWEEN c.start AND c.end) AS code_is_valid FROM ( SELECT h.calldate, h.src, SUBSTRING( RIGHT( LPAD( h.dst, 12, '9' ) , 10 ) , 1, 3 ) AS prefix, SUBSTRING( RIGHT( LPAD( h.dst, 12, '9' ) , 10 ) , 4, 7 ) AS number, h.duration, h.billsec, ROUND((CASE h.billsec WHEN 0 THEN 1 ELSE h.billsec END)/60, 2) AS billmin, REGEXP_REPLACE(h.dstchannel, "(?:SIP|OOH323|LOCAL|SCCP)/(?:FMPR|FMGL)?([a-zA-Z_]+)?.*" , '\\1') as trunk, ( SELECT get_cuid(prefix,number)) AS codeuid FROM cdr h FORCE INDEX (calldate) WHERE calldate BETWEEN '2015-05-01 00:00:00' AND '2015-05-31 23:59:59' AND LENGTH( h.src ) <=3 AND LENGTH( h.dst ) >3 AND h.disposition='ANSWERED' ) AS b INNER JOIN codes c ON b.codeuid=c.uid INNER JOIN zoneinfo zi ON zi.uid=c.zoneinfouid INNER JOIN isp i ON i.uid=zi.ispuid INNER JOIN region r ON r.uid=zi.regionuid INNER JOIN subregion s ON s.uid=zi.subregionuid inner join astcdr.tariff tr on tr.trunk=b.trunk inner join astcdr.`tariff-data` td on td.tuid=tr.uid inner join astcdr.`tariff-zones` tz on tz.zone=zi.uid and tz.tduid=td.uid WHERE b.trunk='Beeline' ORDER BY b.calldate DESC returns result 0.4 seconds