MySQL SELECT runs forever when join is changed

Question

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 change the straight join to a 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 query runs forever and uses ~100% of the server's CPU.

I ran explain on both queries and the first hits the event_time index on vicidial_agent_log, while the second ignores all indexes. There is an index on call_log.uniqueid.

vicidial_agent_log contains ~41,000 rows, call_log contains ~43,000.

So my question is - why is MySQL not hitting the indexes I've defined, is there any way to force it to do so, and if not, how can I make this query run at an acceptable speed?

edit

Full 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;

Answer 1

You can use FORCE INDEX

Answer 2

I imagine the outer join is forcing a table scan because it needs to include all the records that match and supply nulls for the ones that don't.

mck89's solution may very well work, though I've never had a reason to use it...I'm curious how this will turn out.

Answer 3

The first can use the index because in an inner join you are filtering the result set of the join based on the value of the column (Event_Time) that the index is based on...

In the second query, where you are using an outer join, you are NOT filtering the output, so it needs to include all records in the result set regardless of the value of event_time, so it has to do a complete table scan...

Answer 4

Using functions in JOIN or WHERE clauses will always cause havoc with indexes. Example:

DATE_ADD('1970-01-01 02:00:00', INTERVAL clg.uniqueid SECOND)

The database is using the uniqueid index to lookup the values for the conversion, not the comparison to the event_time column in your case. If this were Oracle with PLW errors turned on, you'd be notified about potentially converting away from the data type.

Such situations should always be handled before the comparison, which means using an inline view to perform the conversion and then join to that result 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

Answer 5

When you use LEFT JOIN, the LEFT table is always leading in MySQL.

In your initial query, MySQL could choose which table to make leading, and it chose clg.

Now it cannot choose, and this condition: date_add('1970-01-01 02:00:00', interval clg.uniqueid second) is not sargable.

There is no index on date_add('1970-01-01 02:00:00', interval clg.uniqueid second) which MySQL could use to locate the value of val.event_time.

Rewrite your query as 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