“分组”的另一种方法是避免低性能速度？

Question

I want to group by b.hash however it is too costly to do it. Is there any way to have this query but also with group by but faster?

SELECT STRAIGHT_JOIN b.hash, b.page, n.favorite
FROM behaviour b, new_table n
WHERE b.timestamp >= NOW( ) - INTERVAL 20 SECOND 
AND b.hash = n.hash

This is the EXPLAIN

PROFILING

Answer 1

I'm guessing you want this query

SELECT b.hash, b.page, n.favorite
  FROM behaviour b
  JOIN new_table n ON b.hash = n.hash
 WHERE b.timestamp >= NOW( ) - INTERVAL 20 SECOND 
   AND b.hash = n.hash
 ORDER BY b.hash

I don't understand your remark about GROUP BY in your question. It looks like you want the most recent third of a minute's worth of items.

At any rate, if you create a compound index on your behaviour table containing the following columns, your query will probably start running acceptably fast.

 (timestamp, hash, page)

Why? MySQL's query planner can random-access the index to start at the timestamp value in the query. That's O(log n) quick. Then it can scan the index sequentially for the information needed by your query. That's almost instantaneous.

Is it possible you want this?

SELECT b.hash, b.page, MAX(n.favorite)
  FROM behaviour b
  JOIN new_table n ON b.hash = n.hash
 WHERE b.timestamp >= NOW( ) - INTERVAL 20 SECOND 
   AND b.hash = n.hash
 GROUP BY b.hash, b.page

Or this?

SELECT b.hash, b.page, GROUP_CONCAT(n.favorite)
  FROM ...