Categories
Database Development

MySQL using different index when direct index exist on columns

I have following table (stripped off other columns, indexes for simplicity) CREATE TABLE `issues` ( `id` int(11) NOT NULL AUTO_INCREMENT, `book_id` int(11) NOT NULL, `number` int(11) DEFAULT ‘0’, `title` varbinary(1024) DEFAULT NULL, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_issues_on_book_id_and_number` (`book_id`,`number`), KEY `index_issues_on_book_id_and_updated_at` (`book_id`,`updated_at`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 […]

Categories
Development

Active record lookup intermittently slow inspite of index being present on the column in where clause

I have a simple new API endpoint, which involves querying my newly setup and populated table – inventory_products. The schema of inventory_products table is : CREATE TABLE `inventory_products` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `inventory_product_id` binary(16) DEFAULT NULL, `product_id` binary(16) DEFAULT NULL, `status` enum(‘ACTIVE’,’INACTIVE’,’DELETED’) DEFAULT ‘ACTIVE’, `priority` int(11) DEFAULT ‘0’, `inventory_service_id` tinyint(3) DEFAULT NULL, […]