|
Hi,
Have you found a version or a plan (e.g. with forced indexes) where the query would go considerably faster?
|
|
Yes, i found solution width stored procedure
|
|
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.
|
|
|
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
|
|
It appears to be an unfortunate case of random InnoDB statistics. Sometimes I am getting a better plan after ANALYZE.
Engine-independent table statistics seems to help:
|
Bad plan with random statistics
|
MariaDB [test]> source explain.sql
|
+------+-------------+-------+--------+---------------------------------------+-------------+---------+------------------------+-------+------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------------------------------+-------------+---------+------------------------+-------+------------------------------------------------------------------------+
|
| 1 | SIMPLE | tr | index | PRIMARY,trunk | trunk | 302 | NULL | 1 | Using index; Using temporary; Using filesort |
|
| 1 | SIMPLE | td | ALL | PRIMARY,tuid | NULL | NULL | NULL | 6 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | tz | ref | unique | unique | 4 | astcdr.td.uid | 101 | Using index |
|
| 1 | SIMPLE | zi | eq_ref | PRIMARY,ispuid,regionuid,subregionuid | PRIMARY | 4 | astcdr.tz.zone | 1 | |
|
| 1 | SIMPLE | i | eq_ref | PRIMARY,uid | PRIMARY | 4 | astcdr.zi.ispuid | 1 | |
|
| 1 | SIMPLE | s | eq_ref | PRIMARY,uid | PRIMARY | 4 | astcdr.zi.subregionuid | 1 | |
|
| 1 | SIMPLE | r | eq_ref | PRIMARY,uid | PRIMARY | 4 | astcdr.zi.regionuid | 1 | |
|
| 1 | SIMPLE | c | ref | zoneinfouid,abcdef | zoneinfouid | 4 | astcdr.tz.zone | 10 | |
|
| 1 | SIMPLE | h | range | calldate | calldate | 4 | NULL | 15082 | Using index condition; Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+--------+---------------------------------------+-------------+---------+------------------------+-------+------------------------------------------------------------------------+
|
9 rows in set (0.01 sec)
|
|
MariaDB [test]> source query.sql
|
Empty set (11.61 sec)
|
MariaDB [test]> set use_stat_tables=PREFERABLY;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> analyze table astcdr.codes, astcdr.isp, astcdr.numtype, astcdr.region, astcdr.subregion, astcdr.tariff, astcdr.`tariff-data`, astcdr.`tariff-zones`, astcdr.update_info, astcdr.zone, astcdr.zoneinfo, asteriskcdrdb.cdr;
|
+---------------------+---------+----------+-----------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------------------+---------+----------+-----------------------------------------+
|
| astcdr.codes | analyze | status | Engine-independent statistics collected |
|
| astcdr.codes | analyze | status | OK |
|
| astcdr.isp | analyze | status | Engine-independent statistics collected |
|
| astcdr.isp | analyze | status | OK |
|
| astcdr.numtype | analyze | status | Engine-independent statistics collected |
|
| astcdr.numtype | analyze | status | OK |
|
| astcdr.region | analyze | status | Engine-independent statistics collected |
|
| astcdr.region | analyze | status | OK |
|
| astcdr.subregion | analyze | status | Engine-independent statistics collected |
|
| astcdr.subregion | analyze | status | OK |
|
| astcdr.tariff | analyze | status | Engine-independent statistics collected |
|
| astcdr.tariff | analyze | status | OK |
|
| astcdr.tariff-data | analyze | status | Engine-independent statistics collected |
|
| astcdr.tariff-data | analyze | status | OK |
|
| astcdr.tariff-zones | analyze | status | Engine-independent statistics collected |
|
| astcdr.tariff-zones | analyze | status | OK |
|
| astcdr.update_info | analyze | status | Engine-independent statistics collected |
|
| astcdr.update_info | analyze | status | OK |
|
| astcdr.zone | analyze | status | Engine-independent statistics collected |
|
| astcdr.zone | analyze | status | OK |
|
| astcdr.zoneinfo | analyze | status | Engine-independent statistics collected |
|
| astcdr.zoneinfo | analyze | status | OK |
|
| asteriskcdrdb.cdr | analyze | status | Engine-independent statistics collected |
|
| asteriskcdrdb.cdr | analyze | status | Table is already up to date |
|
+---------------------+---------+----------+-----------------------------------------+
|
24 rows in set (25.81 sec)
|
|
Better plan with EITS
|
MariaDB [test]> source explain.sql
|
+------+-------------+-------+--------+---------------------------------------+----------+---------+------------------------------------+-------+------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------------------------------+----------+---------+------------------------------------+-------+------------------------------------------------------------------------+
|
| 1 | SIMPLE | tr | index | PRIMARY,trunk | trunk | 302 | NULL | 1 | Using index; Using temporary; Using filesort |
|
| 1 | SIMPLE | h | range | calldate | calldate | 4 | NULL | 15082 | Using index condition; Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | c | ref | zoneinfouid,abcdef | abcdef | 2 | func | 1540 | Using index condition |
|
| 1 | SIMPLE | zi | eq_ref | PRIMARY,ispuid,regionuid,subregionuid | PRIMARY | 4 | astcdr.c.zoneinfouid | 1 | |
|
| 1 | SIMPLE | i | eq_ref | PRIMARY,uid | PRIMARY | 4 | astcdr.zi.ispuid | 1 | |
|
| 1 | SIMPLE | s | eq_ref | PRIMARY,uid | PRIMARY | 4 | astcdr.zi.subregionuid | 1 | |
|
| 1 | SIMPLE | r | eq_ref | PRIMARY,uid | PRIMARY | 4 | astcdr.zi.regionuid | 1 | |
|
| 1 | SIMPLE | td | ALL | PRIMARY,tuid | NULL | NULL | NULL | 6 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | tz | eq_ref | unique | unique | 8 | astcdr.td.uid,astcdr.c.zoneinfouid | 1 | Using index |
|
+------+-------------+-------+--------+---------------------------------------+----------+---------+------------------------------------+-------+------------------------------------------------------------------------+
|
|
MariaDB [test]> source query.sql
|
Empty set (0.09 sec)
|
Is it what you were after?
|
|
Please comment to re-open if you have new information on the issue.
|
2dbs.zip