MySql MAX query optimization

Question

Given this situation, I have two tables with the following existing indices:

CREATE TABLE tblBusses 
(
    intID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    strBus VARCHAR(100) NOT NULL DEFAULT ""
);
CREATE INDEX indBussesBus ON tblBusses (strBus );
CREATE INDEX indBussesID ON tblBusses (intID);

CREATE TABLE tblPersons
(
    intID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    strPerson VARCHAR(100) NOT NULL DEFAULT "",
    intBusID BIGINT UNSIGNED NOT NULL,
    intSexID BIGINT UNSIGNED NOT NULL,
    datCreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX indPersonsPerson ON tblPersons (strPerson );
CREATE INDEX indPersonsSex ON tblPersons (intBusID);

...And tblPersons(intBusID) is a foreign key that refers to tblBusses(intID), and tblPersons(intSexID) is a foreign key to a different, obvious table. In this case, tblPersons contains 50 Million+ records, and tblBusses contains approx 1 million records.

The persons table is being populated in steps (with a cronjob), and I need to get the next 100 busses from the tblBusses that are not yet referred to in the tblPersons with a selected sex, to perform a number of populating (insert) queries. Considering the numbers, this is a little challenge. The populating queries are fast enough, but the pre- bus selection queries are a challenge and too slow.

Currently, to select the next 100 busses, I maintain an assumption that I populate tblPersons with increasing Bus IDs, so I search for the MAX(tblPersons.intBusID), then I get the next 100 tblBusses records:

SELECT max(intBusID) AS intMaxBus 
FROM tblPersons 
WHERE intSexID={$intSexID};

SELECT intID, strBus 
FROM tblBusses 
WHERE intID>{$intMaxBus} 
ORDER BY intID ASC 
LIMIT 100;

The second query takes 1900 microseconds (fast enough). The first takes 2.5 seconds, which is too slow. Certainly for the foreseeable future.

How can I make this faster? I don't care if someone comes up with a better way of resolving my entire problem (we're all learners), or an improved solution to the MAX() query, 2.5 seconds is too slow.

One solution that I can imagine, is for mysql to somehow continuously cache the max(intBusID) position. I would have thought this happened anyway with the index, but apparently with 2.5 seconds, that is not working well enough. Any hints?

Answer 1

Try this:

SELECT intBusID AS intMaxBus
FROM tblPersons
WHERE intSexID={$intSexID}
ORDER BY intBusID DESC
LIMIT 1

I think this should use the index on intBusID to scan the rows until it finds one whose sex matches, and return it immediately. MySQL wasn't able to figure out that your query is equivalent, so it created a temporary table of all the rows whose sex matches and then found its max ID.

Answer 2

This is the max() query:

SELECT max(intBusID) AS intMaxBus 
FROM tblPersons 
WHERE intSexID={$intSexID}

You need an index on tblPersons(intSexID, intBusID);

You can combine the queries as:

SELECT intID, strBus 
FROM tblBusses 
WHERE intID > (SELECT coalesce(max(intBusID) , 0) AS intMaxBus 
               FROM tblPersons 
               WHERE intSexID = {$intSexID}
              )
ORDER BY intID ASC 
LIMIT 100;

This query will benefit from an index on tblBusses(intID, strBus).