Query Optimization Needed
Hi Friends,
Need help on this query, i need to optimize it
SELECT DISTINCT `userdeviceinfo`.`deviceid` FROM `userdeviceinfo`
WHERE (`userdeviceinfo`.`created_on` < '2015-02-10 18:30:00' AND NOT (`userdeviceinfo`.`user_id` IS NULL))
--------------------------------------------------------------------------
This query is taking 0.80- 0.90 seconds and return resultset with approx. 165000 rows. I want to optimize it more in terms of execution time.
Here the thing which i already do with this :
1- Query was not using index which already exist on user_id field, i used force index option in query. Now Query is using the index and row exmained is
reduced to 205000, before use force index, it was approx 410000. But i am not gain any benefit in query execution time. it still taking 0.8-0.9 seconds.
Why ?
2- i have used composite/covering index on every searching field in this quey, but this is not help me anymore?
3- Why query is not using index by default, i am using mysql server 5.5.38
-------------------------------------------------------------------------------
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`)
) ENGINE=InnoDB AUTO_INCREMENT=427210 DEFAULT CHARSET=latin1
-------------------------------------------------------------------------------
Regards,
Devrishi