Re: Searching on Two Keys with OR?
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. You have two workarounds: temp tables and unions. By a twitch of algabra, how about ... select * from sometable where not( f1 != 123 and f2 != 123 ), ... at least it gives an AND to optimize. Then again, I maight be too optimistic. HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with 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 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. Best regards -- Are you MySQL certified? - http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
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 seems to work only when there are no other columns in the table. When there are no other columns in the table, it can scan the complete index file to satisfy the query. If there are other fields, it would have to scan the entire index file, and then access the data file to pick up the other fields. It is faster to just scan the entire data file. Why are you cross posting? Initially because I didn't know which list was appropriate, and later in response. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
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 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 workarounds: temp tables and unions. You can have it use one index, though. And you can give it a hint on which index it ought to use if you think you know better than the optimizer. At any rate, this is one of MySQL's deficiencies that many of us have worked around for a long time. --steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
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 workarounds: temp tables and unions. You can have it use one index, though. And you can give it a hint on which index it ought to use if you think you know better than the optimizer. At any rate, this is one of MySQL's deficiencies that many of us have worked around for a long time. --steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
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 take longer than a second. No. It is not! I referred to your test case in your previous email: 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 create temporary table x (y int, z int, index (y, z)); insert into x select f1,f2 from myrealtable; alter table x add q int; explain select * from x where y = 1 or z = 1; Query OK, 0 rows affected (0.00 sec) Query OK, 101200 rows affected (1.95 sec) Records: 101200 Duplicates: 0 Warnings: 0 Query OK, 101200 rows affected (1.61 sec) Records: 101200 Duplicates: 0 Warnings: 0 +---+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++-+ | x | ALL | y | NULL |NULL | NULL | 101200 | Using where | +---+--+---+--+-+--++-+ 1 row in set (0.00 sec) Same table: mysql explain select * from x where y = 1 or z = 1; +---+---+---+--+-+--+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+--+---+-+ | x | index | y | y| 10 | NULL | 85971 | where used; Using index | +---+---+---+--+-+--+---+-+ Note that I am using MySQL 3.23.57, 4.0.14 and 4.1.0 Best regards -- Are you MySQL certified? - http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
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, 5 Aug 2003, Alexander Keremidarski wrote: 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 take longer than a second. No. It is not! I referred to your test case in your previous email: 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 create temporary table x (y int, z int, index (y, z)); insert into x select f1,f2 from myrealtable; alter table x add q int; explain select * from x where y = 1 or z = 1; Query OK, 0 rows affected (0.00 sec) Query OK, 101200 rows affected (1.95 sec) Records: 101200 Duplicates: 0 Warnings: 0 Query OK, 101200 rows affected (1.61 sec) Records: 101200 Duplicates: 0 Warnings: 0 +---+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++-+ | x | ALL | y | NULL |NULL | NULL | 101200 | Using where | +---+--+---+--+-+--++-+ 1 row in set (0.00 sec) Same table: mysql explain select * from x where y = 1 or z = 1; +---+---+---+--+-+--+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+--+---+-+ | x | index | y | y| 10 | NULL | 85971 | where used; Using index | +---+---+---+--+-+--+---+-+ Note that I am using MySQL 3.23.57, 4.0.14 and 4.1.0 Best regards -- Are you MySQL certified? - http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
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 no other columns in the table. Why are you cross posting? Initially because I didn't know which list was appropriate, and later in response. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
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 t1.* from t1,t2 where t1.b=t2.b and t2.d=z ) order by e I guess this is what a good optimizer would generate behind the scenes, but it'd be nice not to have to know about it. On Tue, 5 Aug 2003, Rudi Benkovic wrote: 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: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
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 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. mysql create temporary table x (y int, z int, index (y, z)); insert into x select f1,f2 from myrealtable; alter table x add q int; explain select * from x where y = 1 or z = 1; Query OK, 0 rows affected (0.00 sec) Query OK, 101200 rows affected (1.95 sec) Records: 101200 Duplicates: 0 Warnings: 0 Query OK, 101200 rows affected (1.61 sec) Records: 101200 Duplicates: 0 Warnings: 0 +---+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++-+ | x | ALL | y | NULL |NULL | NULL | 101200 | Using where | +---+--+---+--+-+--++-+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
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. mysql create temporary table x (y int, z int, index (y, z)); insert into x select f1,f2 from myrealtable; alter table x add q int; explain select * from x where y = 1 or z = 1; Query OK, 0 rows affected (0.00 sec) Query OK, 101200 rows affected (1.95 sec) Records: 101200 Duplicates: 0 Warnings: 0 Query OK, 101200 rows affected (1.61 sec) Records: 101200 Duplicates: 0 Warnings: 0 +---+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++-+ | x | ALL | y | NULL |NULL | NULL | 101200 | Using where | +---+--+---+--+-+--++-+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
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. It will work same way untill next major Optimizer update which is scheduled for 5.1 Meanwhile your query can be optimized with using of composite index over both columns: mysql explain select * from t where f1 = 10 or f2 = 10; +---+---+---+--+-+--+---+--+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+--+---+--+ | t | index | f1| f1 | 10 | NULL | 16384 | Using where; Using index | +---+---+---+--+-+--+---+--+ Thanks -- Are you MySQL certified? - http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
That doesn't seem to work (and not with bigger table either): mysql create temporary table x (y int, z int, index (y), index(z)); insert into x values (1,2), (3,4), (5,6); explain select * from x where y = 1 or z = 1; Query OK, 0 rows affected (0.00 sec) Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 +---+--+---+--+-+--+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+-+ | x | ALL | y,z | NULL |NULL | NULL |3 | Using where | +---+--+---+--+-+--+--+-+ 1 row in set (0.00 sec) mysql quit Bye $ rpm -qa | grep -i mysql perl-DBD-MySQL-1.2216-4 MySQL-devel-4.0.13-0 MySQL-client-4.0.13-0 MySQL-server-4.0.13-0 MySQL-shared-compat-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/Searching_on_two_keys.html ? As described there MySQL can use only one index per table. It will work same way untill next major Optimizer update which is scheduled for 5.1 Meanwhile your query can be optimized with using of composite index over both columns: mysql explain select * from t where f1 = 10 or f2 = 10; +---+---+---+--+-+--+---+--+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+--+---+--+ | t | index | f1| f1 | 10 | NULL | 16384 | Using where; Using index | +---+---+---+--+-+--+---+--+ Thanks -- Are you MySQL certified? - http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
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; Query OK, 0 rows affected (0.00 sec) Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 +---+--+---+--+-+--+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+-+ | x | ALL | y | NULL |NULL | NULL |3 | Using where | +---+--+---+--+-+--+--+-+ 1 row in set (0.00 sec) On Mon, 4 Aug 2003, Joshua Spoerri wrote: That doesn't seem to work (and not with bigger table either): mysql create temporary table x (y int, z int, index (y), index(z)); insert into x values (1,2), (3,4), (5,6); explain select * from x where y = 1 or z = 1; Query OK, 0 rows affected (0.00 sec) Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 +---+--+---+--+-+--+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+-+ | x | ALL | y,z | NULL |NULL | NULL |3 | Using where | +---+--+---+--+-+--+--+-+ 1 row in set (0.00 sec) mysql quit Bye $ rpm -qa | grep -i mysql perl-DBD-MySQL-1.2216-4 MySQL-devel-4.0.13-0 MySQL-client-4.0.13-0 MySQL-server-4.0.13-0 MySQL-shared-compat-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/Searching_on_two_keys.html ? As described there MySQL can use only one index per table. It will work same way untill next major Optimizer update which is scheduled for 5.1 Meanwhile your query can be optimized with using of composite index over both columns: mysql explain select * from t where f1 = 10 or f2 = 10; +---+---+---+--+-+--+---+--+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+--+---+--+ | t | index | f1| f1 | 10 | NULL | 16384 | Using where; Using index | +---+---+---+--+-+--+---+--+ Thanks -- Are you MySQL certified? - http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
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: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Searching on Two Keys with OR?
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 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
