I have products table with several indexes and products_published_at_category_id_regular_price_status_index with 4 fields
CREATE TABLE `products` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`creator_id` bigint unsigned NOT NULL,
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`status` enum('D','P','A','I') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'D' COMMENT ' D => Draft, P=>Pending Review, A=>Active, I=>Inactive',
`slug` varchar(260) COLLATE utf8mb4_unicode_ci NOT NULL,
`sku` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`category_id` smallint unsigned NOT NULL,
`city_id` smallint unsigned NOT NULL,
`regular_price` decimal(9,2) DEFAULT NULL,
`sale_price` decimal(9,2) DEFAULT NULL,
`in_stock` tinyint(1) NOT NULL DEFAULT '0',
`stock_qty` mediumint unsigned NOT NULL DEFAULT '0',
`has_discount_price` tinyint(1) NOT NULL DEFAULT '0',
`is_featured` tinyint(1) NOT NULL DEFAULT '0',
`short_description` mediumtext COLLATE utf8mb4_unicode_ci,
`description` longtext COLLATE utf8mb4_unicode_ci,
`published_at` datetime DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `products_creator_id_title_index` (`creator_id`,`title`),
UNIQUE KEY `products_slug_unique` (`slug`),
KEY `products_category_id_foreign` (`category_id`),
KEY `products_city_id_foreign` (`city_id`),
KEY `products_published_at_category_id_regular_price_status_index` (`published_at`,`category_id`,`regular_price`,`status`),
KEY `products_published_category_sale_price_status_discount_index` (`published_at`,`category_id`,`sale_price`,`status`,`has_discount_price`),
KEY `products_title_status_index` (`title`,`status`),
KEY `products_published_at_title_status_index` (`published_at`,`title`,`status`),
CONSTRAINT `products_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT `products_city_id_foreign` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT `products_creator_id_foreign` FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
But running sql with these 4 parameters in sql statement and looking at explain
explain format =json SELECT *
FROM `products`
WHERE products.published_at >'2022-04-01'
AND products.published_at <='2022-04-21'
AND `products`.`category_id` = '6'
AND `products`.`status` = 'A'
ORDER BY `regular_price` asc
I see "products_category_id_foreign" in "key" value, but not "products_published_at_category_id_regular_price_status_index" as I expected :
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "107.90"
},
"ordering_operation": {
"using_filesort": true,
"table": {
"table_name": "products",
"access_type": "ref",
"possible_keys": [
"products_category_id_foreign",
"products_published_at_category_id_regular_price_status_index",
"products_published_category_sale_price_status_discount_index",
"products_published_at_title_status_index"
],
"key": "products_category_id_foreign",
"used_key_parts": [
"category_id"
],
"key_length": "2",
"ref": [
"const"
],
"rows_examined_per_scan": 122,
"rows_produced_per_join": 4,
"filtered": "3.79",
"cost_info": {
"read_cost": "95.70",
"eval_cost": "0.46",
"prefix_cost": "107.90",
"data_read_per_join": "11K"
},
"used_columns": [
"id",
"creator_id",
"title",
"status",
"slug",
"sku",
"category_id",
"city_id",
"regular_price",
"sale_price",
"in_stock",
"stock_qty",
"has_discount_price",
"is_featured",
"short_description",
"description",
"published_at",
"created_at",
"updated_at"
],
"attached_condition": "((`BiCurrencies`.`products`.`published_at` > TIMESTAMP'2022-04-01 00:00:00') and (`BiCurrencies`.`products`.`published_at` <= TIMESTAMP'2022-04-21 00:00:00') and (`BiCurrencies`.`products`.`status` = 'A'))"
}
}
}
}
And
"used_key_parts= [ "category_id" ],
...
So explain seems not good
Also I see that values in "query_cost" and "cost_info" are high. I suppose these are some internal mysql values and they must be as small as possible. I tried to run commands :
OPTIMIZE TABLE products;
ANALYZE TABLE products;
before running my sql statement - but results were the same...
Version mysqlnd 8.1.4 under kubuntu 20
UPDATED PART 1:
Thanks!
- From my prior expierence when I create index on several fields I followed next rule: at left side must be fields with biggest number of set. In my case sure that is published_at.
At right side must be status (enum of 4 possible values)
category_id - has about 10 possible values.
field published_at can also be used in “=” comparison, alos in “>=”/"<". To create different indexes for different cases? 2) trying to delete key products_category_id_foreign with command :
alter TABLE `products` drop key products_category_id_foreign;
I got error :
SQL Error [1553] [HY000]: Cannot drop index 'products_category_id_foreign': needed in a foreign key constraint
This is reference to other table...
- Can In in mysql request set preferable index? Actually I use laravel eloquent library?
UPDATED PART 2 :
I moved published_at into the end of my filter and after database rebuild the index I need is applied. As for “an old myth". Yes, looks like that is true. Before accepyting your answer, could you please give a ref to working rules as for mysql 5.x/8.x ? I have read some docs(including official mysql docs), but I did not read such rule.
As for the error message, when I try to remove products_category_id_foreign : I use laravel migration with eloquent and products_category_id_foreign is created automatically with command :
$table->unsignedSmallInteger('category_id')->unsigned(); $table->foreign('category_id')->references('id')->on('categories')->onUpdate('RESTRICT')->onDelete('CASCADE');
So I do not see how can I remove this index. But seems that is not issue as I managed to use valid index.
Thanks!
