MySQL SELECT runs forever when the connection changes
I have the following SQL code:
select val.PersonNo,
val.event_time,
clg.number_dialed
from vicidial_agent_log val
join
call_log clg on date_add('1970-01-01 02:00:00', interval clg.uniqueid second) = val.event_time
order by val.event_time desc
limit 100;
which executes and returns rows in less than 1 second. However, if I changed the direct connection to left outer
:
select val.PersonNo,
val.event_time,
clg.number_dialed
from vicidial_agent_log val
left outer join
call_log clg on date_add('1970-01-01 02:00:00', interval clg.uniqueid second) = val.event_time
order by val.event_time desc
limit 100;
the request runs forever and uses ~ 100% of the server CPU.
I ran explain
on both queries and the first one hit the index event_time
on vicidial_agent_log
, and the second one ignored all indexes. There call_log.uniqueid
is an index.
vicidial_agent_log
contains ~ 41,000 lines, call_log
contains ~ 43,000.
So my question is, why is MySQL not hitting the indexes I have defined, is there a way to get it to do it, and if not, how can I get this query to run at an acceptable speed?
change
Complete solution:
select val.PersonNo,
val.event_time,
cl.number_dialed
from vicidial_agent_log val
left outer join
(select date_add('1970-01-01 02:00:00', interval clg.uniqueid second) as 'converted_date',
number_dialed
from call_log clg) cl ON cl.converted_date = val.event_time
order by val.event_time desc
limit 100;
source to share
Using functions in a JOIN or WHERE clause will always lead to indices chaos. Example:
DATE_ADD('1970-01-01 02:00:00', INTERVAL clg.uniqueid SECOND)
The database uses the unique ID to look up the values to convert, not compare against the event_time column in your case. If it was Oracle with PLW errors, you would be notified of a potential conversion from datatype.
Such situations should always be done before comparison, which means using the inline representation to perform the conversion and then append the result to that column. IE:
JOIN (SELECT DATE_ADD('1970-01-01 02:00:00', INTERVAL clg.uniqueid SECOND) 'converted_date'
FROM CALL_LOG clg) cl ON cl.converted_date = val.event_time
source to share
When you use LEFT JOIN
, the table LEFT
always leads to MySQL
.
In the original query, he MySQL
can choose which table to make the leading, and selected clg
.
Now he cannot choose, and this condition: date_add('1970-01-01 02:00:00', interval clg.uniqueid second)
does not give in.
There is date_add('1970-01-01 02:00:00', interval clg.uniqueid second)
no index that MySQL
could be used to determine the value val.event_time
.
Rewrite your query like this:
SELECT val.PersonNo,
val.event_time,
clg.number_dialed
FROM vicidial_agent_log val
LEFT OUTER JOIN
call_log clg
ON clg.uniqueid = UNIX_TIMESTAMP(val.event_time) - 7200
ORDER BY
val.event_time desc
LIMIT 100
source to share
The former can use an index because in an inner join you are filtering the result set of the join based on the column value (Event_Time) where the index is based on ...
In the second query where you are using outer join you are NOT filtering the output, so it must include all records in the result set regardless of the event_time value, so it must do a full table scan ...
source to share