MySQL Locking Tables with millions of rows
<p>I've been running a website, with a large amount of data in the process.</p>
<p>A user's save data like ip , id , date to the server and it is stored in a MySQL database. Each entry is stored as a single row in a table.</p>
<p>Right now there are approximately 24 million rows in the table</p>
<p><strong>Problem 1:</strong></p>
<p>Things are getting slow now, as a full table scan can take too many  minutes but I already indexed the table.</p>
<p><strong>Problem 2:</strong></p>
<p>If a user is pulling a select data from table it could potentially block all other users (as the table is locked) access to the site until the query is complete.</p>
<p><strong>Our server</strong></p>
<ul>
<li>32 Gb Ram</li>
<li>12 core with 24 thread cpu</li>
<li>table use MyISAM engine
<a href="https://i.stack.imgur.com/qMdQE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qMdQE.png" alt=" " /></a></li>
</ul>
<pre><code>EXPLAIN SELECT SUM(impresn), SUM(rae), SUM(reve), `date` FROM `publisher_ads_hits` WHERE date between '2015-05-01' AND '2016-04-02' AND userid='168' GROUP BY date ORDER BY date DESC
</code></pre>