title: MySql MAX query optimization
body: <p>Given this situation, I have two tables with the following existing indices:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>...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 <strong>50 Million+</strong> records, and tblBusses contains <strong>approx 1 million</strong> records.</p>
  
  <p>The persons table is being populated in steps (with a cronjob), and I need to <strong>get the next 100 busses from the tblBusses that are not yet referred to in the tblPersons with a selected sex</strong>, 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.</p>
  
  <p>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:</p>
  
  <pre><code>SELECT max(intBusID) AS intMaxBus 
  FROM tblPersons 
  WHERE intSexID={$intSexID};
  
  SELECT intID, strBus 
  FROM tblBusses 
  WHERE intID&gt;{$intMaxBus} 
  ORDER BY intID ASC 
  LIMIT 100;
  </code></pre>
  
  <p>The second query takes 1900 microseconds (fast enough). The first takes 2.5 seconds, which is too slow. Certainly for the foreseeable future.</p>
  
  <p>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.</p>
  
  <p>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?</p>