If you use EXPLAIN on both queries you will see that a table scan is used
for the second query while the index is used for the first.
Even if the query is changed to use WHERE CLASSNAME NOT LIKE 'opinion%'
(which is a cleaner way to write the query) a table scan is still used.
One option would be to first count all the records in the table and then
subtract the count of all the records that match WHERE CLASSNAME LIKE
'opinion%', so something like
SELECT SUM (val1) AS result
FROM
(
SELECT -1 * COUNT(*) AS val1 FROM PUBLIC.OPINION WHERE CLASSNAME
LIKE 'opinion%' // returns a negative value for the number of records
which match LIKE 'opinion%'
UNION ALL
SELECT COUNT(*) AS val1 FROM
PUBLIC.OPINION
// returns a positive value for the total number of records in the table
)
Roger
On Wednesday, April 2, 2014 4:21:44 PM UTC+1, 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.
