How can I optimize sql for price and timestamp fields?

Question

checking explain (mysqlnd 8.1):

    explain analyze       SELECT *
        FROM `ts_products`  WHERE `ts_products`.`creator_id` = '2' AND 
`ts_products`.`title` like '%no%'     AND 

`ts_products`.`status` = 'A'     AND 
ts_products.published_at >= '2022-04-01' AND 
ts_products.published_at < '2022-04-21' AND 
ts_products.sale_price > '261' 
ORDER BY `sale_price` asc

on table :

CREATE TABLE `ts_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,
   `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 `ts_products_slug_index` (`slug`),
   UNIQUE KEY `ts_products_sku_index` (`sku`),
   UNIQUE KEY `ts_products_creator_id_3fields_index` (`creator_id`,`sale_price`,`status`,`title`,`published_at`),
   KEY `ts_products_status_title_regular_price_published_at_index` (`status`,`title`,`regular_price`,`published_at`),
   KEY `ts_products_status_5fields_index` (`status`,`in_stock`,`has_discount_price`,`is_featured`,`title`,`published_at`,`stock_qty`,`sale_price`),
   CONSTRAINT `ts_products_creator_id_foreign` FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=801 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I see that ts_products_creator_id_3fields_index index is selected and seems good, but looking at output :

-> Index range scan on ts_products using ts_products_creator_id_3fields_index over (creator_id = 2 AND 261.00 < sale_price), with index condition: ((ts_products.creator_id = 2) and (ts_products.title like '%no%') and (ts_products.`status` = 'A') and (ts_products.published_at >= TIMESTAMP'2022-04-01 00:00:00') and (ts_products.published_at < TIMESTAMP'2022-04-21 00:00:00') and (ts_products.sale_price > 261.00))  (cost=20.06 rows=44) (actual time=0.070..0.179 rows=2 loops=1)

I wonder if how signs ">=" / "<" / ">" work where. AS far as I know only '=' works for indices. If there is sense to add sale_price and published_at in ts_products_creator_id_3fields_index index ?

How can I optimize this request ?

Thanks!

An index basically sorts the rows for quick access. You're right that this makes it easy to find exact values with '=', but it also makes it easy to find those rows that are greater or smaller than a value. This will be simply all the rows above or below a value in the sorted rows. Does this explanation help you to understand how an index can aid doing '>=', '<' or '>'? So yes, having `sale_price` and `published_at` in `ts_products_creator_id_3fields_index` does speed up your query. — KIKO Software, May 27 '22 at 06:31
The term "sorting" is a bit a simplification of what an index does. Yes, it kind of "sorts", but what it actually does is making finding rows easier by [creating a search tree](https://severalnines.com/database-blog/overview-mysql-database-indexing). — KIKO Software, May 27 '22 at 06:39
If there is a sence to reorder fields in ts_products_creator_id_3fields_index key ? I mean maybe to set sale_price and published_at(which have no “=” compare) at the end of fields list? — mstdmstd, May 27 '22 at 06:42
This is explained here: [Does Order of Fields of Multi-Column Index in MySQL Matter](https://stackoverflow.com/questions/24315151/does-order-of-fields-of-multi-column-index-in-mysql-matter). The order of columns in a multi-column index definitely matters. — KIKO Software, May 27 '22 at 06:45
The slowest condition in your query is definitely `title LIKE '%no%'`. Only characters before the first wild card can be used to speed up searches during tree traversal of the index. Since your wild card is the first character there are no characters that can be used. For that reason you might want to remove the `title` column from the `ts_products_creator_id_3fields_index` index, if you created it specifically for this query. — KIKO Software, May 27 '22 at 06:55
But seems I see title in analyze ((ts_products.creator_id = 2) and (ts_products.title like '%no%') and (ts_products.`status` = 'A') . Does it say that title is used ? — mstdmstd, May 27 '22 at 07:03
It's difficult for me to see what your `EXPLAIN` returned exactly, but even if MySQL tried to use `title` it cannot help to speed up the query. This is because the `%` is at the start of the pattern. — KIKO Software, May 27 '22 at 07:14
Thanks a lot. Are there some additive decisions with title LIKE '%no%' ? — mstdmstd, May 27 '22 at 10:03
You're welcome. Sorry, I'm not sure I understand your question. There's nothing you can do to speed up searching for `'%no%'` in the `title` column. All remaining rows, after the other conditions have been applied, have to be inspected. Just make sure there aren't a huge amount of rows left to search through, for instance by restricting the `published_at` range to a maximum of 1 month or 1 year. — KIKO Software, May 27 '22 at 10:21
You could search for "words" in a string by using a `FULLTEXT` index. But "no" is not likely to be usable for multiple reasons. — Rick James, May 28 '22 at 00:24

score 1 · Answer 1 · answered May 28 '22 at 00:21

This might help:

INDEX(creator_id, status, sale_price)

There is one column that is unindexable (title like '%no%') because of the leading wildcard, and 2 ranges: published_at and sale_price. I picked the latter because it might be useful both in WHERE and in ORDER BY.

Please provide the EXPLAIN.

As discussed in Index Cookbook , columns tested by = should come first in a composite index, followed by one 'range' column.

How can I optimize sql for price and timestamp fields?

1 Answers1