主页 / dba / 问题 / 4244
Accepted
Mokus
Asked: 2011-08-02 13:51:46 +0800 CST

MySQL 查询优化：赌注和平均数

  • 772

我有以下选择字符串：

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` ) >2 AND betavg <= q1
AND (qmax - MIN( q1 )) < 0.5 AND q1 <3)
ORDER BY `diff` DESC LIMIT 0 , 30

我可以以某种方式优化它吗？我想选择 q1 大于平均值的数据。

这是示例数据：

+-------+---------------+------+------+------+---------------------+-----------+
| 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 |
+-------+---------------+------+------+------+---------------------+-----------+

最后我在drex方案的基础上做了另外一个方案：

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 >2 AND (A.q1 - tb1.betavg) >0
ORDER BY diff DESC LIMIT 0 , 30
mysql query

2 个回答

  1. Best Answer
    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 >= betavg AND nr > 2 AND diff < .5 AND q1 < 3)
        LIMIT 0, 30) AS tb1
   USING (bet_id)
GROUP BY `bet_id`;

    请试试这个。

    • 3 
  2. ALTER TABLE jos_bet_1x3_best ADD INDEX (bet_id,id);
ALTER TABLE jos_bet_1x3_best ADD INDEX (bet_id,q1);
SELECT A.*
FROM
(
    SELECT AA.*,BB.q1 FROM
    (
        SELECT
            bet_id,
            COUNT(1) nr, 
            MAX(q1) qmax,
            AVG(q1) betavg,
            MAX(q1)-AVG(q1) diff
        FROM
            (SELECT bet_id,q1 FROM jos_bet_1x3_best) AAA
        GROUP BY
            bet_id
        HAVING
            (COUNT(1) > 2 AND betavg <= q1 AND
            (qmax-MIN(q1)) < 0.5 AND q1 < 3)
        ORDER BY
            diff DESC LIMIT 0,30
    ) AA INNER JOIN
    (
        SELECT bet_id,q1
        FROM jos_bet_1x3_best
    ) BB USING(bet_id)
) A INNER JOIN jos_bet_1x3_best USING (bet_id,q1);

    不确定这是否会加快速度，但无论如何都要试一试！！！

    • 1

相关问题