I have a "simple" query how takes 0.7678 seconds or more to be executed with MariaDB.
Here is the query:
select `referenceNumber`
from `invoice`
where `groupId` = 3550
and `referenceNumber` >= 301
order by `referenceNumber` desc
limit 1;
These columns have an index: "referenceNumber", "groupId"
Here is the result of an EXPLAIN:

I found a solution by creating a subquery like that:
select `referenceNumber`
from (
SELECT id
from `invoice`
where `groupId` = 3550
and `referenceNumber` >= 301
) as subquery
JOIN invoice as invoice ON invoice.id = subquery.id
order by `referenceNumber` desc
limit 1;
This query takes like 0.0011 seconds.
Here is the result of an EXPLAIN:

Do you have an explanation about the poor performance of the first query?
Two surprising findings:
The query without the where `groupId` = 3550 takes only 0.0005 seconds like that:
select `referenceNumber`
from `invoice`
where `referenceNumber` >= 301
order by `referenceNumber` desc
limit 1;
The query without the order by `referenceNumber` desc takes only 0.0011 seconds like that:
select `referenceNumber`
from `invoice`
where `groupId` = 3550
and `referenceNumber` >= 301
limit 1;
Here is the schema of this table:
CREATE TABLE `invoice` (
`id` int(10) UNSIGNED NOT NULL,
`groupId` int(11) NOT NULL,
`referenceNumber` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `invoice`
ADD PRIMARY KEY (`id`),
ADD KEY `invoice_groupid_index` (`groupId`),
ADD KEY `invoice_referencenumber_index` (`referenceNumber`);
ALTER TABLE `invoice`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;
Thank you really much for your help!
groupId, referenceNumberfor the type of query you're doing. Having thegroupIdindex only gets you so far, the rest of the rows have to be matched with a scan.innodb_buffer_pool_sizeis absolutely critical. If it's set too small, you're sacrificing a ton of performance for no reason.groupId, referenceNumberand now the first query takes only 0.0005 seconds. Amazing, thank you really much!!