I'd imagine that all db systems use a full table scan with NOT LIKE.
How many possible values does className have? If there only a few, then you
should rewrite the query to be
... where className in ('foobar', 'barfoo', 'another one that doesn''t
start with opinion')
Or can you add a 'IS_OPINION' boolean column that is populated when you
create the table or insert into the table?
then with an index on that column you'd have extremely fast performance
with SELECT COUNT(*) FROM PUBLIC.OPINION WHERE NOT IS_OPINION
On Wednesday, 2 April 2014 17:21:44 UTC+2, Marcin Mirończuk wrote:
>
> Hi,
> I have a:
> - table opinion
> - index CREATE INDEX opinionClassNameIndex ON OPINION(CLASSNAME )
>
> This query is very fast and use index:
> select count(*) from opinion where className like 'opinion%';
> 307 ms
>
> SELECT
> COUNT(*)
> FROM PUBLIC.OPINION
> /* PUBLIC.OPINIONCLASSNAMEINDEX: CLASSNAME >= 'opinion'
> AND CLASSNAME < 'opinioo'
> */
> /* scanCount: 944400 */
> WHERE CLASSNAME LIKE 'opinion%'
> /*
> OPINION.OPINIONCLASSNAMEINDEX read: 18481
> */
>
> This query is very slow and not use index:
> select count(*) from opinion where className not like 'opinion%';
> 4826 ms
>
> SELECT
> COUNT(*)
> FROM PUBLIC.OPINION
> /* PUBLIC.OPINION.tableScan */
> /* scanCount: 1099380 */
> WHERE NOT (CLASSNAME LIKE 'opinion%')
> /*
> total: 545475
> OPINION.OPINION_DATA read: 545470 (99%)
> overflow read: 5 (0%)
> */
>
> Why NOT-LIKE is extremely slow? How I can improve it?
>
> Best
>
>
> Marcin M.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
