How to speed up a SELECT query which displays the price in the date range?

Tell me, how can I speed up the query
SELECT DATE_FORMAT(date,'%d.%m.%Y') as date1, `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` WHERE `id_product` = 235408123 ORDER BY `date`

The query runs 13 seconds
It displays the following data :
"date1" "date" "prevDate" "price_product"
"04.02.2019" "2019-02-04" "2019-02-03" "2870"
"07.02.2019" "2019-02-07" "2019-02-06" "2870"
"15.02.2019" "2019-02-15" "2019-02-14" "2857"
"23.02.2019" "2019-02-23" "2019-02-22" "2856"
"07.03.2019" "2019-03-07" "2019-03-06" "3359"
"08.03.2019" "2019-03-08" "2019-03-07" "3358"
"15.03.2019" "2019-03-15" "2019-03-14" "3356"
"16.03.2019" "2019-03-16" "2019-03-15" "3034"
"24.03.2019" "2019-03-24" "2019-03-23" "3036"
"29.03.2019" "2019-03-29" "2019-03-28" "3358"
"31.03.2019" "2019-03-31" "2019-03-30" "3356"
"01.04.2019" "2019-04-01" "2019-03-31" "3354"
"12.04.2019" "2019-04-12" "2019-04-11" "3348"
"13.04.2019" "2019-04-13" "2019-04-12" "3162"
"22.04.2019" "2019-04-22" "2019-04-21" "3156"
"30.04.2019" "2019-04-30" "2019-04-29" "3159"
"07.05.2019" "2019-05-07" "2019-05-06" "3162"
"24.05.2019" "2019-05-24" "2019-05-23" "2941"
"03.06.2019" "2019-06-03" "2019-06-02" "3162"

