MySQL optimization with tables over 12 million rows
$250-750 USD
Suoritettu
Julkaistu yli 11 vuotta sitten
$250-750 USD
Maksettu toimituksen yhteydessä
We have a platform that collects, computes, and sends data over an API as well as via a dashboard of metrics. We have some MySQL queries that are running slow, one being against a table with over 12 million rows. We need someone to go in and optimize the queries and overall database structure if need be. We're currently hosted with Media Temple and setup with a MySQL container. If need be, may migrate to Amazon EC2.
We have created indexes on tables, but still have not seen load improvements.
Sample queries:
$plugins = mysql_query("SELECT SQL_NO_CACHE plugin, count(*) as freq FROM plugins_used WHERE auth = '$auth' GROUP BY plugin ORDER BY freq DESC LIMIT 0, 10") or die(mysql_error());
$returned_plugins = mysql_fetch_array($plugins);
$total_plugins = mysql_num_rows($plugins);
$total_freq = mysql_query("SELECT SQL_NO_CACHE SUM(totalfreq) FROM (SELECT plugin, count(*) as totalfreq FROM plugins_used WHERE auth = '$auth' GROUP BY plugin ORDER BY totalfreq DESC LIMIT 0, 10) as total") or die(mysql_error());
$final_freq = mysql_fetch_array($total_freq);
$total = $final_freq['SUM(totalfreq)'];