RE: Help saving MySQL
I think that letter actually does MySQL a favour as it points out 'MySQL has been used as a pricing lever by Oracle customers' That single factor says Oracle should not be allowed to control MySQL as it would enable Oracle to more easily raise or maintain high prices! For something to be an effective 'pricing lever' it has to be a viable alternative. MySQL is a very effective pricing lever on Oracle as it is a mature and proven product with excellent support. Regards John -Original Message- From: Martijn Tonies m.ton...@upscene.com Sent: 16 December 2009 13:16 Cc: mysql@lists.mysql.com Subject: Re: Help saving MySQL It's still not too late to save MySQL and everyone that is using MySQL can help making a real difference. Please visit http://monty-says.blogspot.com/2009/12/help-saving-mysql.html and write a message to EC! Regards, Monty Guess you don't want them to write letters like this? http://kirkwylie.blogspot.com/2009/12/my-open-letter-to-european-competition.html 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=john.dais...@butterflysystems.co.uk _ This e-mail has been scanned for viruses by MessageLabs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help saving MySQL
I think that letter actually does MySQL a favour as it points out 'MySQL has been used as a pricing lever by Oracle customers' That single factor says Oracle should not be allowed to control MySQL as it would enable Oracle to more easily raise or maintain high prices! For something to be an effective 'pricing lever' it has to be a viable alternative. MySQL is a very effective pricing lever on Oracle as it is a mature and proven product with excellent support. I have to disagree with you there as the letter also mentions that MySQL isn't a viable alternative, or actually, Oracle shouldn't have been used for those projects in the first place. Give a number of other open source database systems, that particular point (MySQL) to drive the license price for Oracle down, is moot because you could use any other freely available DBMS (even for commercial projects!) to do the same. Please visit http://monty-says.blogspot.com/2009/12/help-saving-mysql.html and write a message to EC! Guess you don't want them to write letters like this? http://kirkwylie.blogspot.com/2009/12/my-open-letter-to-european-competition.html 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: Importing large databases faster
Madison Kelly wrote: Hi all, I've got a fairly large set of databases I'm backing up each Friday. The dump takes about 12.5h to finish, generating a ~172 GB file. When I try to load it though, *after* manually dumping the old databases, it takes 1.5~2 days to load the same databases. I am guessing this is, at least in part, due to indexing. My question is; Given an empty target DB and a dump file generated via: ssh r...@server mysqldump --all-databases -psecret /path/to/backup.sql I use the -e -v -f -q -Q -K parameters for the mysqldump on large tables/databases. It does what you are asking for. Disables the key generation until all of the data is inserted. It also uses multi insert statements and not individual insert statement for every row which speeds up things considerable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help Save MySQL
Just wanted to see what people thought about the offer from Nexedi for MySQL http://www.nexedi.com/NXD-MySQL.Takeover/view They obviously have more reason to protect and develop MySQL but are they serious? 1€ for a profitable business unit with a turnover of around $100m. If that’s what the price is going to be then perhaps I should offer 2€ or maybe MySQL users should get together submit a realistic offer. Regards John Daisley Business Intelligence Developer / Certified MySQL 5.0 Database Administrator Inspired Gaming Group Plc Direct Dial +44 (0)1283 519244 Telephone +44 (0)1283 512777 ext 2244 Mobile +44 (0)7812 451238 Email john.dais...@llg.co.uk www.inspiredgaminggroup.com ** Confidentiality : This e-mail and any attachments are intended for the addressee only and may be confidential. If they come to you in error you must take no action based on them, nor must you copy or show them to anyone. Please advise the sender by replying to this e-mail immediately and then delete the original from your computer. Opinion : Any opinions expressed in this e-mail are entirely those of the author and unless specifically stated to the contrary, are not necessarily those of the author’s employer. Security Warning : Internet e-mail is not necessarily a secure communications medium and can be subject to data corruption. We advise that you consider this fact when e-mailing us. Viruses : We have taken steps to ensure that this e-mail and any attachments are free from known viruses but in keeping with good computing practice, you should ensure that they are virus free. Inspired Gaming (UK) Limited Registered in England No 3565640 Registered Office 3 The Maltings Wetmore Road, Burton On Trent, Staffordshire DE14 1SE ___ This message has been checked for all known viruses by the MessageLabs Virus Control Centre.
RE: Help saving MySQL
I see some of your point Martin but I think the eu would look at that letter and see the author is stating 'MySQL has been used as a pricing lever'. That single factor should be enough for them to be very concerned by an acquisition as removing an effective pricing lever from the market by acquisition is anti-competitive and helps increase or maintain high prices. I don't believe you could use any other open source database as a pricing lever in the same way because none are as mature or offer the levels of support that MySQL does and no other open source system can boast the performance benefits (especially with ndbcluster) or the availability of suitably trained and certified people to support their products. Regards John -Original Message- From: Martijn Tonies m.ton...@upscene.com Sent: 17 December 2009 09:44 To: mysql@lists.mysql.com Subject: Re: Help saving MySQL I think that letter actually does MySQL a favour as it points out 'MySQL has been used as a pricing lever by Oracle customers' That single factor says Oracle should not be allowed to control MySQL as it would enable Oracle to more easily raise or maintain high prices! For something to be an effective 'pricing lever' it has to be a viable alternative. MySQL is a very effective pricing lever on Oracle as it is a mature and proven product with excellent support. I have to disagree with you there as the letter also mentions that MySQL isn't a viable alternative, or actually, Oracle shouldn't have been used for those projects in the first place. Give a number of other open source database systems, that particular point (MySQL) to drive the license price for Oracle down, is moot because you could use any other freely available DBMS (even for commercial projects!) to do the same. Please visit http://monty-says.blogspot.com/2009/12/help-saving-mysql.html and write a message to EC! Guess you don't want them to write letters like this? http://kirkwylie.blogspot.com/2009/12/my-open-letter-to-european-competition.html 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=john.dais...@butterflysystems.co.uk _ This e-mail has been scanned for viruses by MessageLabs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Simple Query Question
Hi, I am sure there is a simple solution to this problem, I just cant find it :) I have got a table that records views for an article for each blog per day. So the structure is as follows: CREATE TABLE `wp_views` ( `blog_id` int(11) NOT NULL, `post_id` int(11) NOT NULL, `date` date NOT NULL, `views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8; Now thats fine and I can pull top blogs per day and thats all fine, but what I am after is pulling the top articles for a time period and where I am running into problems is where two blogs have the same post_id's the views get sum()'d for the day and I cant figure out (read end of year mind block) how to get around it. Here is my current query (for last 7 days): SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10 Any ideas as to whats wrong. I know its something simple, I just cant put my finger on it. Thanks in advance, Ian
Re: Simple Query Question
Hi Ian, Why do you think something's wrong? Here is my test data and the results of your query: --- mysql SELECT * FROM wp_views; +-+-++---+ | blog_id | post_id | date | views | +-+-++---+ | 1 | 1 | 2009-12-16 | 2 | | 1 | 1 | 2009-12-17 | 3 | | 1 | 2 | 2009-12-16 | 4 | | 1 | 2 | 2009-12-17 | 5 | | 2 | 1 | 2009-12-16 | 6 | | 2 | 1 | 2009-12-17 | 7 | | 2 | 2 | 2009-12-16 | 8 | | 2 | 2 | 2009-12-17 | 9 | | 1 | 1 | 2009-12-18 | 1 | | 1 | 2 | 2009-12-18 | 1 | | 2 | 1 | 2009-12-18 | 1 | | 2 | 2 | 2009-12-18 | 1 | +-+-++---+ 12 rows in set (0.00 sec) mysql SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10; +-+-+---+ | blog_id | post_id | views | +-+-+---+ | 2 | 2 |17 | | 2 | 1 |13 | | 1 | 2 | 9 | | 1 | 1 | 5 | +-+-+---+ 4 rows in set (0.00 sec) --- Seems OK to me... Are you getting different results? Take care, Aleksandar Ian wrote: Hi, I am sure there is a simple solution to this problem, I just cant find it :) I have got a table that records views for an article for each blog per day. So the structure is as follows: CREATE TABLE `wp_views` ( `blog_id` int(11) NOT NULL, `post_id` int(11) NOT NULL, `date` date NOT NULL, `views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8; Now thats fine and I can pull top blogs per day and thats all fine, but what I am after is pulling the top articles for a time period and where I am running into problems is where two blogs have the same post_id's the views get sum()'d for the day and I cant figure out (read end of year mind block) how to get around it. Here is my current query (for last 7 days): SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10 Any ideas as to whats wrong. I know its something simple, I just cant put my finger on it. Thanks in advance, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 ) ? Thanks ! Edward. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simple Query Question
Hi, Thanks, I just checked and it was a memcache that was caching the output. See I knew it was a simple solution ;) Thanks for the effort everyone and sorry for wasting time. Regards Ian 2009/12/17 Aleksandar Bradaric leann...@gmail.com Hi Ian, Why do you think something's wrong? Here is my test data and the results of your query: --- mysql SELECT * FROM wp_views; +-+-++---+ | blog_id | post_id | date | views | +-+-++---+ | 1 | 1 | 2009-12-16 | 2 | | 1 | 1 | 2009-12-17 | 3 | | 1 | 2 | 2009-12-16 | 4 | | 1 | 2 | 2009-12-17 | 5 | | 2 | 1 | 2009-12-16 | 6 | | 2 | 1 | 2009-12-17 | 7 | | 2 | 2 | 2009-12-16 | 8 | | 2 | 2 | 2009-12-17 | 9 | | 1 | 1 | 2009-12-18 | 1 | | 1 | 2 | 2009-12-18 | 1 | | 2 | 1 | 2009-12-18 | 1 | | 2 | 2 | 2009-12-18 | 1 | +-+-++---+ 12 rows in set (0.00 sec) mysql SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10; +-+-+---+ | blog_id | post_id | views | +-+-+---+ | 2 | 2 |17 | | 2 | 1 |13 | | 1 | 2 | 9 | | 1 | 1 | 5 | +-+-+---+ 4 rows in set (0.00 sec) --- Seems OK to me... Are you getting different results? Take care, Aleksandar Ian wrote: Hi, I am sure there is a simple solution to this problem, I just cant find it :) I have got a table that records views for an article for each blog per day. So the structure is as follows: CREATE TABLE `wp_views` ( `blog_id` int(11) NOT NULL, `post_id` int(11) NOT NULL, `date` date NOT NULL, `views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8; Now thats fine and I can pull top blogs per day and thats all fine, but what I am after is pulling the top articles for a time period and where I am running into problems is where two blogs have the same post_id's the views get sum()'d for the day and I cant figure out (read end of year mind block) how to get around it. Here is my current query (for last 7 days): SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10 Any ideas as to whats wrong. I know its something simple, I just cant put my finger on it. Thanks in advance, Ian
Re: Help saving MySQL
I see some of your point Martin but I think the eu would look at that letter and see the author is stating 'MySQL has been used as a pricing lever'. That single factor should be enough for them to be very concerned by an acquisition as removing an effective pricing lever from the market by acquisition is anti-competitive and helps increase or maintain high prices. Believe me, the EU (Smit Kroes) ain't exactly stupid... That single factor only makes a difference -if there are no alternatives- (eg: other lower prices database systems), and yet, there are. So I doubt if that's gonna make a difference. I don't believe you could use any other open source database as a pricing lever in the same way because none are as mature or offer the levels of support that MySQL does and no other open source system can boast the performance benefits (especially with ndbcluster) or the availability of suitably trained and certified people to support their products. I beg to differ, heck, I also would like to aadd that some things in MySQL are not mature whatsoever compared to other DBMSses, being open source or not. MySQL is gonna have one big struggle to get things straight after this... Mind you, it would certainly be easier and probably better for business if Oracle didn't get MySQL, I agree with you on that ;-) But it's not about what -we- think. 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: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance
-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. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Thanks ! Edward. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.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: Importing large databases faster
At 03:59 AM 12/17/2009, you wrote: Madison Kelly wrote: Hi all, I've got a fairly large set of databases I'm backing up each Friday. The dump takes about 12.5h to finish, generating a ~172 GB file. When I try to load it though, *after* manually dumping the old databases, it takes 1.5~2 days to load the same databases. I am guessing this is, at least in part, due to indexing. My question is; Given an empty target DB and a dump file generated via: ssh r...@server mysqldump --all-databases -psecret /path/to/backup.sql I use the -e -v -f -q -Q -K parameters for the mysqldump on large tables/databases. It does what you are asking for. Disables the key generation until all of the data is inserted. It also uses multi insert statements and not individual insert statement for every row which speeds up things considerable. Load Data ... is still going to be much faster. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Help Save MySQL
If that’s what the price is going to be then perhaps I should offer 2€ or maybe MySQL users should get together submit a realistic offer. This sounds interesting... Get a community effort to accept donations and purchase MySQL. Then, put it under the GPL and make sure nobody owns it. Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS virtual server for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help Save MySQL
Hi Guys, Let's say that every mysql developer (here I am thinking only persons, not companies) that wants mysql to go forward would contribute from $500,00 to $1500,00, how much are we talking about? And we would have a 100% community owned and community driven open source initiative... Course, there are many others management problems and legal issues to solve, but if anybody would join me I would be the first one! And also would be a REMARKABLE adventure and maybe the next step for the open source initiatives around the world... Best Regards Bruno B. B. Magalhães BLACKBEAN CONSULTORIA Rua Real Grandeza 193, Sala 210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9695-2263 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. On Dec 17, 2009, at 2:16 PM, Neil Aggarwal wrote: If that’s what the price is going to be then perhaps I should offer 2€ or maybe MySQL users should get together submit a realistic offer. This sounds interesting... Get a community effort to accept donations and purchase MySQL. Then, put it under the GPL and make sure nobody owns it. Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS virtual server for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=brunomagalh...@blackbean.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Spatial extensions
Awesome, this is what I was trying to find, as you succinctly wrote it. I *really* appreciate getting pointed in the right direction, since I haven't found a lot of MySQL's GIS tutorials directed at what I'm trying to do. Still, a couple questions, the Distance() function you included, that must require 5.1 or higher right? 5.0.88 on my box throws an error: Function places.Distance does not exist Also, where does line_segment come from in the below query? Thanks. ...Rene On 2009-12-17, at 8:45 AM, Gavin Towey wrote: Yes, spatial indexes are very fast: Query would be something like: SET @center = GeomFromText('POINT(37.372241 -122.021671)'); SET @radius = 0.005; SET @bbox = GeomFromText(CONCAT('POLYGON((', X(@center) - @radius, ' ', Y(@center) - @radius, ',', X(@center) + @radius, ' ', Y(@center) - @radius, ',', X(@center) + @radius, ' ', Y(@center) + @radius, ',', X(@center) - @radius, ' ', Y(@center) + @radius, ',', X(@center) - @radius, ' ', Y(@center) - @radius, '))') ); select id, astext(coordinates), Distance(@center,line_segment) as dist FROM places where MBRContains(@bbox, line_segment) order by dist limit 10; Regards, Gavin Towey -Original Message- From: René Fournier [mailto:m...@renefournier.com] Sent: Wednesday, December 16, 2009 4:32 PM To: mysql Subject: Spatial extensions I have table with 2 million rows of geographic points (latitude, longitude). Given a location -- say, 52º, -113.9º -- what's the fastest way to query the 10 closest points (records) from that table? Currently, I'm using a simple two-column index to speed up queries: CREATE TABLE `places` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `latitude` decimal(10,8) NOT NULL, `longitude` decimal(12,8) NOT NULL PRIMARY KEY (`id`), KEY `latlng` (`latitude`,`longitude`) ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; My current query is fairly quick: SELECT SQL_NO_CACHE * FROM places WHERE latitude BETWEEN 51.98228037384 AND 52.033153677 AND longitude BETWEEN -113.94770681881 AND -113.86685484296; But I wonder a couple things: 1. Would MySQL's [seemingly anemic] spatial extensions would speed things up if I added a column of type POINT (and a corresponding spatial INDEX)? CREATE TABLE `places` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `latitude` decimal(10,8) NOT NULL, `longitude` decimal(12,8) NOT NULL, `coordinates` point NOT NULL, PRIMARY KEY (`id`), KEY `latlng` (`latitude`,`longitude`), KEY `coord` (`coordinates`(25)) ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 2. How would I write the query? ...Rene -- 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...@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
Re: Help saving MySQL
On Thursday 17 December 2009, Daisley, John (Burton) wrote: I think that letter actually does MySQL a favour as it points out 'MySQL has been used as a pricing lever by Oracle customers' That single factor says Oracle should not be allowed to control MySQL as it would enable Oracle to more easily raise or maintain high prices! For something to be an effective 'pricing lever' it has to be a viable alternative. MySQL is a very effective pricing lever on Oracle as it is a mature and proven product with excellent support. Regards John -Original Message- From: Martijn Tonies m.ton...@upscene.com Sent: 16 December 2009 13:16 Cc: mysql@lists.mysql.com Subject: Re: Help saving MySQL It's still not too late to save MySQL and everyone that is using MySQL can help making a real difference. Please visit http://monty-says.blogspot.com/2009/12/help-saving-mysql.html and write a message to EC! Regards, Monty Guess you don't want them to write letters like this? http://kirkwylie.blogspot.com/2009/12/my-open-letter-to-european-competitio n.html Shesh, this guy should get a job as a spin doctor for Tiger Woods! -- Cheers, Gene There are four boxes to be used in defense of liberty: soap, ballot, jury, and ammo. Please use in that order. -Ed Howdershelt (Author) The NRA is offering FREE Associate memberships to anyone who wants them. https://www.nrahq.org/nrabonus/accept-membership.asp An Italian is COMBING his hair in suburban DES MOINES! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Spatial extensions
Rene: We've easily integrated GIS with MySQL into our sites: http://tenant.com/map-search.php http://yearlyrentals.com http://acnj.com/map.php ... Thanks! Jim Ginn Visit My Work (888) 546-4466 office (609) 226-5709 cell Awesome, this is what I was trying to find, as you succinctly wrote it. I *really* appreciate getting pointed in the right direction, since I haven't found a lot of MySQL's GIS tutorials directed at what I'm trying to do. Still, a couple questions, the Distance() function you included, that must require 5.1 or higher right? 5.0.88 on my box throws an error: Function places.Distance does not exist Also, where does line_segment come from in the below query? Thanks. ...Rene On 2009-12-17, at 8:45 AM, Gavin Towey wrote: Yes, spatial indexes are very fast: Query would be something like: SET @center = GeomFromText('POINT(37.372241 -122.021671)'); SET @radius = 0.005; SET @bbox = GeomFromText(CONCAT('POLYGON((', X(@center) - @radius, ' ', Y(@center) - @radius, ',', X(@center) + @radius, ' ', Y(@center) - @radius, ',', X(@center) + @radius, ' ', Y(@center) + @radius, ',', X(@center) - @radius, ' ', Y(@center) + @radius, ',', X(@center) - @radius, ' ', Y(@center) - @radius, '))') ); select id, astext(coordinates), Distance(@center,line_segment) as dist FROM places where MBRContains(@bbox, line_segment) order by dist limit 10; Regards, Gavin Towey -Original Message- From: René Fournier [mailto:m...@renefournier.com] Sent: Wednesday, December 16, 2009 4:32 PM To: mysql Subject: Spatial extensions I have table with 2 million rows of geographic points (latitude, longitude). Given a location -- say, 52º, -113.9º -- what's the fastest way to query the 10 closest points (records) from that table? Currently, I'm using a simple two-column index to speed up queries: CREATE TABLE `places` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `latitude` decimal(10,8) NOT NULL, `longitude` decimal(12,8) NOT NULL PRIMARY KEY (`id`), KEY `latlng` (`latitude`,`longitude`) ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; My current query is fairly quick: SELECT SQL_NO_CACHE * FROM places WHERE latitude BETWEEN 51.98228037384 AND 52.033153677 AND longitude BETWEEN -113.94770681881 AND -113.86685484296; But I wonder a couple things: 1. Would MySQL's [seemingly anemic] spatial extensions would speed things up if I added a column of type POINT (and a corresponding spatial INDEX)? CREATE TABLE `places` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `latitude` decimal(10,8) NOT NULL, `longitude` decimal(12,8) NOT NULL, `coordinates` point NOT NULL, PRIMARY KEY (`id`), KEY `latlng` (`latitude`,`longitude`), KEY `coord` (`coordinates`(25)) ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 2. How would I write the query? ...Rene -- 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...@renefournier.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@oats.com
Re: Help Save MySQL
Hey, Let's say that every mysql developer (here I am thinking only persons, not companies) that wants mysql to go forward would contribute from $500,00 to $1500,00, how much are we talking about? And we would have a 100% community owned and community driven open source initiative... Course, there are many others management problems and legal issues to solve, but if anybody would join me I would be the first one! And also would be a REMARKABLE adventure and maybe the next step for the open source initiatives around the world... Brilliant... now, whose gonna run the project? ;-) 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: Help Save MySQL
Maybe a three-years consul composed from and elected by the community (by community I mean the people that contributed with funds, not the user community) by voting? Regards, Bruno B. B. Magalhães BLACKBEAN CONSULTORIA Rua Real Grandeza 193, Sala 210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9695-2263 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. On Dec 17, 2009, at 3:03 PM, Martijn Tonies wrote: Hey, Let's say that every mysql developer (here I am thinking only persons, not companies) that wants mysql to go forward would contribute from $500,00 to $1500,00, how much are we talking about? And we would have a 100% community owned and community driven open source initiative... Course, there are many others management problems and legal issues to solve, but if anybody would join me I would be the first one! And also would be a REMARKABLE adventure and maybe the next step for the open source initiatives around the world... Brilliant... now, whose gonna run the project? ;-) 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=brunomagalh...@blackbean.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help Save MySQL
Maybe a three-years consul composed from and elected by the community (by community I mean the people that contributed with funds, not the user community) by voting? I have been quite closely involved in doing this for the Firebird project, you would be amazed as to how hard it is to get things going, even for a large community like Firebird. Having seen this from up close and personal, I have a lot of respect for the people that actually get things done... Let's say that every mysql developer (here I am thinking only persons, not companies) that wants mysql to go forward would contribute from $500,00 to $1500,00, how much are we talking about? And we would have a 100% community owned and community driven open source initiative... Course, there are many others management problems and legal issues to solve, but if anybody would join me I would be the first one! And also would be a REMARKABLE adventure and maybe the next step for the open source initiatives around the world... Brilliant... now, whose gonna run the project? ;-) 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: Help saving MySQL
John, Another read on the subject: http://blogs.the451group.com/opensource/2009/12/10/the-case-against-the-case-against-oracle-mysql/ Enjoy. With regards, Martijn Tonies Upscene Productions http://www.upscene.com I see some of your point Martin but I think the eu would look at that letter and see the author is stating 'MySQL has been used as a pricing lever'. That single factor should be enough for them to be very concerned by an acquisition as removing an effective pricing lever from the market by acquisition is anti-competitive and helps increase or maintain high prices. I don't believe you could use any other open source database as a pricing lever in the same way because none are as mature or offer the levels of support that MySQL does and no other open source system can boast the performance benefits (especially with ndbcluster) or the availability of suitably trained and certified people to support their products. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help saving MySQL
Martjin, I really don't like to point fingers or anything like that, but the simple fact Oracle owns the MySQL copyrights is by it self very concerning, as all our investments (time and money) could be lost over night, if Oracles decides to close de source or change it's licensing policies. Many could say Oh, they will not do that, because they promised not to., as an old professor of mine said: What isn't written, does not count! Everything else is here say, and there is no legal or moral grounds. As a sailor I saw what Larry Ellisson did with the oldest and most prestigious match race in the sport of sailing, the America's Cup. He and Ernesto Bertarelli (a swiss billionaire) are fighting in the New York Supreme Court for over 3 years for power, and almost 160 years of history and sportsmanship are being destroyed. Personally, that's not Ellisson's fault because Bertarelli is the one who is trying to subvert the rules (the Deed of Gift written in 1852 and that drives the competition until today http://en.wikipedia.org/wiki/Deed_of_Gift), but this shows the kind of mentality that Ellisson works with: Until the last consequences. More info at: http://www.yachtingmagazine.com/article.jsp?ID=170610 Best Regards, Bruno B. B. Magalhães BLACKBEAN CONSULTORIA Rua Real Grandeza 193, Sala 210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9695-2263 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. On Dec 17, 2009, at 3:33 PM, Martijn Tonies wrote: John, Another read on the subject: http://blogs.the451group.com/opensource/2009/12/10/the-case-against-the-case-against-oracle-mysql/ Enjoy. With regards, Martijn Tonies Upscene Productions http://www.upscene.com I see some of your point Martin but I think the eu would look at that letter and see the author is stating 'MySQL has been used as a pricing lever'. That single factor should be enough for them to be very concerned by an acquisition as removing an effective pricing lever from the market by acquisition is anti-competitive and helps increase or maintain high prices. I don't believe you could use any other open source database as a pricing lever in the same way because none are as mature or offer the levels of support that MySQL does and no other open source system can boast the performance benefits (especially with ndbcluster) or the availability of suitably trained and certified people to support their products. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=brunomagalh...@blackbean.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Spatial extensions
Not only is it 5.1, but there's a special branch that has improved GIS functions not found in the regular MySQL. I'm not sure if/when they're planning on rolling them back into mysql: http://downloads.mysql.com/forge/mysql-5.1.35-gis/ If it's not possible to use that version, then you can still implement a Distance function yourself as a stored procedure or UDF. Just google for mysql+haversine or something similar. The important part though is the MBRContains, which does an efficient box cull and uses the spatial index. Oops, I forgot to change a couple occurances of line_segment to coordinates line_segment was just the column name I was using in my original query. Regards, Gavin Towey -Original Message- From: René Fournier [mailto:m...@renefournier.com] Sent: Thursday, December 17, 2009 8:54 AM To: Gavin Towey Cc: mysql Subject: Re: Spatial extensions Awesome, this is what I was trying to find, as you succinctly wrote it. I *really* appreciate getting pointed in the right direction, since I haven't found a lot of MySQL's GIS tutorials directed at what I'm trying to do. Still, a couple questions, the Distance() function you included, that must require 5.1 or higher right? 5.0.88 on my box throws an error: Function places.Distance does not exist Also, where does line_segment come from in the below query? Thanks. ...Rene On 2009-12-17, at 8:45 AM, Gavin Towey wrote: Yes, spatial indexes are very fast: Query would be something like: SET @center = GeomFromText('POINT(37.372241 -122.021671)'); SET @radius = 0.005; SET @bbox = GeomFromText(CONCAT('POLYGON((', X(@center) - @radius, ' ', Y(@center) - @radius, ',', X(@center) + @radius, ' ', Y(@center) - @radius, ',', X(@center) + @radius, ' ', Y(@center) + @radius, ',', X(@center) - @radius, ' ', Y(@center) + @radius, ',', X(@center) - @radius, ' ', Y(@center) - @radius, '))') ); select id, astext(coordinates), Distance(@center,line_segment) as dist FROM places where MBRContains(@bbox, line_segment) order by dist limit 10; Regards, Gavin Towey -Original Message- From: René Fournier [mailto:m...@renefournier.com] Sent: Wednesday, December 16, 2009 4:32 PM To: mysql Subject: Spatial extensions I have table with 2 million rows of geographic points (latitude, longitude). Given a location -- say, 52º, -113.9º -- what's the fastest way to query the 10 closest points (records) from that table? Currently, I'm using a simple two-column index to speed up queries: CREATE TABLE `places` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `latitude` decimal(10,8) NOT NULL, `longitude` decimal(12,8) NOT NULL PRIMARY KEY (`id`), KEY `latlng` (`latitude`,`longitude`) ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; My current query is fairly quick: SELECT SQL_NO_CACHE * FROM places WHERE latitude BETWEEN 51.98228037384 AND 52.033153677 AND longitude BETWEEN -113.94770681881 AND -113.86685484296; But I wonder a couple things: 1. Would MySQL's [seemingly anemic] spatial extensions would speed things up if I added a column of type POINT (and a corresponding spatial INDEX)? CREATE TABLE `places` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `latitude` decimal(10,8) NOT NULL, `longitude` decimal(12,8) NOT NULL, `coordinates` point NOT NULL, PRIMARY KEY (`id`), KEY `latlng` (`latitude`,`longitude`), KEY `coord` (`coordinates`(25)) ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 2. How would I write the query? ...Rene -- 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...@renefournier.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
copying a static table
i have a large myisam table (about 3gb) that is updated once a day in the middle of the night. when it is not being updated, is there any reason not to copy it out with rsync without shutting down the server or flush tables with read lock or whatever? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Help Save MySQL
Bruno, I wasn't exactly joking when i suggested users put up an offer but the complexity of doing so is huge. I don't know what value Sun or Oracle would put on MySQL as it stands and there are lots of issues not least those existing MySQL customers with contracts which need to be honoured by any new owner. Legal issues aside i would be up for the 'journey' but we'd need a lot of us or some deep pockets or some backing maybe from one of the big corporate users (Google maybe?). I don't think we'll see Oracle disposing of MySQL a bargain basement price unless a regulator demands it (unlikely). John === John Daisley MySQL 5.0 Certified Database Administrator (CMDBA) MySQL 5.0 Certified Developer Cognos BI Developer Telephone: +44(0)1283 537111 Mobile: +44(0)7812 451238 Email: john.dais...@butterflysystems.co.uk === Sent via HP IPAQ mobile device -Original Message- From: Bruno B. B. Magalhaes brunomagalh...@blackbean.com.br Sent: 17 December 2009 17:06 To: mysql@lists.mysql.com Subject: Re: Help Save MySQL Hi Guys, Let's say that every mysql developer (here I am thinking only persons, not companies) that wants mysql to go forward would contribute from $500,00 to $1500,00, how much are we talking about? And we would have a 100% community owned and community driven open source initiative... Course, there are many others management problems and legal issues to solve, but if anybody would join me I would be the first one! And also would be a REMARKABLE adventure and maybe the next step for the open source initiatives around the world... Best Regards Bruno B. B. Magalhães BLACKBEAN CONSULTORIA Rua Real Grandeza 193, Sala 210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9695-2263 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. On Dec 17, 2009, at 2:16 PM, Neil Aggarwal wrote: If that's what the price is going to be then perhaps I should offer 2€ or maybe MySQL users should get together submit a realistic offer. This sounds interesting... Get a community effort to accept donations and purchase MySQL. Then, put it under the GPL and make sure nobody owns it. Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS virtual server for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=brunomagalh...@blackbean.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: copying a static table
Tom, For MyISAM tables, as long as you aren't overlapping your update cycles, I can see nothing wrong with this. We used to rync to prepare slaves under inndb: do a broken rsync from the live data files on the master to the archives.. when that is complete, the result file is quite broken but significantly similar. Lock the tables and rsync again; the second time is MUCH faster and the result is correct. Against a 'quieted' MyISAM table, your technique sounds fool-proof. Of course, test your resulting tables before you commit to anything. - michael dykman On Thu, Dec 17, 2009 at 1:38 PM, Tom Worster f...@thefsb.org wrote: i have a large myisam table (about 3gb) that is updated once a day in the middle of the night. when it is not being updated, is there any reason not to copy it out with rsync without shutting down the server or flush tables with read lock or whatever? -- 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
Lesser of two values in list
I need to find the lesser of two values provided in a list. I know I can do it with a couple of IF's in a function, but I'd really like to know if there is already a function that I can use. min(12,3,1,4) of course doesnt work, but is there some other math function I am overlooking in MySql? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Lesser of two values in list
I need to find the lesser of two values provided in a list LEAST(). PB http://www.artfulsoftware.com - Cantwell, Bryan wrote: I need to find the lesser of two values provided in a list. I know I can do it with a couple of IF's in a function, but I'd really like to know if there is already a function that I can use. min(12,3,1,4) of course doesnt work, but is there some other math function I am overlooking in MySql? No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.427 / Virus Database: 270.14.111/2570 - Release Date: 12/17/09 08:30:00
Re: Join using Table1 or Table2 - depending on content of rel table
Miguel Vaz wrote: Hi, How would one go about doing this: - I have 3 tables: - A relationship table(REL), then TABLE1 and TABLE2: REL TABLE has fields: . ID . TYPE - type of event . ID_EVENT - id of event, but this id will either point to TABLE1 or TABLE2, depending on the content of the field TYPE Is it possible to do everything on the same select? I mean, the join will use a different table depending on the content of one of the fields. This join will retrieve the name of the event, either from TABLE1 or 2. Or should i just do a select to get the first row content, and then get the rest afterwards? Thanks, MV You can do it if you UNION your results together like this: ( SELECT ... FROM REL INNER JOIN TABLE1 ON REL.somecolumn = TABLE1.somecolumn AND REL.type = 'table1-type-value' WHERE ... ) UNION ( SELECT ... FROM REL INNER JOIN TABLE2 ON REL.somecolumn = TABLE2.somecolumn AND REL.type = 'table2-type-value' WHERE ... ) or, you can conditionally select which columns to return like this SELECT ... , if (REL.type = 'table1-type-value1', t1.column1, t2.column1) as column1 , ... FROM REL LEFT JOIN TABLE1 t1 on t1.somecolumn = REL.somecolumn and REL.type = 'table1-type-value' LEFT JOIN TABLE1 t2 on t2.somecolumn = REL.somecolumn and REL.type = 'table2-type-value' WHERE ... But typically,if your REF table refers to two separate tables, it will be much faster to access your data if you split it into two REF tables, one that points only to TABLE1 rows and one that points only to TABLE2 rows. That kind of separation of purpose is also known as normalization. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Innodb buffer pool size filling up
Good Morning all QUOTE: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up I have monitored this issue for the last couple of weeks and even though the buffer pool usage is increasing slowly, it is still heading to become 100% full. It used to vary in the past where it would increase and decrease each day as required, however this does not seem to be happening anymore. Does anybody know whether there is a process that is supposed to manage this or otherwise clear this occasionally that might not be working? We are getting quite a bit of issues with the client wanting to know why this is happening, however thus far I haven't been able to find any conclusive answers, not even through google. Any assistance would be appreciated. Thanking everyone in advance. Regards Machiel -Original Message- From: Jerry Schwartz [mailto:jschwa...@the-infoshop.com] Sent: 01 December 2009 10:04 PM To: 'machiel.richards'; 'Claudio Nanni' Cc: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up -Original Message- From: machiel.richards [mailto:machiel.richa...@gmail.com] Sent: Tuesday, December 01, 2009 6:17 AM To: 'Claudio Nanni' Cc: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up The size was at 2Gb and was recently changed to 3Gb in size during the last week of November (around the 23rd / 24th) and as of this morning was already sitting at 2.3gb used. [JS] At the others have said, the whole purpose of a buffer pool is to hold as much frequently used data as possible. That lowers the probability of having to do physical I/O, which is much slower than memory access. The buffer pool should be full. The total database size is about 750Mb. [JS] It does surprise me that the buffer pool fills up, even though it is three times the size of your database. My guess is that whatever mechanism is used to scavenge space in the buffer pool isn't triggered until the buffer pool is full; but that is simply a guess, I really have no idea. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Regards Machiel From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: 01 December 2009 01:12 PM To: machiel.richards Cc: mysql@lists.mysql.com Subject: Re: Innodb buffer pool size filling up That is basically its use, the buffer pool is the collection of all mysql innodb buffers, and after warm up it goes to keep all cacheable data. How big is your INNODB_BUFFER_POOL_SIZE ? Cheers Claudio 2009/12/1 machiel.richards machiel.richa...@gmail.com There are no errors in the logs at all. We have a script to calculate the percentages used and free in order to do daily,weekly and monthly reporting trend analyses and thus we notice the growth but no errors. -Original Message- From: Neil Aggarwal [mailto:n...@jammconsulting.com] Sent: 01 December 2009 08:55 AM To: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up Machiel: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up. Are you getting any errors or just noticing the buffer pool is full? I saw some error messages about the buffer pool size becoming a problem if the fscync is slow. Do you see any more info in the logs? Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=machiel.richa...@gmail.com -- 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: Help Save MySQL
I am in! Bruno B. B. Magalhaes wrote: Hi Guys, Let's say that every mysql developer (here I am thinking only persons, not companies) that wants mysql to go forward would contribute from $500,00 to $1500,00, how much are we talking about? And we would have a 100% community owned and community driven open source initiative... Course, there are many others management problems and legal issues to solve, but if anybody would join me I would be the first one! And also would be a REMARKABLE adventure and maybe the next step for the open source initiatives around the world... Best Regards Bruno B. B. Magalhães BLACKBEAN CONSULTORIA Rua Real Grandeza 193, Sala 210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9695-2263 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. On Dec 17, 2009, at 2:16 PM, Neil Aggarwal wrote: If that’s what the price is going to be then perhaps I should offer 2€ or maybe MySQL users should get together submit a realistic offer. This sounds interesting... Get a community effort to accept donations and purchase MySQL. Then, put it under the GPL and make sure nobody owns it. Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS virtual server for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=brunomagalh...@blackbean.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org