MySQL Query Optimization : Bets and Averages I have the following selection string:

SELECT * , AVG( `q1` ) AS betavg, COUNT( bet_id ) AS nr, MAX( q1 ) AS qmax, 
(MAX( q1 ) - AVG( `q1` )) AS diff FROM jos_bet_1x3_best GROUP BY `bet_id`
HAVING ( COUNT( `bet_id` ) &gt;2 AND betavg &lt;= q1
AND (qmax - MIN( q1 )) &lt; 0.5 AND q1 &lt;3)
ORDER BY `diff` DESC LIMIT 0 , 30


Can I optimize this somehow ? I want to select that data where the q1 is bigger than the average. 

This is the sample data:

+-------+---------------+------+------+------+---------------------+-----------+
| id    | bet_id        | q1   | qx   | q2   | timestamp           | bookie_id |
+-------+---------------+------+------+------+---------------------+-----------+
| 32665 | 4e371a6e23735 | 1.90 | 3.50 | 3.80 | 2011-08-01 21:28:14 |         4 |
| 33410 | 4e371a6e23735 | 2.02 | 3.25 | 3.70 | 2011-08-01 21:28:31 |         8 |
| 34423 | 4e371a6e23735 | 2.05 | 3.25 | 3.25 | 2011-08-01 21:30:08 |         3 |
| 32666 | 4e371a6e23fe8 | 2.20 | 3.25 | 3.20 | 2011-08-01 21:28:14 |         4 |
| 33407 | 4e371a6e23fe8 | 2.17 | 3.20 | 3.35 | 2011-08-01 21:28:31 |         8 |
| 34252 | 4e371a6e23fe8 | 2.20 | 3.25 | 3.25 | 2011-08-01 21:28:52 |         2 |
| 34418 | 4e371a6e23fe8 | 2.05 | 3.25 | 3.30 | 2011-08-01 21:30:08 |         3 |
| 32667 | 4e371a6e24750 | 1.40 | 4.25 | 8.00 | 2011-08-01 21:28:14 |         4 |
| 33406 | 4e371a6e24750 | 1.45 | 4.10 | 7.10 | 2011-08-01 21:28:31 |         8 |
| 34253 | 4e371a6e24750 | 1.40 | 4.20 | 8.50 | 2011-08-01 21:28:52 |         2 |
| 34425 | 4e371a6e24750 | 1.45 | 3.75 | 7.00 | 2011-08-01 21:30:08 |         3 |
| 32668 | 4e371a6e24ee4 | 1.80 | 3.50 | 4.25 | 2011-08-01 21:28:14 |         4 |
| 33462 | 4e371a6e24ee4 | 1.87 | 3.35 | 4.15 | 2011-08-01 21:28:31 |         8 |
| 34251 | 4e371a6e24ee4 | 1.75 | 3.55 | 4.65 | 2011-08-01 21:28:52 |         2 |
| 34420 | 4e371a6e24ee4 | 1.85 | 3.25 | 4.00 | 2011-08-01 21:30:08 |         3 |
+-------+---------------+------+------+------+---------------------+-----------+


Finally, I made an other solution based on drex solution:

SELECT A. * , ( A.q1 - tb1.betavg) AS diff, tb1.betavg, tb1.nr FROM jos_bet_1x3_best A 
    INNER JOIN (
        SELECT bet_id, AVG( `q1` ) AS betavg, COUNT( bet_id ) AS nr
        FROM jos_bet_1x3_best GROUP BY `bet_id`
    ) AS tb1
USING ( bet_id ) WHERE tb1.nr &gt;2 AND (A.q1 - tb1.betavg) &gt;0
ORDER BY diff DESC LIMIT 0 , 30
SELECT A.*
FROM    jos_bet_1x3_best A
   INNER JOIN
      (SELECT bet_id,
              q1,
              AVG(`q1`) AS betavg,
              COUNT(bet_id) AS nr,
              MAX(q1) AS qmax,
              (MAX(q1) - AVG(`q1`)) AS diff
         FROM jos_bet_1x3_best
       GROUP BY `bet_id`
       HAVING (q1 &gt;= betavg AND nr &gt; 2 AND diff &lt; .5 AND q1 &lt; 3)
        LIMIT 0, 30) AS tb1
   USING (bet_id)
GROUP BY `bet_id`;


Please try this.