Steven Roussey wrote:
ORing on two different fields is what I have been asking about :).
This is not optimized, and I don't think it is set to be optimized
until 5.1 (as per someone else's comment).
Using a composite index was suggested
This is bad information. It works for AND, not for OR.
Joshua,
Joshua Spoerri wrote:
Forgive me, that example is no good.
Oddly, it works, but the following does not:
mysql create temporary table x (y int, z int, q int, index (y, z)); insert into x
values (1,2,3), (3,4,5), (5,6,7); explain select * from x where y = 1 or z = 1;
MySQL will never use
Joshua Spoerri wrote:
On Tue, 5 Aug 2003, gerald_clark wrote:
You are ORing on two different fields. The index cannot be used to
check the value of z for an OR.
ORing on two different fields is what I have been asking about :).
Using a composite index was suggested, which strangely
I think I have a similar problem... I am thinking abeout switching to a
different DBMS. Can anyone tell me something about PostgreSQL?
Thanks in advance,
Martin.
Am Donnerstag, 07.08.03, um 03:32 Uhr (Europe/Zurich) schrieb Steven
Roussey:
ORing on two different fields is what I have been
ORing on two different fields is what I have been asking about :).
This is not optimized, and I don't think it is set to be optimized until
5.1 (as per someone else's comment).
Using a composite index was suggested
This is bad information. It works for AND, not for OR.
You have two
Joshua,
Joshua Spoerri wrote:
On Tue, 5 Aug 2003, Alexander Keremidarski wrote:
MySQL will never use any index for small tables. With just few rows using index
adds overhead only. Table scan is faster in such cases. This is docummented behaviour.
is 100,000 rows small? my simple OR queries
You're saying that when you try my example, it does use the composite
index? Even with an extra column in the table that isn't being searched on
(q below)?
If so, do you know of anything in version 4.0.13 that could cause this bad
behaviour? i'm using the default configuration unchanged.
On Tue,
On Tue, 5 Aug 2003, gerald_clark wrote:
You are ORing on two different fields. The index cannot be used to
check the value of z for an OR.
ORing on two different fields is what I have been asking about :).
Using a composite index was suggested, which strangely seems to work
only when there are
Thanks for the suggestion.
This is not ideal for a couple of reasons: I'm using an object-relational
layer that would have to be hacked up something fierce, and my actual
query would be pretty hairy:
(select * from t1 where a=x
union select t1.* from t1,t2 where t1.b=t2.b and t2.c=y
union select
You are ORing on two different fields. The index cannot be used to
check the value of z for an OR.
Why are you cross posting?
Joshua Spoerri wrote:
On Tue, 5 Aug 2003, Alexander Keremidarski wrote:
MySQL will never use any index for small tables. With just few rows using index
adds overhead
On Tue, 5 Aug 2003, Alexander Keremidarski wrote:
MySQL will never use any index for small tables. With just few rows using index
adds overhead only. Table scan is faster in such cases. This is docummented
behaviour.
is 100,000 rows small? my simple OR queries take longer than a second.
Joshua,
Joshua Spoerri wrote:
Which version is targetted for optimization of OR searching on two keys,
that is, select * from sometable where f1 = 123 or f2 = 123,
as described in http://www.mysql.com/doc/en/Searching_on_two_keys.html
?
As described there MySQL can use only one index per table
-4.0.13-0
(Thanks for your help)
On Mon, 4 Aug 2003, Alexander Keremidarski wrote:
Joshua,
Joshua Spoerri wrote:
Which version is targetted for optimization of OR searching on two keys,
that is, select * from sometable where f1 = 123 or f2 = 123,
as described in http://www.mysql.com/doc/en
Keremidarski wrote:
Joshua,
Joshua Spoerri wrote:
Which version is targetted for optimization of OR searching on two keys,
that is, select * from sometable where f1 = 123 or f2 = 123,
as described in http://www.mysql.com/doc/en/Searching_on_two_keys.html
?
As described
Have you tried using the UNION statement? That worked great for me.
So, something like:
(select * from sometable where f1 = 123)
UNION
(select * from sometable where f2 = 123)
?
--
Rudi Benkovic [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives:
Which version is targetted for optimization of OR searching on two keys,
that is, select * from sometable where f1 = 123 or f2 = 123,
as described in http://www.mysql.com/doc/en/Searching_on_two_keys.html
?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
16 matches
Mail list logo
