I have 23 of these tables in one database:
CREATE TABLE foo1 ( DateTime datetime NOT NULL, BidOpen decimal(11, 5) NOT NULL, BidHigh decimal(11, 5) NOT NULL, BidLow decimal(11, 5) NOT NULL, BidClose decimal(11, 5) NOT NULL, AskOpen decimal(11, 5) NOT NULL, AskHigh decimal(11, 5) NOT NULL, AskLow decimal(11, 5) NOT NULL, AskClose decimal(11, 5) NOT NULL, Volume decimal(8, 2) NOT NULL ) ENGINE = InnoDB; ALTER TABLE foo1 ADD UNIQUE INDEX AskHigh (DateTime, AskHigh); ALTER TABLE foo1 ADD UNIQUE INDEX AskLow (DateTime, AskLow); ALTER TABLE foo1 ADD UNIQUE INDEX BidHigh (DateTime, BidHigh); ALTER TABLE foo1 ADD UNIQUE INDEX BidLow (DateTime, BidLow); ALTER TABLE foo1 ADD UNIQUE INDEX DateTime (DateTime);
When running this query in ~70 threads
SELECT MIN(DateTime) FROM fooXY WHERE DateTime>'2018-08-20 09:53:00' AND AskHigh>1.7444;
almost each needs ~2 seconds for ~225k rows.
The issues is only when these queries are ran multi-threaded.
Single execution is very fast and as expected.
- Server has 4GB RAM
- MySQL 8.0.15
- 8 cores
innodb_buffer_pool_size= 3133495296 [e.q. 75% of available RAM]
- MySQL is using all of the available cores.
How can be this query optimized/rewritten so there is no performance issue?
As requested, the result of
id 1 select_type SIMPLE table foo1 partitions (null) type range possible_keys PRIMARY key PRIMARY key_len 5 ref (null) rows 113400 filtered 33.33 Extra Using where
The result of the suggestions in the chat or the answer:
Suggestions by user ypercube:
- I changed the
ORDER BY DateTime LIMIT 1fixed the performance issues.. The job needs now 50% of the time.
Suggestions by user Rick James:
- The other indexes - apart from
PRIMARY KEYare indeed not needed. The deletion of them also shrunk the database by almost 50%. This alone didn't fix the issue.
- Changing the pool size to 50% of the available RAM. I don't see any change in comparison to 75% so I leave the 50% in order to avoid/minimize any possible swapping.