inserting sets of data
Hi; I have a column defined as a set. How do I insert data into that column? Please give me an example. TIA, Victor
Re: inserting sets of data
On Sat, Dec 12, 2009 at 9:54 AM, Victor Subervi victorsube...@gmail.comwrote: Hi; I have a column defined as a set. How do I insert data into that column? Please give me an example. TIA, Victor Lots of examples here: http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html David -- There is more hunger for love and appreciation in this world than for bread.- Mother Teresa
Re: inserting sets of data
On Sat, Dec 12, 2009 at 10:58 AM, David Giragosian dgiragos...@gmail.comwrote: On Sat, Dec 12, 2009 at 9:54 AM, Victor Subervi victorsube...@gmail.comwrote: Hi; I have a column defined as a set. How do I insert data into that column? Please give me an example. TIA, Victor Lots of examples here: http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html Perfect. Thanks! V
Re: Join on a where clause.
Hello Paul,
Paul Halliday wrote:
I have 2 tables:
1) Event Data
2) Mappings
The query should return something like this:
Hits IP Country Code
20213.136.52.29 SE
I am trying this:
SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip),
mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN
'2009-12-06 20:00' and '2009-12-07 20:00:00' AND event.src_ip BETWEEN
'mappings.start_ip' AND 'mappings.end_ip' GROUP BY event.src_ip ORDER
BY count DESC LIMIT 20;
Am I supposed to do a join somewhere? Do joins even apply in a where
clause? or am I totally off the mark.
Singularly, the queries look like this:
SELECT cc FROM mappings WHERE INET_ATON('src_ip') BETWEEN start_ip AND end_ip;
SELECT COUNT(src_ip) AS count, INET_NTOA(src_ip) FROM event WHERE
timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00' GROUP
BY src_ip ORDER BY count DESC LIMIT 20;
Technically speaking, you are already doing a JOIN in your query. The
comma operator in the FROM clause combined with the WHERE conditions
make your query logically equivalent to the following rewrite:
SELECT COUNT(event.src_ip) AS count
, INET_NTOA(event.src_ip)
, mappings.cc
FROM event
INNER JOIN mappings
ON event.src_ip BETWEEN 'mappings.start_ip' AND 'mappings.end_ip'
WHERE event.timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00'
GROUP BY event.src_ip
ORDER BY count DESC
LIMIT 20;
However, as we document in the manual, we have demoted the execution
precedence of the comma operator to form what I like to call an
implicit join to be evaluated AFTER any explicit JOIN clauses.
http://dev.mysql.com/doc/refman/5.0/en/join.html
What this means is that you may get better performance out of an
explicit join than you do an implicit join.
Also, you may want to consider rewriting your matching condition so that
it can use an index or a combination of indexes on your `start_ip` and
`end_ip` columns (notice my use of backticks ` ` not single quotes ' '
to identify column names) by rewriting your condition as an AND
comparison instead of a BETWEEN comparison
ON event.src_ip = mappings.start_ip
AND event.src_ip = mappings.end_ip
The way it is written now: col BETWEEN colA and colB is not one
of the cases that we have an optimized and generalized execution plan to
handle well. The separate conditions, like I wrote in my example, is an
optimized situation and has a much better likelihood of using an index
during its evaluation.
I would think that an index on both columns would work better than two
single-column indexes.
ALTER TABLE event ADD KEY(src_ip, end_ip);
Warmest regards,
--
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: Multiple joins from same table?
Terry Van de Velde wrote: Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. I am basically looking to have the following outcome: Oct. 30 - Titans vs. Hawks (7:30 PM) Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM) I would like this handled by MySQL instead of PHP if possible. Schedule table 'id' int, 'date_time' datetime, 'visitor' tinyint 'home' tinyint teams table 'id' int 'team_no' smallint, 'team_name' varchar (20) SELECT schedule.date_time, teams.team_name FROM schedule, sojhlteams WHERE schedule.visitor = teams.team_no Any help is appreciated. The trick to using the same table two or more times in the same query is through something called aliases when you alias a column or table you give it a different name and will make it easier to manage. Something like this is what you are after SELECT s.date_time , th.team_name home_team , ta.team_name away_team FROM schedule s INNER JOIN teams th ON th.team_no = s.home INNER JOIN teams ta ON ta.team_no = s.visitor Here you can see that I aliased the `teams` table twice. Once to handle the home team information (th) and once for the away team info (ta). I also aliased the team_name columns to make them less confusing labeling one as home_team and other as away_team. I think that once you get a grip on how to use aliases, all of this multiple-table stuff will start to become much easier. -- 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
Help saving MySQL
Subject: Help saving MySQL from Oracle! I, Michael Monty Widenius, the creator of MySQL, is asking you urgently to help save MySQL from Oracle's clutches. Without your immediate help Oracle might get to own MySQL any day now. By writing to the European Commission (EC) you can support this cause and make things much harder for Oracle. What this text is about: - Summary of what is happening - What Oracle has not promised - Oracles past behavior with Open Source - Help spread this information (Jump to 'What I want to ask you to do') - Example of email to send to the commission (Jump to 'send this to:') I have spent the last 27 years creating and working on MySQL and I hope, together with my team of MySQL core developers, to work on it for many more years. Oracle is trying to buy Sun, and since Sun bought MySQL last year, Oracle would then own MySQL. With your support, there is a good chance that the EC (from which Oracle needs approval) could prevent this from happening. Without your support, it might not. The EC is our last big hope now because the US government approved the deal while Europe is still worried about the effects. Instead of just working out this with the EC and agree on appropriate remedies to correct the situation, Oracle has instead contacted hundreds of their big customers and asked them to write to the EC and require unconditional acceptance of the deal. According what I been told, Oracle has promised to the customers, among other things, that they will put more money into MySQL development than what Sun did and that if they would ever abandon MYSQL, a fork will appear and take care of things. However just putting money into development is not proof that anything useful will ever be delivered or that MySQL will continue to be a competitive force in the market as it's now. As I already blogged about before, http://monty-says.blogspot.com/2009/10/importance-of-license-model-of-mysql-or.html, a fork is not enough to keep MySQL alive for all future, if Oracle, as the copyright holder of MySQL, would at any point decide that they should kill MySQL or make parts of MySQL closed source. Oracle claims that it would take good care of MySQL but let's face the facts: Unlike ten years ago, when MySQL was mostly just used for the web, it has become very functional, scalable and credible. Now it's used in many of the world's largest companies and they use it for an increasing number of purposes. This not only scares but actually hurts Oracle every day. Oracle salespeople have to lower prices all the time to compete with MySQL when companies start new projects. Some companies even migrate existing projects from Oracle to MySQL to save money. Of course Oracle has a lot more features, but MySQL can already do a lot of things for which Oracle is often used and helps people save a lot of money. Over time MySQL can do to Oracle what the originally belittled Linux did to commercial Unix (roughly speaking). So I just don't buy it that Oracle will be a good home for MySQL. A weak MySQL is worth about one billion dollars per year to Oracle, maybe more. A strong MySQL could never generate enough income for Oracle that they would want to cannibalize their real cash cow. I don't think any company has ever done anything like that. That's why the EC is skeptical and formalized its objections about a month ago. Richard Stallman agrees that it's very important which company owns MySQL, that Oracle should not be allowed to buy it and that it can't just be taken care of by a community of volunteers: http://keionline.org/ec-mysql Oracle has NOT promised (as far as I know and certainly not in a legally binding manner) that: - They keep (all of) MySQL under an open source license - Not add closed source parts, modules or required tools. - To not rise MySQL license or MySQL support prices - To release new MySQL versions in a regular and timely manner. - To continue with dual licensing and always provide affordable commercial licenses to MySQL to those who needs them (to storage vendors and application vendors) or provide MySQL under a more permissive license - To develop MySQL as an Open Source project - To actively work with the community - Apply submitted patches in a timely manner - Not discriminate patches that makes MySQL compete more with Oracles other products. - To ensure that MySQL is improved also in manners that make it compete even more with Oracles' main offering. From looking at how Oracle handled the InnoDB acquisition, I don't have high hopes that Oracle will do the above right if not required to do so: For InnoDB: - Bug fixes where done (but this was done under a contractual obligation) - New features, like compression that was announced before acquisition, took 3 years to implement - No time tables or insight into development - The community where not allowed to participate in development - Patches from users (like Google) that would have increased performance was not
RE: Multiple joins from same table?
Shawn, Thanks for the info, it does help indeed. I had also replied back to Gary to thank him as well, but I don't think that it made it to the list... so to Gary, thanks as well. Regards, Terry Terry Van de Velde Email: bya...@rogers.com Phone: (519) 685-0295 Cell: (519) 619-0987 -Original Message- From: shawn.gr...@sun.com [mailto:shawn.gr...@sun.com] Sent: December 12, 2009 4:39 PM To: Terry Van de Velde Cc: mysql@lists.mysql.com Subject: Re: Multiple joins from same table? Terry Van de Velde wrote: Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. I am basically looking to have the following outcome: Oct. 30 - Titans vs. Hawks (7:30 PM) Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM) I would like this handled by MySQL instead of PHP if possible. Schedule table 'id' int, 'date_time' datetime, 'visitor' tinyint 'home' tinyint teams table 'id' int 'team_no' smallint, 'team_name' varchar (20) SELECT schedule.date_time, teams.team_name FROM schedule, sojhlteams WHERE schedule.visitor = teams.team_no Any help is appreciated. The trick to using the same table two or more times in the same query is through something called aliases when you alias a column or table you give it a different name and will make it easier to manage. Something like this is what you are after SELECT s.date_time , th.team_name home_team , ta.team_name away_team FROM schedule s INNER JOIN teams th ON th.team_no = s.home INNER JOIN teams ta ON ta.team_no = s.visitor Here you can see that I aliased the `teams` table twice. Once to handle the home team information (th) and once for the away team info (ta). I also aliased the team_name columns to make them less confusing labeling one as home_team and other as away_team. I think that once you get a grip on how to use aliases, all of this multiple-table stuff will start to become much easier. -- 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=bya...@rogers.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.716 / Virus Database: 270.14.105/2561 - Release Date: 12/12/09 14:39:00 -- 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 hope the only reason this thread is so quiet is because we are all busy notifying our friends. There are a hell of a lot more users invested in MySQL than those who read this list. Spread the word! Monty is not asking us to help him: he is asking you to help yourselves. MySQL has never been more important than it is today! - michael dykman On Sat, Dec 12, 2009 at 5:29 PM, Michael Widenius michael.widen...@gmail.com wrote: Subject: Help saving MySQL from Oracle! I, Michael Monty Widenius, the creator of MySQL, is asking you urgently to help save MySQL from Oracle's clutches. Without your immediate help Oracle might get to own MySQL any day now. By writing to the European Commission (EC) you can support this cause and make things much harder for Oracle. What this text is about: - Summary of what is happening - What Oracle has not promised - Oracles past behavior with Open Source - Help spread this information (Jump to 'What I want to ask you to do') - Example of email to send to the commission (Jump to 'send this to:') I have spent the last 27 years creating and working on MySQL and I hope, together with my team of MySQL core developers, to work on it for many more years. Oracle is trying to buy Sun, and since Sun bought MySQL last year, Oracle would then own MySQL. With your support, there is a good chance that the EC (from which Oracle needs approval) could prevent this from happening. Without your support, it might not. The EC is our last big hope now because the US government approved the deal while Europe is still worried about the effects. Instead of just working out this with the EC and agree on appropriate remedies to correct the situation, Oracle has instead contacted hundreds of their big customers and asked them to write to the EC and require unconditional acceptance of the deal. According what I been told, Oracle has promised to the customers, among other things, that they will put more money into MySQL development than what Sun did and that if they would ever abandon MYSQL, a fork will appear and take care of things. However just putting money into development is not proof that anything useful will ever be delivered or that MySQL will continue to be a competitive force in the market as it's now. As I already blogged about before, http://monty-says.blogspot.com/2009/10/importance-of-license-model-of-mysql-or.html, a fork is not enough to keep MySQL alive for all future, if Oracle, as the copyright holder of MySQL, would at any point decide that they should kill MySQL or make parts of MySQL closed source. Oracle claims that it would take good care of MySQL but let's face the facts: Unlike ten years ago, when MySQL was mostly just used for the web, it has become very functional, scalable and credible. Now it's used in many of the world's largest companies and they use it for an increasing number of purposes. This not only scares but actually hurts Oracle every day. Oracle salespeople have to lower prices all the time to compete with MySQL when companies start new projects. Some companies even migrate existing projects from Oracle to MySQL to save money. Of course Oracle has a lot more features, but MySQL can already do a lot of things for which Oracle is often used and helps people save a lot of money. Over time MySQL can do to Oracle what the originally belittled Linux did to commercial Unix (roughly speaking). So I just don't buy it that Oracle will be a good home for MySQL. A weak MySQL is worth about one billion dollars per year to Oracle, maybe more. A strong MySQL could never generate enough income for Oracle that they would want to cannibalize their real cash cow. I don't think any company has ever done anything like that. That's why the EC is skeptical and formalized its objections about a month ago. Richard Stallman agrees that it's very important which company owns MySQL, that Oracle should not be allowed to buy it and that it can't just be taken care of by a community of volunteers: http://keionline.org/ec-mysql Oracle has NOT promised (as far as I know and certainly not in a legally binding manner) that: - They keep (all of) MySQL under an open source license - Not add closed source parts, modules or required tools. - To not rise MySQL license or MySQL support prices - To release new MySQL versions in a regular and timely manner. - To continue with dual licensing and always provide affordable commercial licenses to MySQL to those who needs them (to storage vendors and application vendors) or provide MySQL under a more permissive license - To develop MySQL as an Open Source project - To actively work with the community - Apply submitted patches in a timely manner - Not discriminate patches that makes MySQL compete more with Oracles other products. - To ensure that MySQL is improved also in manners that make it compete even more with Oracles' main offering. From looking at how
Re: Help saving MySQL
On Sun, Dec 13, 2009 at 11:48 AM, Michael Dykman mdyk...@gmail.com wrote: I hope the only reason this thread is so quiet is because we are all busy notifying our friends. There are a hell of a lot more users invested in MySQL than those who read this list. Spread the word! Let's stand up today to face the evil! -- 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
Ryan Chan ryanchan...@gmail.com : On Sun, Dec 13, 2009 at 11:48 AM, Michael Dykman mdyk...@gmail.com wrote: I hope the only reason this thread is so quiet is because we are all busy notifying our friends. There are a hell of a lot more users invested in MySQL than those who read this list. Spread the word! Let's stand up today to face the evil! Could people translate that message to the maximum available langages? -- Architecte Informatique chez Blueline/Gulfsat: Administration Systeme, Recherche Developpement +261 34 29 155 34 / +261 33 11 207 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