In the table about 12 million records and they grow
The indices stand for id_product and date
CREATE TABLE `prices` (
 `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
 `id_product` VARCHAR(100) NOT NULL DEFAULT '0',
 `price_product` INT(11) NULL DEFAULT NULL,
 `date` DATE NULL DEFAULT NULL,
 PRIMARY KEY (`id`),
 INDEX `id_product` (`id_product`),
 INDEX `id_date` (`id_product`, `date`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT

EXPLAIN returns the following data
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" "prices" "ALL" "id_product,id_date" \N \N \N "13132125" "Using where; Using filesort"

The problem is in this query, if it is clear, then the page opens instantly.
March 23rd 20 at 19:07
3 answers
March 23rd 20 at 19:09
Well make permanent MySQL to use an index:
SELECT DATE_FORMAT(date,'%d.%m.%Y') as date1, `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` FORCE INDEX (`id_product`) WHERE `id_product` = 235408123 ORDER BY `date`

If the version of MySQL will not allow to comply with such request - change FORCE INDEX to USE the INDEX.
SELECT `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` FORCE INDEX (`id_product`) WHERE `id_product` = 235408123 ORDER BY `date`;

/* Affected rows: 0 Found lines: 19 Warnings: 0 Duration for 1 query: 11,279 sec. */
SELECT `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` USE INDEX (`id_product`) WHERE `id_product` = 235408123 ORDER BY `date`;

/* Affected rows: 0 Found lines: 19 Warnings: 0 Duration for 1 query: 12,449 sec. */ - Eladio.Veum commented on March 23rd 20 at 19:12
And what was the EXPLAIN of the first query? - Eryn.Beer26 commented on March 23rd 20 at 19:15
@Eryn.Beer26,
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" "prices" "ALL" "id_product" \N \N \N "12975903" "Using where; Using filesort" - Eladio.Veum commented on March 23rd 20 at 19:18
And you can change the index `id_date`, adding `price_product`, well, to change the FORCE INDEX on the `id_date`. Then all the data can come straight from the index, without reading the base. - Eryn.Beer26 commented on March 23rd 20 at 19:21
\N \N \N says that the index is not used at all.

Change the type `id_product` and a BIGINT is clearly there you have a number less than two in the 63-degree...
Or is there a stored string of letters? - Eryn.Beer26 commented on March 23rd 20 at 19:24
@Eryn.Beer26, I'm trying to replace the indexes and change the type to BIGINT but apparently it's not fast. Table hanging 4 hours using ALTER TABLE - Eladio.Veum commented on March 23rd 20 at 19:27
Still hangs? - Eryn.Beer26 commented on March 23rd 20 at 19:30
@Eryn.Beer26, Changed to BIGINT, but now for some reason the main request otkazyvaetsya to run. Just hangs, even EXPLOIN do not want. No errors, just hangs. In the code nothing changed, just changed the type id_product from VARCHAR to BIGINT - Eladio.Veum commented on March 23rd 20 at 19:33
Make a correction table can feel better.
REPAIR TABLE `prices`; - Eryn.Beer26 commented on March 23rd 20 at 19:36
@Eryn.Beer26does Not work. Says
The storage engine for the table doesn't support repair - Eladio.Veum commented on March 23rd 20 at 19:39
@Eladio.Veum, then
CHECK TABLE `prices`;
At least learn table is operational at all... - Eryn.Beer26 commented on March 23rd 20 at 19:42
@Eryn.Beer26, Like all the rules with a table. The answer is
"Table" "Op" "Msg_type" "Msg_text"
"test.prices" "check" "status" "OK"
But the query hangs and all..... - Eladio.Veum commented on March 23rd 20 at 19:45
@Eladio.Veum, so you do a SELECT query in the absence of an index, and it hangs? - Eryn.Beer26 commented on March 23rd 20 at 19:48
@Eryn.Beer26, to be honest I don't understand what's going on.
Request
SELECT dtc, id_product, vendor, vendorCode, description, image_product, link_product, name, price_product,dtp
 FROM (
 SELECT t2.dtc, t2.id_product, n.name, prc.price_product, t2.dtp n.vendor, n.vendorCode, n.description, n.image_product, n.link_product
 FROM (
 SELECT t1.AS dtc dt, t1.id_product, MAX(p.`date`) AS dtp
 FROM ( 
 SELECT p.dt products.id_product
 Pcalendar FROM p (
 SELECT distinct p.id_product
 FROM prices_test p
 WHERE id_product = 837623465
 ORDER BY id_product
 ) products
 WHERE p.dt between p.dt AND CURDATE()
 ) t1
 Prices_test p LEFT JOIN ON (t1.dt >= p.`date`) AND (t1.id_product = p.id_product)
 GROUP BY dtc, id_product
 ORDER BY dtc, id_product
 ) t2
 LEFT JOIN prices_test prc ON (t2.dtp = prc.`date`) AND (t2.id_product = prc.id_product)
 N LEFT JOIN products ON (t2.id_product = n.id_product)
 ORDER BY dtc, id_product
 ) t3 
 WHERE the dtc between dtp AND CURDATE()
 ORDER BY DESC dtc, id_product

Before I changed the type id_product from VARCHAR to BIGINT everything worked. A query is executed. Now just hanging out.
Now I tried to create a test table with 1000 rows
CREATE TABLE `prices` (
 `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
 `id_product` BIGINT(21) NOT NULL DEFAULT '0',
 `price_product` INT(11) NULL DEFAULT NULL,
 `date` DATE NULL DEFAULT NULL,
 PRIMARY KEY (`id`),
 INDEX `id_product` (`id_product`),
 INDEX `id_date` (`id_product`, `date`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT

Still hanging. Now change the value of `id_product` from BIGINT to VARCHAR and all again earned - Eladio.Veum commented on March 23rd 20 at 19:51
So, immediately, not even understanding that in this query, why, here it is:
WHERE p.dt between p.dt AND CURDATE()
what is it like? "min <= X <= max", where min is always equal to X? - Eryn.Beer26 commented on March 23rd 20 at 19:54
@Eryn.Beer26, In fact, this line is not necessary. But its presence or absence does not affect what type VARCHAR it works, but INT or BIGINT immediately enters it into a stupor. I don't know why - Eladio.Veum commented on March 23rd 20 at 19:57
@Eryn.Beer26, the Essence of this request is that it complements the dates. Because it is written in the base price only if different from last price. But I need to display prices every day. What actually makes this request - Eladio.Veum commented on March 23rd 20 at 20:00
@Eladio.Veum, well, let's look at one craaaazy query:
SELECT p.dt products.id_product
FROM pcalendar p,
(
 SELECT DISTINCT p.id_product
 FROM prices_test p
 WHERE id_product = 837623465
 ORDER BY id_product
) products
WHERE p.dt between p.dt AND CURDATE()


So from the database `prices_test` take all records WHERE id_product=..., through DISTINCT the duplicates are discarded, it turns out one record id_product=..., it is the result of the inner query.

Further, from the `pcalendar` through meaningless WHERE p.dt between p.dt AND CURDATE() is taken for ALL RECORDS in table `pcalendar`, each WITHOUT any conditions stupidly added id_product=..., and this is the result of the outer query.

We have an array of ALL (even repetitive) pcalendar.dt with stupid attached to them id_product=... And how many entries you have in `pcalendar`?
That's good, right? And then... No, I can't, I won't complain because more - more! - Eryn.Beer26 commented on March 23rd 20 at 20:03
Further, from the `pcalendar` using WHERE p is meaningless.dt between p.dt AND CURDATE() are ALL RECORDS of table `pcalendar`, each WITHOUT any conditions stupidly added id_product=..., and this is the result of the outer query.

Why are all the records? There is a condition that take records from the first date in the calendar table for the current CURDATE()
Ie at the moment it displays 158 rows 2019-01-01 for 2019-06-07 - Eladio.Veum commented on March 23rd 20 at 20:06
@Eladio.Veum, ALL records. Because there is a condition p.dt BETWEEN p.dt AND CURDATE(), which means `the value dt` <= `the value dt` <= CURDATE().
But since p.dt is always equal to itself (p.dt == p.dt), and it is always less than CURDATE(), something WHERE you can replace the WHERE 1...

Or you there are p.dt from the future? Well, then not ALL records in the table and only the records with p.dt prior to the date CURDATE(). - Eryn.Beer26 commented on March 23rd 20 at 20:09
@Eryn.Beer26, Yes, the table of calendar entries from the future) until 2025 - Eladio.Veum commented on March 23rd 20 at 20:12
@Eladio.Veum, but the show EXPLAIN this request. Curious how long it will take? - Eryn.Beer26 commented on March 23rd 20 at 20:15
@Eryn.Beer26,
1 PRIMARY <derived3> NULL ALL NULL NULL NULL NULL 15585 11.11 Using where; Using filesort 
1 PRIMARY NULL ref prc id_product,id_date id_date 306 t2.id_product,t2.dtp 1 100.00 NULL 
N 1 PRIMARY NULL ref id_product id_product 303 t2.id_product 1 100.00 NULL 
3 DERIVED <derived5> NULL system NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort 
3 DERIVED p ALL NULL NULL NULL NULL NULL 2192 11.11 Using where 
3 DERIVED p ref id_product, NULL,id_date id_date 302 const 64 100.00 Using where; Using index 
5 DERIVED p ref id_product, NULL,id_date id_product 302 const 64 100.00 Using index

He Select took 0.0104 seconds.)
But all this does not solve my problems, because I still can't understand why a VARCHAR works, but with digital types - no - Eladio.Veum commented on March 23rd 20 at 20:18
March 23rd 20 at 19:11
Very desirable id_product replaced by int if possible, or switch to using the other id.

Try to create a combined index on all fields participating in the sample. It will be quite large and will slow down the insert, but will improve the selection.

Try to choose no sorting, if the difference is very large, it is possible to do it separately. (a subquery to get the data and then sort).

If still bad, and the data is growing very quickly, then you may want to consider other DB, something high-performance like tarantool, clickhouse, or any of the family tsdb.
March 23rd 20 at 19:13
1. According to EXPLAIN, the indexes you have are not used during query execution. This can happen due to the fact that the indexes are too large and do not climb in memory. Change the value of innodb_buffer_pool_size (if you have InnoDB) or key_buffer_size (if MyISAM) ~30%-50% of the RAM
2. Remove the sorting from a query if you need it only for the total sample. Use SELECT FROM SELECT to sort the resulting sample

Find more questions by tags MySQL