title: Efficiently querying a 15,000,000 rows table in MySQL
body: <p>Consider the following database tables:</p>
  
  <ul>
  <li>Table "messages" with 13,000,000 rows (one row per message).</li>
  <li>Table "users" with 3,000,000 rows (one row per user).</li>
  </ul>
  
  <p>The following query is used to fetch a bunch of messages and the corresponding users:</p>
  
  <pre><code>SELECT messages.id, messages.message, users.id, users.username
  FROM messages
  INNER JOIN users ON messages.user_id=users.id 
  WHERE messages.id in (?, ?, ?, ? ... a total of 100 "?":s);
  </code></pre>
  
  <p>100 messages are fetched in each query. </p>
  
  <p>"messages" is indexed on id (primary key, BIGINT <em>not</em> auto-generated) and user_id.</p>
  
  <p>"users" is indexed on id (primary key, INT auto-generated).</p>
  
  <p>The database is MySQL using MyISAM.</p>
  
  <p>Currently the query takes well over 3000 ms to execute which puzzles me since "messages" is indexed on "id", so retrieving the correct rows should be very quick.</p>
  
  <p>My question is: Given the describe scenario and setup, is a 3000 ms query time "normal" or am I missing something? Please let me know if further details are required.</p>
  
  <p><b>Update #1:</b> Here are the table definitions:</p>
  
  <pre><code>CREATE TABLE messages (
    id bigint(20) NOT NULL DEFAULT '0',
    user_id int(11) NOT NULL DEFAULT '0',
    message varchar(160) NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    KEY user_id (user_id),
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  
  CREATE TABLE users (
    id int(11) NOT NULL DEFAULT '0',
    username varchar(32) NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    UNIQUE KEY username (username),
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>The only "non-standard" thing I observe in the definitions is that "messages.id" is a BIGINT rather than an INT. Could that be a hint?</p>