Left join optimization- query is very slow
Hi Friends,
I have two tables view_tracker and userdeviceinfo, i need to findout distinct imei numbers along with some other fields from view_tracker table which are exist and not exist in userdeviceinfo tables, if the record exist then it gives me max(app_version) other wise Null.So i write this query.
-------------------------------------------------------------------------------
select v.`imei`, v.`device_id`,v.`user_gmail_id`,max(u.`app_version`) from view_tracker v force index (kkk) left join userdeviceinfo u on v.`imei`=u.imei
where v.tag_name in ('Gmobipush_RS_MMX_1_4_9_Test','Gmobipush_RS_iBall_1_4_9_Test',
'Gmobipush_RS_Intex_1_4_9_Test','Gmobipush_RS_MMX_1_4_9','Gmobipush_RS_iBall_1_4_9','Gmobipush_RS_Intex_1_4_9') group by v.`imei`
-------------------------------------------------------------------------------
Query gives me perfect result but it is not execute even in 24-36 hours. can some one help me to optimize the query.
Records in view_tracker table are 1052081 and in userdeviceinfo are 529736. i have used index hints and without index hints but result is same, not execute in 24 hours. i am using mysql 5.5.38 and both tables are innodb, innodb buffer pool size is 1 GB and innodb trx commit variable is set 2 for higher performance.
-------------------------------------------------------------------------------
show create tables are :
CREATE TABLE `userdeviceinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`imei` varchar(100) DEFAULT NULL,
`deviceid` varchar(100) DEFAULT NULL,
`osversion` varchar(100) DEFAULT NULL,
`operator` varchar(100) DEFAULT NULL,
`msisdn` varchar(100) DEFAULT NULL,
`circle` varchar(50) DEFAULT NULL,
`modal` varchar(100) DEFAULT NULL,
`simoperator` varchar(100) DEFAULT NULL,
`simcountrycode` varchar(100) DEFAULT NULL,
`phonetype` varchar(100) DEFAULT NULL,
`manufacturer` varchar(100) DEFAULT NULL,
`networktype` varchar(100) DEFAULT NULL,
`gcm_reg_id` varchar(4096) DEFAULT NULL,
`oem` varchar(100) DEFAULT NULL,
`device_type` varchar(16) DEFAULT NULL,
`app_version` varchar(100) DEFAULT NULL,
`screen_width` varchar(10) DEFAULT NULL,
`screen_height` varchar(10) DEFAULT NULL,
`download_source` int(11) NOT NULL DEFAULT '0',
`is_updated` tinyint(1) NOT NULL,
`created_on` datetime NOT NULL,
`updated_on` datetime NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `userdeviceinfo_fbfc09f1` (`user_id`),
KEY `ind_userdeviceinfo_imei` (`imei`),
KEY `Index_deviceid` (`deviceid`),
KEY `ggg` (`app_version`)
) ENGINE=InnoDB AUTO_INCREMENT=529955 DEFAULT CHARSET=latin1
CREATE TABLE `view_tracker` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(50) DEFAULT NULL,
`device_id` varchar(50) NOT NULL,
`from_screen_id` int(11) DEFAULT NULL,
`current_screen_id` int(11) NOT NULL,
`clicked_on` varchar(50) DEFAULT NULL,
`datetime` datetime NOT NULL,
`imei` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
`book_mag_id` int(10) DEFAULT NULL,
`book_type` int(2) DEFAULT NULL,
`tag_name` varchar(100) DEFAULT NULL,
`model_num` varchar(25) DEFAULT NULL,
`book_name` varchar(50) DEFAULT NULL,
`token_id` int(11) DEFAULT NULL,
`orignalprice` varchar(50) DEFAULT NULL,
`code` varchar(50) DEFAULT NULL,
`trans_token` varchar(50) DEFAULT NULL,
`category_name` varchar(50) DEFAULT NULL,
`download_status` varchar(50) DEFAULT NULL,
`search_key` varchar(50) DEFAULT NULL,
`filter` varchar(50) DEFAULT NULL,
`user_gmail_id` varchar(100) DEFAULT NULL,
`first_open_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `view_tracker_301bcee0` (`current_screen_id`),
KEY `token_id_refs_id_666280b3` (`token_id`),
KEY `Index_datetime` (`datetime`),
KEY `Index_tagname` (`tag_name`),
KEY `kkk` (`imei`),
CONSTRAINT `current_screen_id_refs_id_dee6b627` FOREIGN KEY (`current_screen_id`) REFERENCES `screen_master` (`id`),
CONSTRAINT `token_id_refs_id_666280b3` FOREIGN KEY (`token_id`) REFERENCES `transactionsession` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15892049 DEFAULT CHARSET=latin1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Here is my execution plan
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | v | index | NULL | kkk | 153 | NULL | 1 | Using where |
| 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 542552 | |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
2 rows in set (0.00 sec)
Regards,
Devrishi