优化H2查询:从表WHERE x = y中选择COUNT(*) [英] Optimize H2 query: SELECT COUNT(*) FROM table WHERE x=y

问题描述

我有一个如下查询:

EXPLAIN ANALYZE 
select count(*) from Table t 
where t.outcome='SUCCESS'

结果"列中有一个索引.

The 'outcome' column has an index.

H2告诉我它使用的是索引，但是由于大多数行都设置了"SUCCESS"，并且基数非常低，因此我仍然可以获得接近全表扫描的信息.

H2 tells me thats its using the index, but I still get something close to a full table scan, because most rows have 'SUCCESS' set, and cardinality is very low.

有没有办法加快速度? 顺便说一句，就像文档所说的那样，没有"WHERE"部分的查询非常快.

Is there a way to speed this up? Btw., queries without the 'WHERE' part are very fast, just as the doc says.

推荐答案

使用3个子查询应该起作用:

What should work is using 3 subqueries:

select
  (select count(*) from table) - 
  (select count(*) from table where outcome<'SUCCESS') -
  (select count(*) from table where outcome>'SUCCESS')
as count

这应该很快，因为第一部分是直接查找，而其他两个查询应该很快(因为大多数outcome通常是'SUCCESS').

This should be fast because the first part is a direct lookup, and the two other queries should be fast (because most outcome is usually 'SUCCESS').

如果没有，您能否获得查询计划并将其添加到问题中(解释分析选择...)?

If not, could you get the query plan and add it to the question (explain analyze select ...)?

这篇关于优化H2查询:从表WHERE x = y中选择COUNT(*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！