Greetings,
I am trying to find ways to optimize my MySQL usage and reduce load on the server. One idea I had was to consolidate many small queries into one complex one.
For instance, feedback ratings being consolidated from:
[php]$sql = mysql_fetch_array(mysql_query(“SELECT COUNT() as num FROM feedback WHERE receiver = ‘$seller’ AND rating > 0"));
$totalcount = $sql[‘num’];
$sql = mysql_fetch_array(mysql_query("SELECT COUNT() as num FROM feedback WHERE receiver = ‘$seller’ AND rating = ‘1’”));
$positivecount = $sql[‘num’];
$sql = mysql_fetch_array(mysql_query(“SELECT COUNT(*) as num FROM feedback WHERE receiver = ‘$seller’ AND rating = ‘2’”));
$negativecount = $sql[‘num’];[/php]
to:
[php]$sql = mysql_fetch_array(mysql_query(“SELECT COUNT(CASE WHEN rating > 0 THEN ‘1’ END) AS tot, COUNT(CASE WHEN rating = 1 THEN ‘2’ END) AS pos, COUNT(CASE WHEN rating = 2 THEN ‘3’ END) AS neg FROM feedback WHERE receiver = ‘$seller’”));
$feedbackcount = $sql[‘tot’];
$positivecount = $sql[‘pos’];
$negativecount = $sql[‘neg’];[/php]
Another thing was I split up a main table with 100 columns into about 5 smaller tables. Currently, I use 5 different queries to get bits of info from each table on certain pages. I was thinking of consolidating these into 1 single query using JOIN on 5 tables.
Question: I was wondering if anyone at Dreamhost could comment on whether this will make the server processors work harder due to the more complex code or will these solutions effectively reduce the MySQL usage? What is recommended to not get me booted onto a private server basically due to over-usage?
Thanks