Methods to speed up specific query

I have an existing sql query which works well but takes what I consider to be quite a bit of time and resources for such a small resultset. I am trying to figure out if the following query can be optimized in ways I am unfamiliar for better performance.

Query

SELECT
    a.programname, count(b.id)
FROM
    groups a
LEFT JOIN
    selections b ON (a.id_selection = b.id AND a.min_age = 18 AND a.max_age = 24)
LEFT JOIN
    member_info c ON (b.memberid = c.memberid AND (c.status = 1 OR c.term_date > '2011-01-31'))
WHERE
    a.flag = 3
GROUP BY
    a.programname
ORDER BY
    a.programid asc;

There are three tables at work here:

Groups - A

Groups contains a list of possible program selections a member can make. A member can have multiple selections within the entire table but can only have one selection per programname and only one age bracket. The overall program is determined by the flag which limits the 400+ programs to only say 100 possible mixes. The program names grouped together are:

member only, member plus spouse, member plus child, family

The resultset must return the count of all active members who have that particular selection, even if the result is 0 (i.e. cannot limit the resultset to 3 rows just because one has a zero count).

Selections

This table groups the member selections to multiple groups selections. One member can have multiple IDs from groups but only one of each type.

Member_info

contains information about each particular member, including their status (1 is active) and if their termination date is passed in the event they are not active.

My query takes nearly 3/4 of a full second which I find to be way too much for this time of information but maybe I can wrong with all the necessary joins.

Any help is greatly appreciated. I can further expand my question if necessary.

EXPLAIN details

1   SIMPLE  a   ALL                 184 Using where; Using temporary; Using filesort
1   SIMPLE  b   index       memberid_id 7       3845    Using index
1   SIMPLE  c   ALL                 1551    

EDIT REGARDING INDEX SUGGESTION

I have given much thought to the use of indexes regarding this query but as nearly all sources would sug开发者_Go百科gest, the use in an example like this may actually be hurtful. The best summary i found was:

Indexes are something extra that you can enable on your MySQL tables to increase performance,cbut they do have some downsides. When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.

The member_information table will grow daily, the groups will stay fairly constant but the selections table can change drastically on a daily basis. As such, the use of indexes really seems to have a negative effect in this case.

---

Do you have indexes on the columns being joined? That would be an obvious first step.

---

There seems to be no problems with this query. Your options are

• using indexes: if you plan to read way more than write
• using parameterized queries, so that the db engine can cache the execution plan for reuse

Beyond this, there must be some serious bottleneck in the system or millions of rows in the tables that causes a long execution.

How does you query perform, if you run the query 100 times parallel?

---

If you run this query often, try using bind parameters instead of just concatenating sql. That way the db engine can cache the execution plan.