Ето пример:

SELECT
th.themeID,
th.themeNameEN,
ht.handlingID,
ht.handlingNameEN,
DATE(m.updated) AS updated,
COUNT(*) AS mainCount
FROM
v8_main AS m
INNER JOIN v8_themes AS th ON m.themeID = th.themeID
INNER JOIN v8_handling_types AS ht ON m.handlingID = ht.handlingID
WHERE
m.segmentID IN (4,5,6,1,3)
AND m.ph1008 IN (2,1,5,10,3,7,6,11,12,9,8,4)
AND m.handlingID IN (2,19,17,3,20,1,18,7,12,14,9,4,15,10,11,8,5)
AND DATE(m.updated) BETWEEN '2007-07-01' AND '2007-08-01'
GROUP BY
th.themeID,
ht.handlingID
ORDER BY
th.themeID,
ht.handlingNameEN;

# Query_time: 37 Lock_time: 0 Rows_sent: 71 Rows_examined: 1521661

---------------------------

SELECT
th.themeID,
th.themeNameEN,
ht.handlingID,
ht.handlingNameEN,
DATE(m.updated) AS updated,
COUNT(*) AS mainCount
FROM
v8_main AS m FORCE INDEX(monthStat_IDX)
INNER JOIN v8_themes AS th ON m.themeID = th.themeID
INNER JOIN v8_handling_types AS ht ON m.handlingID = ht.handlingID
WHERE
m.segmentID IN (4,5,6,1,3)
AND m.ph1008 IN (2,1,5,10,3,7,6,11,12,9,8,4)
AND m.handlingID IN (2,19,17,3,20,1,18,7,12,14,9,4,15,10,11,8,5)
AND DATE(m.updated) BETWEEN '2007-07-01' AND '2007-08-01'
GROUP BY
th.themeID,
ht.handlingID
ORDER BY
th.themeID,
ht.handlingNameEN;

# Query_time: 22 Lock_time: 0 Rows_sent: 0 Rows_examined: 1673826

monthStat индекса е на полетата `ph1008`, `handlingID`

Има три вида хора. Част от проблема, част от решението и част от пейзажа.

Редактирано от mr.vain на 02.08.07 16:44.