MySQL查询需要很长时间才能加入

问题描述：

I have the following mysql query which takes long time

SELECT `A`.*, max(B.timestamp) as timestamp2
FROM (`A`)
JOIN `B` ON `A`.`column1` = `B`.`column1`
WHERE `column2` =  'Player'
GROUP BY `column1`
ORDER BY `timestamp2` desc

I have index on TABLE A on column1 and indexes on table B are (column1,timestamp,column2),timestamp,column1.

When i use EXPLAIN it does not use timestamp index.

我有以下mysql查询需要很长时间 p>

   SELECT`A`。*，max（B.timestamp）as timestamp2 
FROM（`A`）
JOIN`B`ON`A``column1` =`B``column1` 
WHERE`column2` ='  Player'
GROUP BY`column1` 
ORDER BY`timestamp2`desc 
  code>  pre> 
 
 我在column1上的表A上有索引，在表B上有索引（column1，timestamp）  ，column2），timestamp，column1。 p> 
 
 
当我使用EXPLAIN时，它不使用时间戳索引。 p> 
  div>

答

Try adding an index...

  ... ON `B` (`column2`,`column1`,`timestamp`)

with the columns in that order.

Without any information about datatype, we're going to guess that column2 is character type (and we're going to assume that the column is in table B, given the information about the current indexes.)

Absent any information about cardinality, we're going to guess that the number of rows that satisfy the equality predicate on column2 (in the WHERE clause) is a small subset of the total rows in B.

We expect that MySQL will use of a "range" scan operation, using an index that has column2 as a leading column.

Given that the new index is a "covering" index for the query, we also expect the EXPLAIN output to show "Using index" in the Extra column.

We also expect that MySQL can use the index to satisfy the GROUP BY operation and the MAX aggregate, without requiring a filesort operation.

But we are still going to see a filesort operation, used to satisfy the ORDER BY.

MySQL查询需要很长时间才能加入

相关推荐