title: Slow query on information_schema.tables
body: <p>I run a set of self-developed applications on a typical shared hosting service offering.  I moved from a static configured table list of allowed tables to one based on a list of tables bases on a prefix from the D/B metadata.  When I did promote this version to the public service, my per-request latency increased by an average <strong>2.3–2.4 secs</strong>. Some instrumentation revealed that this was entirely down to one SQL query:</p>
  
  <pre><code>SELECT TABLE_NAME AS name
  FROM information_schema.tables
  WHERE TABLE_SCHEMA = '&lt;DBname&gt;'
  AND TABLE_NAME LIKE '&lt;TablePrefix&gt;%';
  </code></pre>
  
  <p>I used this because I wanted to explicitly name the column in the result set.  However, coding around this with an alternate query adds an extra line of code which ran in <strong>&lt;2 mSec</strong>:</p>
  
  <pre><code>SHOW TABLES LIKE '&lt;TablePrefix&gt;%';
  </code></pre>
  
  <p>My service provider uses Enterprise MySql 5.0.92-50 so I can't do any profiling.  This is a scaling issue as it doesn't occur on my dev environment and test VM where I can profile. They support thousands of users so the live schema will be extremely large, but even so connection and most queries only take milliseconds.  </p>
  
  <p>Does anyone know why querying the memory-based information_schema on a large multi-user system would take so long?</p>