MySQL: Many small queries or one complex one?

#1

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

#2

What about something like this?

select rating, count(*) from feedback where receiver = '$seller' group by rating

You can loop those records and decide what to do with each rating value

#3

Not sure which is most efficient on the Dreamhost servers…

#4

Just test it and find out. It has more to do with your indexes than anything else. Although the panel and wiki still mention conueries, there have been DH staff of the forums who have said that the number of connections is not so important and the thing to watch for is long queries.

#5

How do you test this? I’ve been told by DH that the number of connections isn’t a big deal but I’m not sure if the long query in the example above is more or less efficient than the 3 queries above.

#6

you run the queries via the command line or phpmyadmin and note the times. or add timing to your code.

#7

How can you add timing to code?