Re: Is there a better way than this?
Thanks for the replies! Chris, yeah, that's the first thing I tried. The problem though is that SQL statement also returns Row #2 (x=1, y=25) since y=25 is associated with both x=1 and x=2. I want it only to return row #3. As John said, it may not be possible with a simple SQL statement. My table is used as a mapping table for an M to N relationship similar as described here: http://stackoverflow.com/questions/1680855/sql-select-with-mn-relationship My idea was to get a set of Xs with SELECT X FROM `A` WHERE Y IN (25) and another set of Xs with SELECT X FROM `A` WHERE Y IN (24) ,then return the common elements between the two sets and use THAT set to query X again. Like I said, that approach works but I thought there might be a more elegant way. Unless I am missing something, this should work. SELECT DISTINCT X FROM `A` WHERE Y IN (25) AND Y NOT IN (24) Chris W Tim Molter wrote: I'm new to MySQL and I'm looking for some guidance. I have a table A, with two columns X and Y with the following data: | X | Y | 1 24 1 25 2 25 2 26 3 27 I want my SQL query to return 2 following this verbose logic: SELECT DISTINCT X FROM A WHERE Y equals 25 and Y also does NOT equal 24. I came up with the following SQL, which gives me my desired result, but is there a better way to do it? Can it be achieved using MINUS or UNION somehow? BTW, I'm using IN here because I intend to replace the single numbers (24 and 25) with arrays that have 0 to N members. SELECT DISTINCT X FROM `A` WHERE X IN ( SELECT X FROM `A` WHERE Y IN (25) ) AND X NOT IN ( SELECT X FROM `A` WHERE Y IN (24) ) Thanks! -- ~Tim http://obscuredclarity.blogspot.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why does this query take so long?
Even weirder, I came back to my laptop a couple hours later. And now the same
queries are taking 3-10 seconds instead of 0.01 seconds. What could be causing
this?
On 2009-12-28, at 1:19 PM, René Fournier wrote:
Hmm, weird. I just re-imported the data (after drop/create table, etc.), and
now the spatial queries run fast.
Has anyone seen this sort of thing happen? Maybe the Index got corrupted
somehow, and then MySQL had to do a full table scan (even though EXPLAIN
indicated it would use the Spatial Index)?
On 2009-12-28, at 9:28 AM, René Fournier wrote:
So just to clarify (hello?), the index which *should* be used (EXPLAIN says
so) and *should* make the query run faster than 4 seconds either isn't used
(why?) or simply doesn't speed up the query (again, why?).
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL
| 5260 | Using where |
++-+---+---+---+---+-+--+--+-+
SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m,
quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589
-114.82248918,51.65126254 -114.82248918,51.65126254
-114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'),
coordinates)
8 rows in set (3.87 sec)
On 2009-12-27, at 3:59 PM, René Fournier wrote:
So... there is an index, and it's supposedly used:
mysql EXPLAIN SELECT id, province, latitude, longitude,
AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates);
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL
| 5260 | Using where |
++-+---+---+---+---+-+--+--+-+
1 row in set (0.00 sec)
But when I run the query:
mysql SELECT id, province, latitude, longitude, AsText(coordinates),
s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates)
- ;
++--+-+---+--+--++
| id | province | latitude| longitude | AsText(coordinates)
| s_ts_r_m | quartersection |
++--+-+---+--+--++
| 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228
-114.79282412) | 04-031-06 W5 | N4 |
| 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112
-114.79283278) | 09-031-06 W5 | N4 |
| 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789
-114.81645649) | 05-031-06 W5 | N4 |
| 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119
-114.81643801) | 08-031-06 W5 | N4 |
| 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568
-114.80475858) | 05-031-06 W5 | E4 |
| 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028
-114.80479925) | 05-031-06 W5 | NE |
| 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442
-114.80478262) | 08-031-06 W5 | E4 |
| 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854
-114.80476596) | 08-031-06 W5 | NE |
++--+-+---+--+--++
8 rows in set (3.87 sec)
So, there are ~2.6 million rows in the table, and coordinates is
spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing
wrong?
...REne
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub...@renefournier.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Database fundamentals: wanna learn.
Hey, all. I've been using databases clear back to xBase days; that being said, I've never had a solid foundation for relational databases. While I can muddle by in SQL, I really don't have a good understanding of exactly how keys are set up, the underpinnings of indexing, and, oh, lots of ground-level stuff. Call me a user, and you'd be right -- an administrator of databases? Not so much. So, any suggestions -- books, courses, web sites, what-have-you -- that I should be hitting up so I can have a better grasp of what's going on behind the scenes? Thanks! -Ken -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance
Jerry Schwartz wrote: -Original Message- From: Edward S.P. Leong [mailto:edward...@ita.org.mo] Sent: Thursday, December 17, 2009 7:25 AM To: mysql@lists.mysql.com Subject: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance Dear all, Would you mind to give me the suggestion ? I want to use 32bit php and mysql server on 64bit Windows 2003 Server... So, is it possible ( good for work also ) ? [JS] I'm using that combination on 64-bit Vista. The last time I checked, there was no 64-bit Windows build of PHP. Besides, the database engine doesn't (shouldn't) care who it's talking to. Dear Jerry, In our exiting Server, it is running with 4.4.x php and 4.1.x MySQL in Windows 2000 OS... Now, we are planning to upgrade the Windows OS ( of course include Hardware )... BUT I don't know which new version of php and MySQL is compatiable with the source code ( old ) of php and MySQL... So, would you mind to share your experience with us(me) ? Anyone can help the problem ? Thanks ! Edward.
Re: Database fundamentals: wanna learn.
Hi Ken, thanks for sharing! If you want to start from scratch, I would go for a book like this: http://www.amazon.com/SQL-Complete-Reference-James-Groff/dp/0071592555/ref=dp_ob_title_bk I did not 'read' it thru, but this is the one I would buy. If you want to embrace MySQL, in my opinion, the best book you can get is MySQL 4th edition by Paul DuBois. It's complete, even more, very readable, and it can be with you for a long time. But if you need to grasp better the basic concepts go for a generik SQL book(like the top one), then go with MySQL specific if you want, or others. Cheers Claudio 2009/12/28 Ken D'Ambrosio k...@jots.org Hey, all. I've been using databases clear back to xBase days; that being said, I've never had a solid foundation for relational databases. While I can muddle by in SQL, I really don't have a good understanding of exactly how keys are set up, the underpinnings of indexing, and, oh, lots of ground-level stuff. Call me a user, and you'd be right -- an administrator of databases? Not so much. So, any suggestions -- books, courses, web sites, what-have-you -- that I should be hitting up so I can have a better grasp of what's going on behind the scenes? Thanks! -Ken -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: Database fundamentals: wanna learn.
Ken, So, any suggestions -- books, courses, web sites, what-have-you -- that I should be hitting up so I can have a better grasp of what's going on behind the scenes? http://dev.mysql.com/doc/refman/5.5/en/tutorial.html Start at top left and work your way downwards rightwards at http://www.artfulsoftware.com/dbresources.html. PB - Ken D'Ambrosio wrote: Hey, all. I've been using databases clear back to xBase days; that being said, I've never had a solid foundation for relational databases. While I can muddle by in SQL, I really don't have a good understanding of exactly how keys are set up, the underpinnings of indexing, and, oh, lots of ground-level stuff. Call me a user, and you'd be right -- an administrator of databases? Not so much. So, any suggestions -- books, courses, web sites, what-have-you -- that I should be hitting up so I can have a better grasp of what's going on behind the scenes? Thanks! -Ken No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.431 / Virus Database: 270.14.122/2590 - Release Date: 12/28/09 07:16:00
Re: Database fundamentals: wanna learn.
I have two of Paul's books. They are both fantastic. Mike O'Krongli President and CTO Acorg Inc 519 432-1185 - Original Message - From: Claudio Nanni claudio.na...@gmail.com To: Ken D'Ambrosio k...@jots.org Cc: mysql mysql@lists.mysql.com Sent: Monday, December 28, 2009 10:33 AM Subject: Re: Database fundamentals: wanna learn. Hi Ken, thanks for sharing! If you want to start from scratch, I would go for a book like this: http://www.amazon.com/SQL-Complete-Reference-James-Groff/dp/0071592555/ref=dp_ob_title_bk I did not 'read' it thru, but this is the one I would buy. If you want to embrace MySQL, in my opinion, the best book you can get is MySQL 4th edition by Paul DuBois. It's complete, even more, very readable, and it can be with you for a long time. But if you need to grasp better the basic concepts go for a generik SQL book(like the top one), then go with MySQL specific if you want, or others. Cheers Claudio 2009/12/28 Ken D'Ambrosio k...@jots.org Hey, all. I've been using databases clear back to xBase days; that being said, I've never had a solid foundation for relational databases. While I can muddle by in SQL, I really don't have a good understanding of exactly how keys are set up, the underpinnings of indexing, and, oh, lots of ground-level stuff. Call me a user, and you'd be right -- an administrator of databases? Not so much. So, any suggestions -- books, courses, web sites, what-have-you -- that I should be hitting up so I can have a better grasp of what's going on behind the scenes? Thanks! -Ken -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance
From: Edward S.P. Leong [mailto:edward...@ita.org.mo] Sent: Monday, December 28, 2009 9:25 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance Jerry Schwartz wrote: -Original Message- From: Edward S.P. Leong [mailto:edward...@ita.org.mo] Sent: Thursday, December 17, 2009 7:25 AM To: mysql@lists.mysql.com Subject: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance Dear all, Would you mind to give me the suggestion ? I want to use 32bit php and mysql server on 64bit Windows 2003 Server... So, is it possible ( good for work also ) ? [JS] I'm using that combination on 64-bit Vista. The last time I checked, there was no 64-bit Windows build of PHP. Besides, the database engine doesn't (shouldn't) care who it's talking to. Dear Jerry, In our exiting Server, it is running with 4.4.x php and 4.1.x MySQL in Windows 2000 OS... Now, we are planning to upgrade the Windows OS ( of course include Hardware )... BUT I don't know which new version of php and MySQL is compatiable with the source code ( old ) of php and MySQL... So, would you mind to share your experience with us(me) ? [JS] Unfortunately, I’m not sure I can help you. Our databases were fairly small (under 2GB total), so I simply did a mysqldump from the old system (Linux) and imported it into the new one (Vista). PHP is going to be more of a problem. There are some compatibility issues between 4.x and 5.x, and more between 4.x and 6.x. We were already running 5.x, so I didn’t have a lot of trouble with that. You can probably get things running, but I strongly suggest that you read up on the newer versions of PHP. There are some changes between 5.x and 5.y that you need to pay attention to. The biggest issue will probably be the deprecation of short tags. There is a setting in php.ini that you can change to allow them. Anyone can help the problem ? Thanks ! Edward.
Re: Database fundamentals: wanna learn.
Hey, all. I've been using databases clear back to xBase days; that being said, I've never had a solid foundation for relational databases. While I can muddle by in SQL, I really don't have a good understanding of exactly how keys are set up, the underpinnings of indexing, and, oh, lots of ground-level stuff. Call me a user, and you'd be right -- an administrator of databases? Not so much. So, any suggestions -- books, courses, web sites, what-have-you -- that I should be hitting up so I can have a better grasp of what's going on behind the scenes? I'd suggest you start learning about relational theory first, SQL later, any specific DBMS after that (or together with SQL). Indexing, for example, has nothing to do per-se with relational databases ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Database fundamentals: wanna learn.
Ken D'Ambrosio wrote: Hey, all. I've been using databases clear back to xBase days; that being said, I've never had a solid foundation for relational databases. While I can muddle by in SQL, I really don't have a good understanding of exactly how keys are set up, the underpinnings of indexing, and, oh, lots of ground-level stuff. Call me a user, and you'd be right -- an administrator of databases? Not so much. So, any suggestions -- books, courses, web sites, what-have-you -- that I should be hitting up so I can have a better grasp of what's going on behind the scenes? I recently attended Sun's MySQL DBA course (http://www.mysql.com/training/schedule.php?class=5200) which I can heartily recommend. The course covers the kind of things you're after, including indexing, how the engines work (ie pros and cons), backups, etc. Cheers, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is there a better way than this?
Gavin, very nice, - michael dykman On Mon, Dec 28, 2009 at 2:16 PM, Gavin Towey gto...@ffn.com wrote: No, that won't work, remember that the WHERE clause is applied to each row individually -- y is 25, then it also cannot possibly be 24 at the same time, so AND condition has no meaning there. What you're asking for there is the set of all x that have 25 as a y value, which is 1 and 2. You need to use aggregates to create conditions that are meaningful for all x with the same value: SELECT x FROM a GROUP BY x HAVING sum(y=25) and not sum(y=24); Regards, Gavin Towey -Original Message- From: Chris W [mailto:4rfv...@cox.net] Sent: Sunday, December 27, 2009 6:02 PM To: Tim Molter Cc: mysql@lists.mysql.com Subject: Re: Is there a better way than this? Unless I am missing something, this should work. SELECT DISTINCT X FROM `A` WHERE Y IN (25) AND Y NOT IN (24) Chris W Tim Molter wrote: I'm new to MySQL and I'm looking for some guidance. I have a table A, with two columns X and Y with the following data: | X | Y | 1 24 1 25 2 25 2 26 3 27 I want my SQL query to return 2 following this verbose logic: SELECT DISTINCT X FROM A WHERE Y equals 25 and Y also does NOT equal 24. I came up with the following SQL, which gives me my desired result, but is there a better way to do it? Can it be achieved using MINUS or UNION somehow? BTW, I'm using IN here because I intend to replace the single numbers (24 and 25) with arrays that have 0 to N members. SELECT DISTINCT X FROM `A` WHERE X IN ( SELECT X FROM `A` WHERE Y IN (25) ) AND X NOT IN ( SELECT X FROM `A` WHERE Y IN (24) ) Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Is anything ever equal to NULL?
Will anything ever be equal to NULL in a SELECT query? SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? -- Dante -- D. Dante Lorenso -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
No, nothing will ever equal null. In strict relational theory, which I don't know well enough to begin expounding on here, null does not even equal another null. That's why SQL provides IS NULL and IS NOT NULL as explicit cases. - michael dykman On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso da...@lorenso.com wrote: Will anything ever be equal to NULL in a SELECT query? SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? -- Dante -- D. Dante Lorenso -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso da...@lorenso.com wrote: Will anything ever be equal to NULL in a SELECT query? SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html Should answer some of your questions, Dante.
RE: Is there a better way than this?
No, that won't work, remember that the WHERE clause is applied to each row individually -- y is 25, then it also cannot possibly be 24 at the same time, so AND condition has no meaning there. What you're asking for there is the set of all x that have 25 as a y value, which is 1 and 2. You need to use aggregates to create conditions that are meaningful for all x with the same value: SELECT x FROM a GROUP BY x HAVING sum(y=25) and not sum(y=24); Regards, Gavin Towey -Original Message- From: Chris W [mailto:4rfv...@cox.net] Sent: Sunday, December 27, 2009 6:02 PM To: Tim Molter Cc: mysql@lists.mysql.com Subject: Re: Is there a better way than this? Unless I am missing something, this should work. SELECT DISTINCT X FROM `A` WHERE Y IN (25) AND Y NOT IN (24) Chris W Tim Molter wrote: I'm new to MySQL and I'm looking for some guidance. I have a table A, with two columns X and Y with the following data: | X|Y| 1 24 1 25 2 25 2 26 3 27 I want my SQL query to return 2 following this verbose logic: SELECT DISTINCT X FROM A WHERE Y equals 25 and Y also does NOT equal 24. I came up with the following SQL, which gives me my desired result, but is there a better way to do it? Can it be achieved using MINUS or UNION somehow? BTW, I'm using IN here because I intend to replace the single numbers (24 and 25) with arrays that have 0 to N members. SELECT DISTINCT X FROM `A` WHERE X IN ( SELECT X FROM `A` WHERE Y IN (25) ) AND X NOT IN ( SELECT X FROM `A` WHERE Y IN (24) ) Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL slave master replication
I know the master/slave replication scheme for MySQL is pretty easy to set up; I'm doing it lab now. My question is does anyone know if it will successfully replicate foreign key constraints and large BLOB date types. Any feedback on this would be gratefully appreciated. _ Jamie DelleMonache | Solutions Specialist MEI | 610 Old York Rd, Suite 250 | Jenkintown, PA 19046 P: 215-886-5662 x282 | F: 215-886-5681 E-Mail: jdellemona...@maned.com http://www.maned.com
Re: Is anything ever equal to NULL?
Hi, Will anything ever be equal to NULL in a SELECT query? No, never. Null also means unknown, if you design your tables well enough, there should be no NULLs -stored- (different from a resultset, where there can be nulls, for example in LEFT JOINs), because it's no use to store what you don't know. The only case when you want to store a null is when you do want to -know- you don't know a value. A column can have two states: null or not null. It either has data (a value, depending on the datatype), or no data (null), which is where column IS NULL (has no data) or column IS NOT NULL (has data) comes into play. Null is not the same as empty. An empty string, for example, is not equal to null (which is unknown), you cannot compare anything to what you don't know, which is why your comparison fails. NULL = NULL fails, so does NULL NULL in the strict sense. SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: You mean the optimizer, perhaps it should, perhaps it shouldn't. Yet, your query is not really the smartest, as you should avoid writing column = NULL, as this doesn't make sense. SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? Quite a bit ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why does this query take so long?
It sounds like your laptop might be paging mysql's memory to disk or something
like that. Your laptop may not be the most reliable source for benchmarks.
Regards,
Gavin Towey
-Original Message-
From: René Fournier [mailto:m...@renefournier.com]
Sent: Monday, December 28, 2009 2:16 AM
To: René Fournier
Cc: mysql
Subject: Re: Why does this query take so long?
Even weirder, I came back to my laptop a couple hours later. And now the same
queries are taking 3-10 seconds instead of 0.01 seconds. What could be causing
this?
On 2009-12-28, at 1:19 PM, René Fournier wrote:
Hmm, weird. I just re-imported the data (after drop/create table, etc.), and
now the spatial queries run fast.
Has anyone seen this sort of thing happen? Maybe the Index got corrupted
somehow, and then MySQL had to do a full table scan (even though EXPLAIN
indicated it would use the Spatial Index)?
On 2009-12-28, at 9:28 AM, René Fournier wrote:
So just to clarify (hello?), the index which *should* be used (EXPLAIN says
so) and *should* make the query run faster than 4 seconds either isn't used
(why?) or simply doesn't speed up the query (again, why?).
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL
| 5260 | Using where |
++-+---+---+---+---+-+--+--+-+
SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m,
quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589
-114.82248918,51.65126254 -114.82248918,51.65126254
-114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'),
coordinates)
8 rows in set (3.87 sec)
On 2009-12-27, at 3:59 PM, René Fournier wrote:
So... there is an index, and it's supposedly used:
mysql EXPLAIN SELECT id, province, latitude, longitude,
AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates);
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL
| 5260 | Using where |
++-+---+---+---+---+-+--+--+-+
1 row in set (0.00 sec)
But when I run the query:
mysql SELECT id, province, latitude, longitude, AsText(coordinates),
s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates)
- ;
++--+-+---+--+--++
| id | province | latitude| longitude | AsText(coordinates)
| s_ts_r_m | quartersection |
++--+-+---+--+--++
| 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228
-114.79282412) | 04-031-06 W5 | N4 |
| 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112
-114.79283278) | 09-031-06 W5 | N4 |
| 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789
-114.81645649) | 05-031-06 W5 | N4 |
| 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119
-114.81643801) | 08-031-06 W5 | N4 |
| 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568
-114.80475858) | 05-031-06 W5 | E4 |
| 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028
-114.80479925) | 05-031-06 W5 | NE |
| 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442
-114.80478262) | 08-031-06 W5 | E4 |
| 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854
-114.80476596) | 08-031-06 W5 | NE |
++--+-+---+--+--++
8 rows in set (3.87 sec)
So, there are ~2.6 million rows in the table, and coordinates is
spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing
wrong?
...REne
--
MySQL General Mailing List
For list archives:
Re: Is there a better way than this?
This will work: select distinct X from a as a where Y in(25) and not exists (select X from a as b where a.X = b.X and b.Y in(24)) - Original Message - From: Tim Molter tim.mol...@gmail.com To: mysql@lists.mysql.com Sent: Sunday, December 27, 2009 4:04 PM Subject: Is there a better way than this? I'm new to MySQL and I'm looking for some guidance. I have a table A, with two columns X and Y with the following data: | X|Y| 1 24 1 25 2 25 2 26 3 27 I want my SQL query to return 2 following this verbose logic: SELECT DISTINCT X FROM A WHERE Y equals 25 and Y also does NOT equal 24. I came up with the following SQL, which gives me my desired result, but is there a better way to do it? Can it be achieved using MINUS or UNION somehow? BTW, I'm using IN here because I intend to replace the single numbers (24 and 25) with arrays that have 0 to N members. SELECT DISTINCT X FROM `A` WHERE X IN ( SELECT X FROM `A` WHERE Y IN (25) ) AND X NOT IN ( SELECT X FROM `A` WHERE Y IN (24) ) Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@dawiz.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
David Giragosian skrev: On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso da...@lorenso.com wrote: Will anything ever be equal to NULL in a SELECT query? ... What's so special about NULL? http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html Should answer some of your questions, Dante. Oddly enough, that page fails to mention the = operator for which NULL does indeed equal NULL. http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: anniversary selects
Perhaps the examples here would help you: http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html -Original Message- From: Noel Butler [mailto:noel.but...@ausics.net] Sent: Saturday, December 26, 2009 6:47 PM To: mysql@lists.mysql.com Subject: anniversary selects Hi, Hope we all had a great Christmas! I am trying to run a query that selects a member every 365 days so we can send them a domain reminder. For my test (because i'm too lazy to count out someone with hundreds of days :) ) I am using a known member at around 27/28 days Now if I use this if finds the member, it doesn't find them if I go under to say 27 days, I've used the DATE_SUB many times before, but never for an exact match. SELECT * FROM `member` WHERE AddedOn=DATE_SUB(CURDATE(), INTERVAL 28 DAY) Now trying to get an exact match, fails. SELECT * FROM `member` WHERE AddedOn=DATE_SUB(CURDATE(), INTERVAL 28 DAY) Also tried one by one up to 30 and down to 25 days, Would someone please mind slapping me a good one reminding me what I've done wrong :) I thought CURDATE ignored the hours/mins/secs etc, but it seems it does not? Thanks Noel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why does this query take so long?
I think you might be right. The good-to-poor performance I'm seeing is so
intermittent. And I see now that it's also with other queries, though not as
extremely obvious as the spatial queries. However, even if the Index can't fit
in memory (4GB of RAM, lots free), just reading it from disk should allow
sub-millisecond response, no?
Strange thing is that I've used my laptop for benchmarking for the past five
years and it's always produced results fairly typical or at least consistent in
relation to our servers. This new thing is... new.
On 2009-12-29, at 3:18 AM, Gavin Towey wrote:
It sounds like your laptop might be paging mysql's memory to disk or
something like that. Your laptop may not be the most reliable source for
benchmarks.
Regards,
Gavin Towey
-Original Message-
From: René Fournier [mailto:m...@renefournier.com]
Sent: Monday, December 28, 2009 2:16 AM
To: René Fournier
Cc: mysql
Subject: Re: Why does this query take so long?
Even weirder, I came back to my laptop a couple hours later. And now the same
queries are taking 3-10 seconds instead of 0.01 seconds. What could be
causing this?
On 2009-12-28, at 1:19 PM, René Fournier wrote:
Hmm, weird. I just re-imported the data (after drop/create table, etc.), and
now the spatial queries run fast.
Has anyone seen this sort of thing happen? Maybe the Index got corrupted
somehow, and then MySQL had to do a full table scan (even though EXPLAIN
indicated it would use the Spatial Index)?
On 2009-12-28, at 9:28 AM, René Fournier wrote:
So just to clarify (hello?), the index which *should* be used (EXPLAIN says
so) and *should* make the query run faster than 4 seconds either isn't used
(why?) or simply doesn't speed up the query (again, why?).
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL
| 5260 | Using where |
++-+---+---+---+---+-+--+--+-+
SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m,
quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589
-114.82248918,51.65126254 -114.82248918,51.65126254
-114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'),
coordinates)
8 rows in set (3.87 sec)
On 2009-12-27, at 3:59 PM, René Fournier wrote:
So... there is an index, and it's supposedly used:
mysql EXPLAIN SELECT id, province, latitude, longitude,
AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates);
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 |
NULL | 5260 | Using where |
++-+---+---+---+---+-+--+--+-+
1 row in set (0.00 sec)
But when I run the query:
mysql SELECT id, province, latitude, longitude, AsText(coordinates),
s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates)
- ;
++--+-+---+--+--++
| id | province | latitude| longitude | AsText(coordinates)
| s_ts_r_m | quartersection |
++--+-+---+--+--++
| 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228
-114.79282412) | 04-031-06 W5 | N4 |
| 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112
-114.79283278) | 09-031-06 W5 | N4 |
| 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789
-114.81645649) | 05-031-06 W5 | N4 |
| 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119
-114.81643801) | 08-031-06 W5 | N4 |
| 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568
-114.80475858) | 05-031-06 W5 | E4 |
| 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028
-114.80479925) | 05-031-06 W5 | NE |
|
Weeks
Hi All, trying to write some SQL that will give me records for the CURRENT WEEK. Example, starting on a Sunday and going through Saturday. This week it would be Dec 27 - Jan 2. I am doing this so I can write a query that will show orders that are placed during the current week. Here is what I have, but this is showing from today for the next seven days. SELECT * FROM orders WHERE WEEK(NOW(), 7) = WEEK(orders.order_date, 7) AND DATEDIFF(NOW(),orders.order_date) 7; Would anyone have any advice? -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
On Mon, Dec 28, 2009 at 5:41 PM, Carsten Pedersen cars...@bitbybit.dkwrote: David Giragosian skrev: On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso da...@lorenso.com wrote: Will anything ever be equal to NULL in a SELECT query? ... What's so special about NULL? http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html Should answer some of your questions, Dante. Oddly enough, that page fails to mention the = operator for which NULL does indeed equal NULL. http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to / Carsten Good pick-up, Carsten. And that's definitely a new concept for me. David -- There is more hunger for love and appreciation in this world than for bread.- Mother Teresa
RE: Weeks
See: http://gtowey.blogspot.com/2009/04/how-to-select-this-wednesday-or-other.html just calculate the two dates, and use WHERE order_date BETWEEN (calculated start date) AND (calculated end date) This avoids using functions on the actual column when possible, since that will prevent using indexes to find your query results. Regards, Gavin Towey -Original Message- From: ML [mailto:mailingli...@mailnewsrss.com] Sent: Monday, December 28, 2009 4:15 PM To: mysql@lists.mysql.com Subject: Weeks Hi All, trying to write some SQL that will give me records for the CURRENT WEEK. Example, starting on a Sunday and going through Saturday. This week it would be Dec 27 - Jan 2. I am doing this so I can write a query that will show orders that are placed during the current week. Here is what I have, but this is showing from today for the next seven days. SELECT * FROM orders WHERE WEEK(NOW(), 7) = WEEK(orders.order_date, 7) AND DATEDIFF(NOW(),orders.order_date) 7; Would anyone have any advice? -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql load balancing
Miguel, On Fri, Dec 25, 2009 at 4:56 PM, Miguel Angel Nieto cor...@miguelangelnieto.net wrote: Load balancing, or high availability? I do not think there is anything good and simple AND generic out of the box. As previous posters have noted, you generally have to build something on top of other tools. Hi, I have the HA solved with MMM. Now, I want load balacing, sending read queries to slaves and write queries to masters. I read about mysql proxy, sqlrelay... but I didn't know the difference betwen them, and I think the best way to do this is to split in your application. The magical read/write split, done in a way that's invisible to the application, is almost invariably a source of problems when there is replication lag (which there always is). The application needs to be aware of replication lag and must know how to handle it or when it's OK to ignore it. Most applications cannot simply let a dumb intermediate layer handle it for them, because there are always cases when lag is not permissible at all, mixed with cases where lag is OK, and the application needs to make the decision. -- Baron Schwartz Percona Inc: Services and Support for MySQL http://www.percona.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
Well, if nothing can ever equal null, then why isn't MySQL query parser smart enough to reduce my queries to something more sensible? If I'm saying this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; Why isn't it able to reduce the query to something more like this: SELECT * FROM sometable WHERE somecolumn = 'abc'; Since it already should know that somecolumn = NULL will always evaluate to FALSE (or is it NULL? ... either way, it's not TRUE)? If I run the first query above, the query takes about 15 seconds to run against 40 million records, but if I run the second query, it takes about .050 seconds. The test for NULL seems to cause the query to skip use of an index because I doubt NULL values are indexed. Am I expecting too much of the parser? -- Dante Martijn Tonies wrote: Hi, Will anything ever be equal to NULL in a SELECT query? No, never. Null also means unknown, if you design your tables well enough, there should be no NULLs -stored- (different from a resultset, where there can be nulls, for example in LEFT JOINs), because it's no use to store what you don't know. The only case when you want to store a null is when you do want to -know- you don't know a value. A column can have two states: null or not null. It either has data (a value, depending on the datatype), or no data (null), which is where column IS NULL (has no data) or column IS NOT NULL (has data) comes into play. Null is not the same as empty. An empty string, for example, is not equal to null (which is unknown), you cannot compare anything to what you don't know, which is why your comparison fails. NULL = NULL fails, so does NULL NULL in the strict sense. SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: You mean the optimizer, perhaps it should, perhaps it shouldn't. Yet, your query is not really the smartest, as you should avoid writing column = NULL, as this doesn't make sense. SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? Quite a bit ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- -- D. Dante Lorenso -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: anniversary selects
Thanks, that's exactly what I was after. On Mon, 2009-12-28 at 14:53 -0800, Daevid Vincent wrote: Perhaps the examples here would help you: http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html -Original Message- From: Noel Butler [mailto:noel.but...@ausics.net] Sent: Saturday, December 26, 2009 6:47 PM To: mysql@lists.mysql.com Subject: anniversary selects Hi, Hope we all had a great Christmas! I am trying to run a query that selects a member every 365 days so we can send them a domain reminder. For my test (because i'm too lazy to count out someone with hundreds of days :) ) I am using a known member at around 27/28 days Now if I use this if finds the member, it doesn't find them if I go under to say 27 days, I've used the DATE_SUB many times before, but never for an exact match. SELECT * FROM `member` WHERE AddedOn=DATE_SUB(CURDATE(), INTERVAL 28 DAY) Now trying to get an exact match, fails. SELECT * FROM `member` WHERE AddedOn=DATE_SUB(CURDATE(), INTERVAL 28 DAY) Also tried one by one up to 30 and down to 25 days, Would someone please mind slapping me a good one reminding me what I've done wrong :) I thought CURDATE ignored the hours/mins/secs etc, but it seems it does not? Thanks Noel
Re: Is anything ever equal to NULL?
Well, if nothing can ever equal null, then why isn't MySQL query parser smart enough to reduce my queries to something more sensible? If I'm saying this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; Why isn't it able to reduce the query to something more like this: SELECT * FROM sometable WHERE somecolumn = 'abc'; Since it already should know that somecolumn = NULL will always evaluate to FALSE (or is it NULL? ... either way, it's not TRUE)? If I run the first query above, the query takes about 15 seconds to run against 40 million records, but if I run the second query, it takes about .050 seconds. The test for NULL seems to cause the query to skip use of an index because I doubt NULL values are indexed. Am I expecting too much of the parser? Of the optimizer... I could as easily write: were myintegercolumn = 'test' which would also result into False (haven't tried, depending on how it evaluates, this could result in a datatype error ;-) ) Would the DBSM code have to check for all of these silly constructs? Talking about bloat. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
