Re: Using OR in where clause does not use index

2012-10-23 Thread 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


On Thu, Oct 4, 2012 at 6:02 PM, BrianR brian@gmail.com wrote:

 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.


 On Wednesday, October 3, 2012 6:12:54 AM UTC-4, Vasile Rotaru wrote:

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

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

 instead of OR.

 On Tue, Oct 2, 2012 at 6:57 PM, BrianR bria...@gmail.com wrote:

 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.


  --
 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-H4Jhttps://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=enhttp://groups.google.com/group/h2-database?hl=en
 .

 --
Vasile Rotaru

  --
 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/-/Fy_NTkf0qGUJ.

 To post to this group, send email to h2-database@googlegroups.com.
 To unsubscribe from this group, send email to
 h2-database+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/h2-database?hl=en.


-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.



Re: Using OR in where clause does not use index

2012-10-04 Thread 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.

On Wednesday, October 3, 2012 6:12:54 AM UTC-4, Vasile Rotaru wrote:

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

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

 instead of OR.

 On Tue, Oct 2, 2012 at 6:57 PM, BrianR bria...@gmail.com javascript: 
 wrote: 

 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.


  -- 
 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.comjavascript:
 .
 To unsubscribe from this group, send email to 
 h2-database...@googlegroups.com javascript:.
 For more options, visit this group at 
 http://groups.google.com/group/h2-database?hl=en.

 -- 
Vasile Rotaru


-- 
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/-/Fy_NTkf0qGUJ.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.



Re: Using OR in where clause does not use index

2012-10-03 Thread vrotaru...@gmail.com
This was already discussed some time ago. A working workaround is to use

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

instead of OR.

On Tue, Oct 2, 2012 at 6:57 PM, BrianR brian@gmail.com wrote:

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.


  --
 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-database@googlegroups.com.
 To unsubscribe from this group, send email to
 h2-database+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/h2-database?hl=en.

-- 
   Vasile Rotaru

-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.



Using OR in where clause does not use index

2012-10-02 Thread 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.


-- 
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-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.