How can I speed up this mysql (mariadb) Query?

Question

I'm averaging about 1000 queries per minute with the below query. Most of the query will stay the same with variations on the date,hour,minute and itemName (2021年06月02日, 10, 30 and "football" in this example ). I've switched around various parts and this seems to be the best order for performance. I suspect a query utilizing different functions may be more appropriate for performance but I've yet to figure one out.

SELECT DatetimeRecorded, itemName, field1, field2, field3
 FROM StoreData.data
 WHERE DatetimeRecorded BETWEEN '2021-06-02' - INTERVAL 30 DAY
 AND '2021-06-02' - INTERVAL 1 DAY
 AND itemName = 'football'
 and HOUR(DatetimeRecorded) = 10
 and MINUTE(DatetimeRecorded) <= 30 

The frequency of "football" in the data base will only occur once per day with the example time constraints, meaning this and other similar queries would return a max of 30 rows being 1 per day.

In a more human readable format what I'm hoping to do more efficiently is "select 30 days before 2021年06月02日 where the time is between 10:00 and 10:30 and the item is football"

additional info

• queries are being run on mariadb Ver 15.1 Distrib 10.3.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
• the database is about a half million entries
• there are about 5000 unique itemNames

Here is the output show create table

 | Data | CREATE TABLE `data` (
 `DatetimeRecorded` datetime DEFAULT NULL, 
 `field1` varchar(255) DEFAULT NULL, 
 `field2` varchar(255) DEFAULT NULL, 
 `field3` varchar(9) DEFAULT NULL, 
 `itemName` varchar(255) NOT NULL, 
 `field4` varchar(255) DEFAULT NULL, 
 `field5` double DEFAULT NULL, 
 `field6` int(11) DEFAULT NULL, 
 `field7` varchar(255) DEFAULT NULL, 
 `field8` double DEFAULT NULL, 
 `field9` int(11) DEFAULT NULL, 
 `field10` varchar(255) DEFAULT NULL, 
 `field11` double DEFAULT NULL, 
 `field12` int(11) DEFAULT NULL, 
 `field13` varchar(255) DEFAULT NULL, 
 `field14` double DEFAULT NULL, 
 `field15` double DEFAULT NULL, 
 `field16` double DEFAULT NULL, 
 `field17` double DEFAULT NULL, 
 `field18` double DEFAULT NULL, 
 `field19` int(11) NOT NULL, 
 `field20` mediumtext DEFAULT NULL, 
 `field21` mediumtext DEFAULT NULL, 
 `field22` double DEFAULT NULL, 
 `field23` varchar(255) DEFAULT NULL, 
 `field24` varchar(255) DEFAULT NULL, 
 `field25` varchar(255) DEFAULT NULL, 
 `field26` varchar(255) DEFAULT NULL, 
 `field27` double DEFAULT NULL, 
 `field28` int(11) DEFAULT NULL, 
 `field29` double DEFAULT NULL, 
 `field30` double DEFAULT NULL, 
 `field31` double DEFAULT NULL, 
 `field32` double DEFAULT NULL, 
 `field33` double DEFAULT NULL, 
 `field34` datetime DEFAULT NULL, 
 `field35` varchar(255) DEFAULT NULL, 
 `field36` double DEFAULT NULL, 
 `field37` int(11) DEFAULT NULL, 
 `field38` double DEFAULT NULL 
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

Apologies if I have left out any important details, I'm very new to sql and would appreciate any guidance/suggestions

• danblack: can you edit your question to include show create table StoreData.data output. Also which MariaDB version are you using? You've done pretty well. Welcome to DBA stackexchange.
• sqldumdum: Thanks! I have added that information
• mustaccio: Please consider reading this advice
• Rick James: The table should have a PRIMARY KEY.

Answer 1

Add this index:

ALTER TABLE StoreData.data ADD INDEX (itemName, DatetimeRecorded);

The itemName column needs to go first. This will narrow down the search by equality on that column.

Then the DatetimeRecorded column narrows down the search further within the matching rows to the date range you want. That index will not help narrow down by hours and minutes because the time range occurs every day; it's not contiguous.

If you want to improve the optimization any more with an index, you'd need to store the date and time in separate columns.

But you will probably find that it's unnecessary, because indexing the first two columns as described above will give plenty of improvement, at least until your table grows a few orders of magnitude. But you should re-test your optimization strategy after every order of magnitude growth anyway.