1

Topic: Why does not see an index. Well and generally how to accelerate request... :)

Request:

select *
from cdr
WHERE cdr.calldate> ' 2017-10-06 ' and cdr.dst like ' 0747 ' AND cdr.dstchannel = ' SIP/0747-00001746'

EXPLAIN:
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1, SIMPLE, cdr, range, calldate dst dstchannel, dst, 242, NULL, 9878, Using where
Why it does not use

INDEX ` dstchannel ` (` calldate `, ` dst `, ` dstchannel `)

?
And how to make request faster? Result 1 line.
The table:
[spoiler]

CREATE TABLE ` cdr ` (
` calldate ` DATETIME NOT NULL DEFAULT ' 0000-00-00 0:00:00 AM ';
` clid ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` src ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` dst ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` dcontext ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` channel ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` dstchannel ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` lastapp ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` lastdata ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` duration ` INT (11) NOT NULL DEFAULT ' 0 ';
` billsec ` INT (11) NOT NULL DEFAULT ' 0 ';
` disposition ` VARCHAR (45) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` amaflags ` INT (11) NOT NULL DEFAULT ' 0 ';
` accountcode ` VARCHAR (20) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` uniqueid ` VARCHAR (32) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` userfield ` VARCHAR (255) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` did ` VARCHAR (50) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` recordingfile ` VARCHAR (255) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` cnum ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` cnam ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` outbound_cnum ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` outbound_cnam ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` dst_cnam ` VARCHAR (80) NOT NULL DEFAULT "COLLATE ' utf8_unicode_ci ';
` call_id ` VARCHAR (255) NULL DEFAULT NULL COLLATE ' utf8_unicode_ci ';
` call_route ` VARCHAR (255) NULL DEFAULT NULL COLLATE ' utf8_unicode_ci ';
` filename ` VARCHAR (255) NULL DEFAULT NULL COLLATE ' utf8_unicode_ci ';
` caller ` VARCHAR (255) NULL DEFAULT NULL COLLATE ' utf8_unicode_ci ';
` called ` VARCHAR (255) NULL DEFAULT NULL COLLATE ' utf8_unicode_ci ';
` start ` DATETIME NULL DEFAULT ' 0000-00-00 0:00:00 AM ';
` answer ` DATETIME NULL DEFAULT ' 0000-00-00 0:00:00 AM ';
` end ` DATETIME NULL DEFAULT ' 0000-00-00 0:00:00 AM ';
` linkedid ` VARCHAR (32) NULL DEFAULT NULL COLLATE ' utf8_unicode_ci ';
` sequence ` INT (11) NULL DEFAULT NULL;
` qeholdtime ` VARCHAR (255) NULL DEFAULT NULL COLLATE ' utf8_unicode_ci ';
` qeorigpos ` VARCHAR (255) NULL DEFAULT NULL COLLATE ' utf8_unicode_ci ';
INDEX ` calldate ` (` calldate `);
INDEX ` dst ` (` dst `);
INDEX ` accountcode ` (` accountcode `);
INDEX ` uniqueid ` (` uniqueid `);
INDEX ` did ` (` did `);
INDEX ` dstchannel ` (` calldate `, ` dst `, ` dstchannel `)
)
COLLATE ='utf8_unicode_ci'
ENGINE=InnoDB
;

[/spoiler]

2

Re: Why does not see an index. Well and generally how to accelerate request... :)

1. What speed now? Measure 3 times using SQL_NO_CACHE
Type:
select SQL_NO_CACHE *
from cdr
WHERE cdr.calldate> ' 2017-10-06 '....................
2. What speed you want?
3.  speaks that DIS the index is used.
I.e. a situation not " an index"and" an index
Which I (for some reason) want that mysql used"
4. cdr.dst like ' 0747 ' it is possible to replace on cdr.dst = ' 0747'
5. That  with indexes, it is necessary
To "profile" the data, , dispersion;
Specific samplings...
To begin with produce result of such request:
select count (1) cnt1;
count (cdr.calldate> ' 2017-10-06 ') cnt2;
count (cdr.dst = ' 0747 ') cnt3;
count (cdr.dstchannel = ' SIP/0747-00001746 ') cnt4;
from cdr;

3

Re: Why does not see an index. Well and generally how to accelerate request... :)

1. 0,7 seconds.
2. I want 0,2 at least
3. Well but I an index am direct on all search boxes to it gave, what not so that...
4. Yes there also was =, not that wrote because of different samples of requests, it does not influence result
5. cnt1, cnt2, cnt3, cnt4
3858473,3858473,3858473,3858473
Well really at me a problem here what, and I as the simplified variant showed it smile
[spoiler]
Request

