title: MySQL MyISAM table performance... painfully, painfully slow
body: <p>I've got a table structure that can be summarized as follows:</p>
  
  <pre><code>pagegroup
  * pagegroupid
  * name
  </code></pre>
  
  <p>has 3600 rows</p>
  
  <pre><code>page
  * pageid
  * pagegroupid
  * data
  </code></pre>
  
  <p>references pagegroup;
  has 10000 rows;
  can have anything between 1-700 rows per pagegroup;
  the data column is of type mediumtext and the column contains 100k - 200kbytes data per row</p>
  
  <pre><code>userdata
  * userdataid
  * pageid
  * column1
  * column2
  * column9
  </code></pre>
  
  <p>references page;
  has about 300,000 rows;
  can have about 1-50 rows per page</p>
  
  <p>The above structure is pretty straight forwad, the problem is that that a join from userdata to page group is terribly, terribly slow even though I have indexed all columns that should be indexed. The time needed to run a query for such a join (userdata inner_join page inner_join pagegroup) exceeds 3 minutes. This is terribly slow considering the fact that I am not selecting the data column at all. Example of the query that takes too long:</p>
  
  <pre><code>SELECT userdata.column1, pagegroup.name
  FROM userdata
  INNER JOIN page USING( pageid )
  INNER JOIN pagegroup USING( pagegroupid )
  </code></pre>
  
  <p>Please help by explaining why does it take so long and what can i do to make it faster.</p>
  
  <h2>Edit #1</h2>
  
  <p>Explain returns following gibberish:</p>
  
  <pre><code>id  select_type  table      type    possible_keys        key      key_len  ref                         rows    Extra
  1   SIMPLE       userdata   ALL     pageid                                                             372420
  1   SIMPLE       page       eq_ref  PRIMARY,pagegroupid  PRIMARY  4        topsecret.userdata.pageid   1
  1   SIMPLE       pagegroup  eq_ref  PRIMARY              PRIMARY  4        topsecret.page.pagegroupid  1
  </code></pre>
  
  <h2>Edit #2</h2>
  
  <pre><code>SELECT
  u.field2, p.pageid
  FROM
  userdata u
  INNER JOIN page p ON u.pageid = p.pageid;
  /*
  0.07 sec execution, 6.05 sec fecth
  */
  
  id  select_type  table  type    possible_keys  key      key_len  ref                rows     Extra
  1   SIMPLE       u      ALL     pageid                                              372420
  1   SIMPLE       p      eq_ref  PRIMARY        PRIMARY  4        topsecret.u.pageid 1        Using index
  
  SELECT
  p.pageid, g.pagegroupid
  FROM
  page p
  INNER JOIN pagegroup g ON p.pagegroupid = g.pagegroupid;
  /*
  9.37 sec execution, 60.0 sec fetch
  */
  
  id  select_type  table  type   possible_keys  key          key_len  ref                      rows  Extra
  1   SIMPLE       g      index  PRIMARY        PRIMARY      4                                 3646  Using index
  1   SIMPLE       p      ref    pagegroupid    pagegroupid  5        topsecret.g.pagegroupid  3     Using where
  </code></pre>
  
  <h2>Moral of the story</h2>
  
  <p>Keep medium/long text columns in a separate table if you run into performance problems such as this one.</p>