Search code examples
mysqlruby-on-railsmysql-slow-query-log

Clear MYSQL Slow Query LOG - Rails

The following throws slow query log.

APN::Notification.
    select('apn_notifications.*, devices.device_uid').
    joins('INNER JOIN apn_devices ON (apn_notifications.device_id = apn_devices.id) INNER JOIN devices ON (apn_devices.device_id = devices.id)').
    where(['apn_notifications.sent_at IS NULL AND apn_notifications.badge > 0 AND devices.customer_id = ? AND devices.device_type IN (?)', customer.id, Object::Device.platform_device_types('ios')])

The output of EXPLAIN

EXPLAIN for: SELECT apn_notifications.*, devices.device_uid FROM `apn_notifications` INNER JOIN apn_devices ON (apn_notifications.device_id = apn_devices.id) INNER JOIN devices ON (apn_devices.device_id = devices.id) WHERE (apn_notifications.disabled_at IS NULL) AND (apn_notifications.sent_at IS NULL AND apn_notifications.badge > 0 AND devices.customer_id = 1 AND devices.device_type IN ('iphone4','ipad','iphone3'))

The Output of 'show create table apn_notifications'

| apn_notifications | CREATE TABLE `apn_notifications` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`device_id` int(11) DEFAULT NULL,
`errors_nb` int(11) DEFAULT NULL,
`device_language` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`sound` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`alert` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`badge` int(11) DEFAULT NULL,
`custom_properties` text COLLATE utf8_unicode_ci,
`sent_at` datetime DEFAULT NULL,
`disabled_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_apn_notifications_on_device_id` (`device_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12984412 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

the apn_notifications table has 1.5 million records. So if I try to add index, it is taking much longer time. What is the best way to remove this from slow query?

Also From Mysql 5.6, adding index will not result in any downtime. Am I right?

Solution

  • I added the following indexes and that reduced lots of time.

     add_index :apn_notifications, :sent_at
 add_index :apn_notifications, :badge

    NOTE: Already indexes were added for foreign keys