title: MySQL performance - large database
body: <p>I've read heaps of posts here on stackoverflow, blog posts, tutorials and more, but I still fail to resolve a rather nasty performance issue with my MySQL db. Keep in mind that I'm a novice when it comes to large MySQL databases.</p>
  
  <p>I have a table with approx. 11.000.000 rows (will increase to say 20.000.000 or more). Here's the layout:</p>
  
  <pre><code>CREATE TABLE `myTable` (
    `intcol1` int(11) DEFAULT NULL,
    `charcol1` char(25) DEFAULT NULL,
    `intcol2` int(11) DEFAULT NULL,
    `charcol2` char(50) DEFAULT NULL,
    `charcol3` char(50) DEFAULT NULL,
    `charcol4` char(50) DEFAULT NULL,
    `intcol3` int(11) DEFAULT NULL,
    `charcol5` char(50) DEFAULT NULL,
    `intcol4` int(20) DEFAULT NULL,
    `intcol5` int(20) DEFAULT NULL,
    `intcol6` int(20) DEFAULT NULL,
    `intcol7` int(11) DEFAULT NULL,
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`),
    FULLTEXT KEY `idx` (`charcol2`,`charcol3`)
  ) ENGINE=MyISAM AUTO_INCREMENT=11665231 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>A select statement like </p>
  
  <p><code>SELECT * from myTable where charchol2='bogus' AND charcol3='bogus2';</code> </p>
  
  <p>takes 25 seconds or so to execute. That's too slow, and will be even slower as the table grows.</p>
  
  <p>The table will not have any inserts or updates at all (so to speak), and will be primarily used for outputting searches on the char-columns.</p>
  
  <p>I've tried to make indexing work (playing around with FULLTEXT, as you can see), but it seems that I'm missing something. Any takes on how to speed up the performance?</p>
  
  <p>Please note: Im currently running MySQL on my Macbook Air (1.7 GHz i5, 4GB RAM). If this is the only answer to my performance issues, I'll move the database to something appropriate ;-)</p>
  
  <p>EDIT: Explain table</p>
  
  <p>id  select_type table   type    possible_keys   key key_len ref rows    Extra<br>
  1   SIMPLE  myTable ALL NULL    NULL    NULL    NULL    11596725    Using where</p>