SELECT
RIGHT (` queue_log `. ` agent `, 4) AS ` the Agent`
,` queue_log `. ` data1 ` AS ` VhodVGruppu`
,` queue_log `. ` time ` AS ` VyhodIzGruppy`
, TIME_TO_SEC (TIMEDIFF (` queue_log `. ` time `, IF ((` queue_log `. ` data1 ` = "), ` queue_log `. ` time `, ` queue_log `. ` data1 `))) AS ` VremjaVGruppe`
,` cdr `. ` uniqueid ` AS ` `
,` cdr `. ` src ` AS ` the Subscriber`
, IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ') AS ` VremjaNachala`
,` cdr `. ` end ` AS ` `
,` cdr `. ` billsec ` AS ` VremjaRazgovora`
, SUBSTR (` cdr `. ` dstchannel`
,(LOCATE (' / ', ` cdr `. ` dstchannel `) + 1), 4) AS ` AgentCDR`
,` cdr `. ` disposition ` AS ` the Status`
FROM (` queue_log ` LEFT JOIN ` cdr ` ON (((SUBSTR (` cdr `. ` dstchannel `, 5,4) = RIGHT (` queue_log `. ` agent `, 4))
AND (IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ')> = ` queue_log `. ` data1 `) AND (IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ') <= ` queue_log `. ` time `)
AND (` cdr `. ` dst ` LIKE ' 07 % ') AND (` cdr `. ` calldate `> ' 2017-10-06 '))))
WHERE ((` queue_log `. ` queuename ` = ' Programmist_queue ') AND (` queue_log `. ` event ` = ' REMOVEMEMBER ') AND (` queue_log `. ` data1 ` <> ") AND (` queue_log `. ` time `> ' 2017-10-06 '))
UNION
SELECT
RIGHT (` queue_log `. ` agent `, 4) AS ` the Agent`
,` queue_log `. ` data1 ` AS ` VhodVGruppu`
,` queue_log `. ` time ` AS ` VyhodIzGruppy`
, TIME_TO_SEC (TIMEDIFF (` queue_log `. ` time `, IF ((` queue_log `. ` data1 ` = "), ` queue_log `. ` time `, ` queue_log `. ` data1 `))) AS ` VremjaVGruppe`
,` cdr `. ` uniqueid ` AS ` `
,` cdr `. ` src ` AS ` the Subscriber`
, IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ') AS ` VremjaNachala`
,` cdr `. ` end ` AS ` `
,` cdr `. ` billsec ` AS ` VremjaRazgovora`
, SUBSTR (` cdr `. ` dstchannel`
,(LOCATE (' / ', ` cdr `. ` dstchannel `) + 1), 4) AS ` AgentCDR`
,` cdr `. ` disposition ` AS ` the Status`
FROM (` cdr ` LEFT JOIN ` queue_log ` ON (((SUBSTR (` cdr `. ` dstchannel `, 5,4) = RIGHT (` queue_log `. ` agent `, 4))
AND (IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ')> = ` queue_log `. ` data1 `) AND (IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ') <= ` queue_log `. ` time `)
AND (` queue_log `. ` queuename ` = ' Programmist_queue ') AND (` queue_log `. ` event ` = ' REMOVEMEMBER ') AND (` queue_log `. ` data1 ` <> ") AND (` queue_log `. ` time `> ' 2017-10-06 '))))
WHERE ((` cdr `. ` dst ` LIKE ' 07 % ') AND (` cdr `. ` calldate `> ' 2017-10-06 ') AND (` cdr `. ` dstchannel ` <> "))

EXPLAIN
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1, PRIMARY, queue_log, ref, Event Time Data1, Event, 97, const, 200, Using where
1, PRIMARY, cdr, range, calldate dst dstchannel, dst, 242, NULL, 30976, Using where
2, UNION, cdr, range, calldate dst dstchannel, dst, 242, NULL, 30976, Using where
2, UNION, queue_log, ref, Even Time Data1, Event, 97, const, 200;
NULL, UNION RESULT, <union1 2>, ALL, NULL, NULL, NULL, NULL, NULL;
The table queue_log not big and  does not give, and here CDR 2 already.
And here this big request is fulfilled under 54 seconds....
[/spoiler]

4

Re: Why does not see an index. Well and generally how to accelerate request... :)

Apropos :
[spoiler] TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,NON_UNIQUE,INDEX_SCHEMA,INDEX_NAME,SEQ_IN_INDEX,COLUMN_NAME,COLLATION,CARDINALITY,SUB_PART,PACKED,NULLABLE,INDEX_TYPE,COMMENT
NULL,asteriskcdrdb,cdr,1,asteriskcdrdb,calldate,1,calldate,A,3821260,NULL,NULL,,BTREE;
NULL,asteriskcdrdb,cdr,1,asteriskcdrdb,dst,1,dst,A,127375,NULL,NULL,,BTREE;
NULL,asteriskcdrdb,cdr,1,asteriskcdrdb,accountcode,1,accountcode,A,17,NULL,NULL,,BTREE;
NULL,asteriskcdrdb,cdr,1,asteriskcdrdb,uniqueid,1,uniqueid,A,3821260,NULL,NULL,,BTREE;
NULL,asteriskcdrdb,cdr,1,asteriskcdrdb,did,1,did,A,17,NULL,NULL,,BTREE;
NULL,asteriskcdrdb,cdr,1,asteriskcdrdb,dstchannel,1,calldate,A,3821260,NULL,NULL,,BTREE;
NULL,asteriskcdrdb,cdr,1,asteriskcdrdb,dstchannel,2,dst,A,3821260,NULL,NULL,,BTREE;
NULL,asteriskcdrdb,cdr,1,asteriskcdrdb,dstchannel,3,dstchannel,A,3821260,NULL,NULL,,BTREE;
NULL,asteriskcdrdb,queue_log,1,asteriskcdrdb,Event,1,event,A,18,NULL,NULL,YES,BTREE;
NULL,asteriskcdrdb,queue_log,1,asteriskcdrdb,Time,1,time,A,518934,NULL,NULL,YES,BTREE;
NULL,asteriskcdrdb,queue_log,1,asteriskcdrdb,Data1,1,data1,A,664,NULL,NULL,YES,BTREE;
[/spoiler]

5

Re: Why does not see an index. Well and generally how to accelerate request... :)

TimofeySin;
...Try to change the order of fields in an index -
If on the first place the field on which goes ">"  -
is worse than on the first place a field in the BIG cardinality.
Is better -  a field the first (or even )...
...It is difficult to analyze  - produce particulars ()
Most  from two unions....
....If you are assured of an index - look as to FORCE an index -
Is  type USE INDEX, FORCE INDEX....

6

Re: Why does not see an index. Well and generally how to accelerate request... :)

TimofeySin wrote:

Why it does not use

INDEX ` dstchannel ` (` calldate `, ` dst `, ` dstchannel `)

Also will not be. What difference -  the table or an index? Remove from an index calldate or deliver its last - then it will be used.

7

Re: Why does not see an index. Well and generally how to accelerate request... :)

Yes there if to kill the second request of anything plainly does not change.  the union eats nothing, guzzle requests almost identical.
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1, SIMPLE, queue_log, ref, Event Time Data1, Event, 97, const, 200, Using where
1, SIMPLE, cdr, range, calldate dst dstchannel, dst, 242, NULL, 30976, Using where
About  the index did not know, thanks. It became clear why sql selected this index smile, there was a current a question how to accelerate this all.
Me kills that "rows" in  30976, and in a reality produces 240. I as understood at me 200*30976 operations of comparing and because of it and a brake such turn out...

8

Re: Why does not see an index. Well and generally how to accelerate request... :)

TimofeySin wrote:

Me kills that "rows" in  30976, and in a reality produces 240.

There is a result, and there is an estimation of result without obtaining of the result. The error on two orders - is quite possible, especially if statistics is irrelevant.

9

Re: Why does not see an index. Well and generally how to accelerate request... :)

TimofeySin wrote:

Yes there if to kill the second request of anything plainly does not change.  the union eats nothing, guzzle requests almost identical.

1. From the general reasons, please, specify that
What concerns.
2, it is is specific:
>> guzzle requests almost identical
Two requests in an union has different  logic and different
To the logician of request. Most likely and optimization (if it is possible)
Will be different.
3.  1: "LOG left join CDR"
it is necessary to optimize  on the BROAD GULL.
What for an index "Event"?.
How many records give:
select count (1)
from ` queue_log`
WHERE ` queue_log `. ` queuename ` = ' Programmist_queue'
AND (` queue_log `. ` event ` = ' REMOVEMEMBER ')
AND (` queue_log `. ` data1 ` <> ")
AND (` queue_log `. ` time `> ' 2017-10-06 '))
4. -1. What to help the optimizer, it is possible to rewrite
Specifying a complete sample from the BROAD GULL in an explicit form, type:
SELECT
RIGHT (` queue_log `. ` agent `, 4) AS ` the Agent`
,` queue_log `. ` data1 ` AS ` VhodVGruppu`
,` queue_log `. ` time ` AS ` VyhodIzGruppy`
, TIME_TO_SEC (TIMEDIFF (` queue_log `. ` time `, IF ((` queue_log `. ` data1 ` = "), ` queue_log `. ` time `, ` queue_log `. ` data1 `))) AS ` VremjaVGruppe`
,` cdr `. ` uniqueid ` AS ` `
,` cdr `. ` src ` AS ` the Subscriber`
, IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ') AS ` VremjaNachala`
,` cdr `. ` end ` AS ` `
,` cdr `. ` billsec ` AS ` VremjaRazgovora`
, SUBSTR (` cdr `. ` dstchannel`
,(LOCATE (' / ', ` cdr `. ` dstchannel `) + 1), 4) AS ` AgentCDR`
,` cdr `. ` disposition ` AS ` the Status`
FROM
(
select *
from ` queue_log`
WHERE ` queue_log `. ` queuename ` = ' Programmist_queue'
AND (` queue_log `. ` event ` = ' REMOVEMEMBER ')
AND (` queue_log `. ` data1 ` <> ")
AND (` queue_log `. ` time `> ' 2017-10-06 '))
) LOG2
LEFT JOIN ` cdr ` ON (((SUBSTR (` cdr `. ` dstchannel `, 5,4) = RIGHT ( LOG2 . ` agent `, 4))
AND (IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ')> = LOG2 . ` data1 `)
AND (IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ') <= LOG2 . ` time `)
AND (` cdr `. ` dst ` LIKE ' 07 % ')
AND (` cdr `. ` calldate `> ' 2017-10-06 '))))

10

Re: Why does not see an index. Well and generally how to accelerate request... :)

5. Select-1. Sheaf analysis
LEFT JOIN ` cdr ` ON (((SUBSTR (` cdr `. ` dstchannel `, 5,4) = RIGHT (LOG2. ` agent `, 4))
dstchannel - completely falls out of reviewing
To an index for it is applied
Possible improving: new field creation
CDR.AGENT And to fill with its value SUBSTR (` cdr `. ` dstchannel `, 5,4) at .
And to include ( to include!) CDR.AGENT in an index
AND (IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ')> = LOG2. ` data1 `)
AND (IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ') <= LOG2. ` time `)
> <after IFNULL - most likely the index will not be
Possible improving: to replace NULL on ' 0001-01-01 ' on an insertion
And to remove  from request
(a, at you calldate NOT NULL DEFAULT ' 000... ', what for IFNULL????????)
AND (` cdr `. ` dst ` LIKE ' 07 % ')
The question in selectivity of 07 % can be considered in an index,
That gives request:
select count (1) from CDR where ` dst ` LIKE ' 07 %'
If it is less than 200 - it is possible to try index  with DST.
AND ` cdr `. ` calldate `> ' 2017-10-06'
That gives request:
select count (1) from CDR where ` cdr `. ` calldate `> ' 2017-10-06'
At you requests always for the last day? If there is no that
Whether it is possible it will be restricted to requests for ONE day?
If "yes ' on any question above it is possible to add
CDR.CallDateDate And to store DATE, and then to use
"=" In search.

11

Re: Why does not see an index. Well and generally how to accelerate request... :)

6. -1. Reversely to the BROAD GULL - now looked - there too  at a floor
records. I.e. it is necessary to fulfill  a part seriously.
Index EVENT has low cardinality 18.
It is possible to try  time as the SECOND field.
For , separately, it is possible to try time as PEROV (and )
Field in an index.
(https://dev.mysql.com/doc/refman/5.7/en … ation.html)

12

Re: Why does not see an index. Well and generally how to accelerate request... :)

...By the way, without rather all - make
OPTIMIZE TABLES

13

Re: Why does not see an index. Well and generally how to accelerate request... :)

7. Selekt-2.
, at first SEPARATELY it is necessary to consider request on CDR
select *
from CDR
WHERE ` cdr `. ` dst ` LIKE ' 07 %'
AND ` cdr `. ` calldate `> ' 2017-10-06'
AND ` cdr `. ` dstchannel ` <>"
dst and calldate - to consider too most as for Selekt-1
dstchannel here it is not interesting
8. -2 the Sheaf on the BROAD GULL
LEFT JOIN ` queue_log`
ON (((SUBSTR (` cdr `. ` dstchannel `, 5,4) = RIGHT (` queue_log `. ` agent `, 4))
If there is a possibility - add a field with which fill at an insertion
` queue_log `. ` agent2 ` = RIGHT (` queue_log `. ` agent `, 4)
Can it turns out (or not, without warranties) good selective
AND (IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ')> = ` queue_log `. ` data1 `)
AND (IFNULL (` cdr `. ` calldate `, ' 0000-00-00 ') <= ` queue_log `. ` time `)
To remove IFNULL. 1 and Time can be an index part (or a single index)
AND (` queue_log `. ` queuename ` = ' Programmist_queue ')
select count (1) from log where ` queuename ` = ' Programmist_queue '???
AND (` queue_log `. ` event ` = ' REMOVEMEMBER ')
select count (1) from log where ` event ` = ' REMOVEMEMBER '???
AND (` queue_log `. ` data1 ` <> ")
AND (` queue_log `. ` time `> ' 2017-10-06 '))))
.. already there was such cropping in CDR, what for to repeat?

14

Re: Why does not see an index. Well and generally how to accelerate request... :)

...If  it will be boring, it will be possible it is engaged
Others , type of a scrap of old records;
Removal of some fields in the adjacent tables...

15

Re: Why does not see an index. Well and generally how to accelerate request... :)

TimofeySin wrote:

Request:
select *
from cdr
WHERE cdr.calldate> ' 2017-10-06 ' and cdr.dst like ' 0747 ' AND cdr.dstchannel = ' SIP/0747-00001746'
Why it does not use

INDEX ` dstchannel ` (` calldate `, ` dst `, ` dstchannel `)

?
And how to make request faster? Result 1 line.
The index should be for this request
INDEX ` dstchannel ` (` dstchannel `, ` dst `, ` calldate `)
r]

16

Re: Why does not see an index. Well and generally how to accelerate request... :)

javajdbc;
3)  : there in 3 post  is under tables, at  18.
4) to rewrite with nested query , I   to make such, and mysql does not resolve the enclosed.
5)
Ifnull in the second request and truth it is possible to remove, and in the first it is impossible, it because of connection appears.
Concerning a condition ' 2017-10-06 ', it is simple then changes have been entered into tables and before this date the data on another was filled.
Index and so it is applied only dst.
Log it is quickly fulfilled at the expense of that
queuename ` = ' Programmist_queue'
AND (` queue_log `. ` event ` = ' REMOVEMEMBER ') appeared recently (from 6 numbers)
More shortly understood that it is necessary to start  to write, that all was beautiful in the table.

17

Re: Why does not see an index. Well and generally how to accelerate request... :)

TimofeySin wrote:

Ifnull in the second request and truth it is possible to remove, and in the first it is impossible, it because of connection appears.

IFNULL (calldate...) it is possible and it is necessary to remove in a sheaf (ON section) in both requests.
IFNULL it is necessary to leave in SELECT sections in the first request

18

Re: Why does not see an index. Well and generally how to accelerate request... :)

TimofeySin wrote:

3)  : there in 3 post  is under tables, at  18.

, 18 - it is not enough on 500 as it is already told above, it is necessary
with request about the BROAD GULL separately.
Probably to add  fields in an index

19

Re: Why does not see an index. Well and generally how to accelerate request... :)

TimofeySin wrote:

4) to rewrite with nested query , I   to make such, and mysql does not resolve the enclosed.

At first is a restriction manages - it is possible to make  inside
Secondly - I extremely do not recommend to do  in  a situation. Type:
select * from view where c = 123
The matter is that in not most current releases muskl
concept "predicate push" -  earlier v-juha
It was calculated  (long!) and then to it it was applied where.
In Orakle for a long time already and only more recently MySQL learned
To wear out conditions where inside  with possibility
Usages of indexes.
However it is unstable,  and than more difficult v-jushka
The probability of reset on the full search without indexes there is more.

20

Re: Why does not see an index. Well and generally how to accelerate request... :)

TimofeySin wrote:

Log it is quickly fulfilled at the expense of that
queuename ` = ' Programmist_queue'
AND (` queue_log `. ` event ` = ' REMOVEMEMBER ') appeared recently (from 6 numbers)

In some days-weeks it will be typed more than such records
And  a brake... Do not neglect
Requests to the BROAD GULL - in both cases - and where in the first
And sheaves (ON) in the second - and it can be
indexes.

21

Re: Why does not see an index. Well and generally how to accelerate request... :)

TimofeySin wrote:

understood More shortly that it is necessary to start  to write, that all was beautiful in the table.

Yes, additional calculated fields (type) Agent2
Will be useful. Except triggers it can be made
On  calculating values on the client or
SQL functions directly in the text .
Personally I would use  here in the last queue...