How to optimized mysql query having large dataset

Question

I have two tables in MySql with the following schema,

CREATE TABLE `open_log` (
 `delivery_id` varchar(30) DEFAULT NULL,
 `email_id` varchar(50) DEFAULT NULL,
 `email_activity` varchar(30) DEFAULT NULL,
 `click_url` text,
 `email_code` varchar(30) DEFAULT NULL,
 `on_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `sent_log` (
 `email_id` varchar(50) DEFAULT NULL,
 `delivery_id` varchar(50) DEFAULT NULL,
 `email_code` varchar(50) DEFAULT NULL,
 `delivery_status` varchar(50) DEFAULT NULL,
 `tries` int(11) DEFAULT NULL,
 `creation_ts` varchar(50) DEFAULT NULL,
 `creation_dt` varchar(50) DEFAULT NULL,
 `on_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The email_id and delivery_id columns in both tables make up a unique key.

The open_log table have 2.5 million records where as sent_log table has 0.25 million records.

I want to filter out the records from open log table based on the unique key (email_id and delivery_id).

I'm writing the following query.

SELECT * FROM open_log WHERE CONCAT(email_id,'^',delivery_id) IN ( SELECT DISTINCT CONCAT(email_id,'^',delivery_id) FROM sent_log )

The problem is the query is taking too much time to execute. I've waited for an hour for the query completion but didn't succeed.

I've tried to make the email_id and delivery_id as composite key but that didn't help.

Kindly, suggest what I can do to make it fast since, I have the big data size in the tables.

Thanks, Faisal Nasir

Answer 1

• First, if email_id and delivery_id together are a unique key, please add a primary -composite- key on both tables for (email_id, delivery_id)

• Second, the concat is not necessary and will prevent the previous key from being used. Try:

  SELECT ol.* 
  FROM open_log ol 
  JOIN sent_log sl 
  ON (ol.email_id, ol.delivery_id) = (sl.email_id, sl.delivery_id)
  

• ypercubeᵀᴹ: I like this syntax but the ON ol.email_id = sl.email_id AND ol.delivery_id = sl.delivery_id results in more optimal code than ON (ol.email_id, ol.delivery_id) = (sl.email_id, sl.delivery_id) in some versions.
• jynus: @ypercube, not true, MySQL has a bug for certain uses of that syntax (I think it was fixed in 5.7?), let's speak clearly. Not in OPs use.
• ypercubeᵀᴹ: What if the OP uses 5.1 or 5.0? Anyway, I'm not really sure, have to check it out. It may not affect = but only >= and IN comparisons.
• jynus: @ypercube it will work pastebin.com/XwgiW7j4
• jynus: @ypercube, exactly the bug I mention is the (a, b) IN ((c, d), (e, f)), where indexes are not used- but return correct results.