title: Optimize MySQL for Prestashop 1.6
body: <p>I am running Prestashop 1.6 powered e-shop on VPS with 4GB RAM, 2CPUs, 60GB SSD characteristics. Currently I have about 20000 products in my shop and I am having problems with site loading because mysql queries run long. And when I run <strong>htop</strong> to analyze processes, I see that mysql consumes 100% of both CPUs. This is the output from <strong>mysqltuner</strong>:</p>
  
  <pre><code>-------- Performance Metrics -------------------------------------------------
  [--] Up for: 1h 29m 9s (241K q [45.109 qps], 319 conn, TX: 318M, RX: 126M)
  [--] Reads / Writes: 78% / 22%
  [--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
  [OK] Maximum possible memory usage: 597.8M (15% of installed RAM)
  [OK] Slow queries: 0% (8/241K)
  [OK] Highest usage of available connections: 2% (4/151)
  [OK] Key buffer size / total MyISAM indexes: 16.0M/44.9M
  [OK] Key buffer hit rate: 99.6% (36M cached / 133K reads)
  [OK] Query cache efficiency: 49.6% (101K cached / 205K selects)
  [!!] Query cache prunes per day: 1386761
  [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 7K sorts)
  [!!] Joins performed without indexes: 78
  [OK] Temporary tables created on disk: 8% (846 on disk / 9K total)
  [OK] Thread cache hit rate: 98% (4 created / 319 connections)
  [!!] Table cache hit rate: 10% (340 open / 3K opened)
  [OK] Open file limit used: 62% (643/1K)
  [OK] Table locks acquired immediately: 100% (239K immediate / 239K locks)
  
  -------- Recommendations -----------------------------------------------------
  General recommendations:
      Add skip-innodb to MySQL configuration to disable InnoDB
      MySQL started within last 24 hours - recommendations may be inaccurate
      Enable the slow query log to troubleshoot bad queries
      Adjust your join queries to always utilize indexes
      Increase table_cache gradually to avoid file descriptor limits
  Variables to adjust:
      query_cache_size (&gt; 16M)
      join_buffer_size (&gt; 128.0K, or always use indexes with joins)
      table_cache (&gt; 400)
  </code></pre>
  
  <p>Please suggest any optimization methods.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Slow query output log is <a href="http://pastie.org/9664946" rel="nofollow">here</a>.</p>