2

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

4
  • 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.
    – danblack
    Jul 5 at 2:18
  • Thanks! I have added that information
    – sqldumdum
    Jul 5 at 2:34
  • Please consider reading this advice
    – mustaccio
    Jul 5 at 12:30
  • The table should have a PRIMARY KEY.
    – Rick James
    Jul 5 at 22:16
6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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