Using OR in where clause does not use index

[BrianR]

If column a is index and running a query of 

SELECT * FROM tableFoo WHERE a = 'value' OR a LIKE 'value2'

does not use the index on a.

I also noticed the following query does not use an index

SELECT * FROM tableFoo WHERE a = 'value' OR 1=1

This may be by design but probably should be documented somewhere.

[vrota...@gmail.com]

This was already discussed some time ago. A working workaround is to use

    (SELECT ..) UNION (SELECT ...)

instead of OR.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/GjKh5-2c-H4J.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

--
   Vasile Rotaru

[BrianR]

I understand the workaround. 

Shouldn't this information be on the h2 site somewhere (and not only in the forums)?

The same with the fact that REGEXP does not use indicies. I'm worried that there are other commands that do not use indicies and I dont know about them because they are in some posting and not in the documentation.

[Thomas Mueller]

Hi,

Most optimizations are documented at http://h2database.com/html/performance.html#explain_plan - but I agree the documentation could be improved, patches are welcome. 

Regards,

Thomas

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/Fy_NTkf0qGUJ.