Maintaining mysql db and server loads

Question

I am creating a chart using information from mysql. The problem is, the load on the database is huge and when you have hundreds or more people accessing the chart at the same time, MASSIVE stalling issues arise.

The queries look like the two I have listed below:

$stmt=$db->prepare("SELECT COUNT(*) AS cnt
                    FROM stream_updates 
                        INNER JOIN members 
                    ON members.username=stream_updates.username 
                    WHERE (stream_updates.time between (NOW() - INTERVAL 1 DAY) AND NOW())
                    AND (members.username = :user) OR members.username IN (
                           SELECT friend2 as username FROM list_friends 
                           WHERE (friend1 = :user AND friend2 <> :user) 
                              UNION
                           SELECT friend1 as username FROM list_friends
                           WHERE (friend2 = :user AND friend1 <> :user)
                    )");
$stmt->bindParam(':user', $username);
$stmt->execute();
$textcnt1 = $stmt->fetchColumn();

$stmt=$db->prepare("SELECT COUNT(*) AS cnt
                    FROM stream_updates 
                        INNER JOIN members 
                    ON members.username=stream_updates.username 
                    WHERE (stream_updates.time between (NOW() - INTERVAL 2 DAY) AND (NOW() - INTERVAL 1 DAY))
                    AND (members.username = :user) OR members.username IN (
                           SELECT friend2 as username FROM list_friends 
                           WHERE (friend1 = :user AND friend2 <> :user) 
                              UNION
                           SELECT friend1 as username FROM list_friends
                           WHERE (friend2 = :user AND friend1 <> :user)
                    )");
$stmt->bindParam(':user', $username);
$stmt->execute();
$textcnt2 = $stmt->fetchColumn();

So the way it works is simple, 7 queries grab 7 different numbers based on the times they were posted. Each of those 7 queries is run against 5 different databases to make for the total of 35 queries. And this is only for a single chart on the page that is also performing other queries and doing other tasks.

I was thinking perhaps I could store the query values as cookies that expire every 30 minutes so as to lighten the load a touch, but was hoping others with more experience could perhaps offer some insight into how to do this?

The time and username columns are indexed and I am not looking to use stored procedures.

Is there a better way to do this so I don't make 35 separate database queries?

UPDATED WITH AN SQL FIDDLE

http://sqlfiddle.com/#!9/fa369f6/1

The above fiddle only shows one example query. Simply switch to test various queries.

(NOW() - INTERVAL 1 DAY) AND NOW())

to

(NOW() - INTERVAL 2 DAY) AND (NOW() - INTERVAL 1 DAY))

Clarifying the queries

So what these query do is simple. Each does a count for all rows matching a given user on a given table and based on that users friends.

In the sqlfiddle example, the return is 3 because a count of all posts in the "favorites" table matched again both a user and all their friends from table list_friends return 3. If we add another user to the table, but do no include said user in the friends list, those posts would not be counted.

All this works as expected, but 35 queries seems to have a rather large load even when they are indexed columns.

id   |     select_type     |    table     |  type   |  possible_keys  |   key    |  key_len  |        ref         |  rows  |  Extra
-------------------------------------------------------------------------------------------------------------------------------------
1    |       PRIMARY       |  favorites   |   ALL   |      NULL       |   NULL   |  NULL     |         NULL       |    9   |   
1    |       PRIMARY       |    members   |  eq_ref |    username     | username |    257    | favorites.username |    1   | Using where; Using index
2    | DEPENDENT SUBQUERY  | list_friends |   ALL   |      NULL       |   NULL   |    NULL   |        NULL        |    3   |    Using where
3    |   DEPENDENT UNION   | list_friends |   ALL   |      NULL       |   NULL   |    NULL   |        NULL        |    3   |    Using where
NULL |    UNION RESULT     |  <union2,3>  |   ALL   |      NULL       |   NULL   |    NULL   |        NULL        |  NULL  |

Answer 1

Let's take a look at your explain result, note the possible_keys column.

The possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table ... If this column is NULL (or undefined in JSON-formatted output), there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to check whether it refers to some column or columns that would be suitable for indexing. If so, create an appropriate index and check the query with EXPLAIN again.

It is likely that you will see a big boost in performance if you index friend1,friend2 fields on your list_friends table. Particularly in view of the fact that it's used twice.

ALTER TABLE list_friends ADD INDEX f1f2(friend1,friend2)

You may also benefit from an index on the time field on your favorite table.

These operations will make each of your queries run faster, as for the need to run them 35 times, it's not clear why you are actually running them 7 times and on 5 different databases.

Answer 2

How about this:

SELECT datediff(NOW(), `stream_updates`.`time`) AS days, COUNT(*) AS cnt
FROM stream_updates 
INNER JOIN members ON members.username = stream_updates.username 
WHERE datediff(NOW(), `stream_updates`.`time`) < 3
AND (
  members.username = :user
  OR
  members.username IN (
    SELECT DISTINCT if(friend2 = :user, friend1, friend2)
    FROM list_friends 
    WHERE friend1 = :user
    OR friend2 = :user
  )
)
GROUP BY 1

Keywords Distinct might speedup as well

And here is the update to your demo: http://sqlfiddle.com/#!9/fa369f6/7

OR run this, pick up whichever is faster:

SELECT datediff(NOW(), `stream_updates`.`time`) AS days, COUNT(*) AS cnt
FROM stream_updates 
INNER JOIN members ON members.username = stream_updates.username 
WHERE datediff(NOW(), `stream_updates`.`time`) < 3
AND (
  members.username = :user
  OR EXISTS (
    SELECT 1 FROM list_friends WHERE
    (members.username = friend1 AND friend2 = :user)
    OR 
    (members.username = friend2 AND friend1 = :user)
  )
)
GROUP BY 1