I have 23 of these tables in one database:

  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);

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
  • key_buffer_size == 268435456
  • innodb_thread_concurrency = 64
  • innodb_buffer_pool_size = 3133495296 [e.q. 75% of available RAM]
  • MySQL is using all of the available cores.

My question:

How can be this query optimized/rewritten so there is no performance issue?

As requested, the result of EXPLAIN:

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 DateTime index to PRIMARY KEY.
  • Changing MIN() to ORDER BY DateTime LIMIT 1 fixed the performance issues.. The job needs now 50% of the time.

Suggestions by user Rick James:

  • The other indexes - apart from PRIMARY KEY are 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.
  • 1
    @ypercubeᵀᴹ Question updated in order to answer all your comments. PS: Can yo clean up a bit your comments? Otherwise SO complaints we should start a chat... Thx Feb 11, 2019 at 16:17
  • 1
    Let us continue this discussion in chat. Feb 11, 2019 at 16:58
  • FYI key_buffer_size is irrelevant for InnoDB tables. That buffer is used only for indexes of MyISAM tables. Apr 28, 2023 at 17:09

1 Answer 1


First, some cleanup. Then I will provide the solution.

First, the UNIQUE(DateTime) obviates the need for any of the other unique keys. They could be plain (non-unique) indexes.

Second, you have not specified a PRIMARY KEY for your InnoDB table. This is naughty, but not the end of the world. I recommend you promote UNIQUE(DateTime) to be PRIMARY KEY(DateTime).

Third; once you have done that, the rest of the indexes (plain or unique) are useless; get rid of them.

The query is a tough one to optimize -- it needs, shall we say, a "2-dimensional" index. But indexes are only 1D. WHERE DateTime>'2018-08-20 09:53:00' AND AskHigh>999999 would probably involve scanning all the rows with a date after the given one if an index starting with DateTime were used.

So, to make that variation run faster, you would need an index starting with AskHigh. But then that is essentially useless if you ask AND AskHign > 0.0001.

PARTITIONing won't help because of the same issues -- pruning partitions with low values of DateTime or AskHigh is no better than having an index to avoid it.

With only 4GB of RAM, 3G is too much for the buffer_pool. Lower it to 1500M. Swapping is terrible for performance of MySQL.

For further discussion, please provide

the size of the table
  • I understand that I did the requested cleanup before you provide the solution. Now it works because I eliminated MIN() but I would like to see the solution with MIN(). Feb 14, 2019 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.