Re: mysql old 4.* query fails on 5.*
Anyone know whats wrong here? Try as ... From (Klienter AS K, Tid As Td, Personal AS P) JOIN Uppdrag AS U ON K.Klient_ID = U.Klient_ID ... or ... From Tid As Td, Personal AS P, Klienter AS K JOIN Uppdrag AS U ON K.Klient_ID = U.Klient_ID ... This is the problem I had in one of my queries. The join is on the last table on the left side. Either use parentheses or put K table last. HTH, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
mysqlcheck -h$host -u$user -p$pass --analyze $dbname I wish that was the case! I tried analyze table ... and optimize table ..., which I presume would be the same. It did not help. I also ran mysqlcheck just to see if it will make a difference. Nope! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to rewrite SQL query for new MySQL LEFT JOINS
I hope someone can clue me in what a syntax of query that produces the same would look like for MySQL 5.0.12 Old query meant to list most recent message from each thread, e.g. select * from messages left join messages as messages_ on messages.thread = messages_.thread and messages.created messages_.created where messages_.id is null It worked in 4.x but does not work in the new syntax. How should I rewrite it to get the same result? TIA, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to rewrite SQL query for new MySQL LEFT JOINS
It worked in 4.x but does not work in the new syntax. How should I rewrite it to get the same result? OK, that was a lie. It works in 5.x as well. I should learn to describe my problem more accurately as well as RTFM :-( The correct description of the query in question would have been: select * from messages, someothertable left join messages as messages_ on messages.thread = messages_.thread and messages.created messages_.created where messages_.id is null Since there was a comma (inner) join there, the left join applied only to 'someothertable' and not to message table as intended. As I learned, in 5.0.12+, parentheses matter! Duh! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
I had perfectly working complex queries both with LEFT JOIN and without and they were returning results in under a second. After upgrade to 5.0.x, the same queries would return results in 20-30 second range. Through trial and error, I discovered that in case of SELECT ... FROM table1, table2 ... ORDER BY table2.column will be very slow (10s of seconds), while the same query ordered by table1.column will be in under 1 second. If I switch the order of tables in FROM, the same will hold true in reverse order. Is this a bug or I missed something in my googling? More importantly, what can I do change this--I need those sorts! :-( I got same results with 5.0.22, 5.0.27 (Linux). TIA, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
possible you had set up some query cache in 4, but not currently in 5? may not be optimized, but yes, query cache is enabled, all 25 MB of it. :-) how did you 'upgraded' your data? regrettably, in-place. interestingly, I was recovering after server crash that chopped of a table. after upgrading the server (in-place), I re-read the corrupt table from script dumped by mysqlbinlog. it is THAT table that is causing me grief. I thought it was some missing indices, but I have indices on all columns I use in WHERE. what means this exactly? in reverse ordered tables, query is fast on second or on first table order? 'select ... from table1, table2, table3 ... order by table1.column' is FAST 'select ... from table1, table2, table3 ... order by table2.column' is SLOW did your tried an EXPLAIN? yes, thanks for reminding me to use it. I compared the two; the slow one uses temporary table and filesort; the fast one does not. Both use where and all select types are SIMPLE. So, with your help, I know why it is slow. Is there a way to out of this without downgrading the server? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
When you upgraded from 4.1 to 5.0, did you do an in-place upgrade, or mysqldump your data and then re-import? As replied to Sebastian's post, in-place. Try using either mysqldump or mysql-administrator to dump out your data to an .sql file. Then re-import all of your data into 5.x. You will see a significant difference in your query speeds once you do this. Will this still hold true, even if I dump data out of MySQL 5 and re-import it, or do I need to downgrade first? As to your query cache, make sure that it's on (on by default) and, based on your tables, either your MyISAM key_buffer_size or your InnoDB buffer_pool_size are correct for your server's amount of RAM. I guess I will have to check if 8MB is good on 500MB RAM. I did some research back when messing with 4.1, so a good time to do it again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Zip Code Distance
On Tue, 2006-08-29 at 16:30 -0400, Jesse wrote: Does anyone have any ideas? One technique is to calculate set distances (5,10,25,50) between the zip codes in advance and stick the results in a table. Enjoy, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_incrment seed number
On Thu, 2006-07-06 at 13:41 -0700, Chuck Holzwarth wrote: Is there a way to set the auto_increment start number? I am trying to set up a development and test system and the application that is writing to the tables is confused as to which MySQL it is writing to. I don't have any contrtol over the other app but since the key value is taken back to the other app, I can determine which areas are having the problem if I start the dev instance at 1 and start the test instance at 100,000. http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
On Tue, 2006-04-25 at 00:43 -0700, Daevid Vincent wrote: So for a sanity check, I decided to look online and punch in some to see what the real lat/long should be. Well, different sites give different values, and not only are they slightly off, but sometimes they're _positive_ or _negative_!? UGH! Not sure what your confusion is. It is a matter of notation. The negative value represents West where it is negative (as would be the East; note how there is no W mentioned there). So read about their presentation formats. I think 4 decimal points are plenty, especially if they follow the rounding rules. :-) Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
... err, as would be South... N+, S-, E+, W- Ed :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OK, need a little inspiration here...
I am stomped and not sure how to get results from the particular type of query. While I am not sure, if this is an appropriate place to ask, if nothing else perhaps someone will direct me to a more appropriate forum. I am trying to figure out how to return the latest record in each group of records identified by some hash, e.g. (^ are rows I want), ^ 000543 | 20031019140457 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000542 | 20031018214128 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000525 | 20031018210622 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000513 | 20031017010947 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000512 | 20031017010804 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | ^ 000431 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 | | 000417 | 20031012135916 | 73641d1f174a502951db06653525af125dd4df46 | ^ 000191 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | 000188 | 20030920110057 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | ^ 000161 | 20030917094352 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | | 000152 | 20030917084805 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | | 000151 | 20030915095857 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | | 000148 | 20030915094718 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | The result I want is: | 000543 | 20031019140457 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000431 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 | | 000191 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | 000161 | 20030917094352 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | I tried GROUP BY on the hash, but then MySQL grabs the earliest record. (Anyway to influence the sorting order before GROUP BY is applied?) E.g., | 000512 | 20031017010804 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000417 | 20031012135916 | 73641d1f174a502951db06653525af125dd4df46 | | 000188 | 20030920110057 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | 000148 | 20030915094718 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | Alternatively, I also tried MAX(date), which of course is incorrect, because while returning the latest date for the hash--good!, it looses touch with the remaining columns of the record, i.e, ids and dates are mismatched, | outside provided sample | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | ^^^ ignore | 000417 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 | | 000188 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | outside provided sample | b8b993464ec303bbff704f582e4f7b5b9ea100bf | ^^^ ignore, again record outside the provided sample Am I missing some kind of magic somewhere or do I need to resort to extra columns and/or extra steps to get what I want? I am using 4.1.16 on Linux with 512 MB of RAM, so not sure if using intermediate tables would be a well performing solution, but if there is one, I still want to hear about it. :-) TIA, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
