Re: Help saving MySQL
Claudio Nanni wrote: If he really cared about MySQL he would have not sold it or prevent from selling it to Sun. Initially her was convinced that MySQL as a division of Sun would really benefit the future of MySQL [1]. Obviously his relationship with Sun changed a bit later on. It shows that he really cared about MySQL and in his own way, he still cares for MySQL. This has nothing to do with earning money or selling things. People sell things to companies or other people and think that the new owner will be good for the product they cared about. Sometimes it doesn't work out like you think it would and to me it shows that someone still cares about that product if they try to do something about it. I am not talking about agreeing with mr. Widenius or not; that is a different discussion. [1] http://www.internetnews.com/dev-news/article.php/3760831/MySQL+Back+to+Its+Roots+via+Sun.htm Regard, Jigal. -- 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
Claudio Nanni wrote: Due to selling MySQL to Sun, Widenius earned about 16.6 million € in (...) This isn't about Monty or how much he earns. This is about protecting MySQL and open source, without which many of us wouldn't have a job. and besides, having dedicated 27 years of his life to MySQL I think he earned every last penny! === 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: Claudio Nanni claudio.na...@gmail.com Sent: 14 December 2009 06:30 To: mo...@askmonty.org Cc: mysql@lists.mysql.com Subject: Re: Help saving MySQL Due to selling MySQL to Sun, Widenius earned about 16.6 million € in capital gains http://en.wikipedia.org/wiki/Capital_gain in 2008 (16.8 million € total income), making the top 10 of highest earners in Finland that year. [wikipedia] Cheers Claudio Nanni Michael Widenius 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
Re: Duplicate Entry, But Table Empty!
Gods. What is this, a creche ? *plonk* On Sun, Dec 13, 2009 at 6:44 PM, Victor Subervi victorsube...@gmail.comwrote: On Sun, Dec 13, 2009 at 12:21 PM, Pinter Tibor tib...@tibyke.hu wrote: Victor Subervi wrote: Hi; mysql insert into *tem126072414516* (ProdID, Quantity) values (2, 2); mysql select * from *tem126072385457*; mysql insert into *tem126072414516* (ProdID, Quantity) values (2, 2); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*tem126072414516* (ProdID, Quantity) values (2, 2)' at line 1 Please advise. V
Re: Duplicate Entry, But Table Empty!
2009/12/13 Victor Subervi victorsube...@gmail.com: [...] Please advise. review your sql: you are inserting into tem126072414516 and selecting from tem126072385457 ( Asterisk in Pinter Tibor's mail means bold ) Greetings, Mattia Merzi. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Delete table definition without .frm files.
Heh. You'll need to just create an empty .frm file, and then issue the drop table statement. On Mon, Dec 14, 2009 at 6:11 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I really don't know how to elaborate this problem because it is quite strange- I have deleted .frm files but not by issuing Drop table command I deleted the folder from backend at folder level as there was some problem. Is there any way I can delete this definition from innodb datafile? Thanks in advance. -- Regards, Manasi Save Artificial Machines Private Limited
different type column and keys for EXPLAIN
I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM. Please refer to the below two statements. First query is checking for lastname 'clarke' where as second query is checking for lastname 'clark'. Rest everything is same with these two queries. However, the explain output shows ref for the first query and uses only one key for the first query whereas second query uses index_merge and both keys. mysql explain select count(*) from tblList where fldFIRSTNAME='michael' and fldLASTNAME='clarke'; ++-+---+--+--+-- ---+-+---+---+-+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra | ++-+---+--+--+-- ---+-+---+---+-+ | 1 | SIMPLE | tblList | ref | fldLASTNAME,fldFIRSTNAME | fldLASTNAME | 31 | const | 35043 | Using where | ++-+---+--+--+-- ---+-+---+---+-+ 1 row in set (0.07 sec) mysql explain select count(*) from tblList where fldFIRSTNAME='michael' and fldLASTNAME='clark'; ++-+---+-+-- +--+-+--+--+ -+ | id | select_type | table | type| possible_keys | key | key_len | ref | rows | Extra | ++-+---+-+-- +--+-+--+--+ -+ | 1 | SIMPLE | tblList | index_merge | fldLASTNAME,fldFIRSTNAME | fldLASTNAME,fldFIRSTNAME | 31,31 | NULL | 2190 | Using intersect(fldLASTNAME,fldFIRSTNAME); Using where; Using index | ++-+---+-+-- +--+-+--+--+ -+ 1 row in set (0.02 sec) What could be the problem here. Please help. Thanks, Manish
How to create new mysql instance
Hi, Can any body help me how to create new instance at the same mysql databas server in 5.0.85 community version ? Thanks Jeetendra Ranjan
Re: Delete table definition without .frm files.
Thanks Johan, I tried doing this. When I try to delete that empty table it is giving me an error saying Unknow table 'tblename'. I have created empty .frm file at folder level. can I create one from mysql. Thanks in advance. -- Regards, Manasi Save Quoting Johan De Meersman vegiv...@tuxera.be: Heh. You'll need to just create an empty .frm file, and then issue the drop table statement. On Mon, Dec 14, 2009 at 6:11 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I really don't know how to elaborate this problem because it is quite strange- I have deleted .frm files but not by issuing Drop table command I deleted the folder from backend at folder level as there was some problem. Is there any way I can delete this definition from innodb datafile? Thanks in advance. -- Regards, Manasi Save Artificial Machines Private Limited -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to create new mysql instance
Can any body help me how to create new instance at the same mysql databas server in 5.0.85 community version ? You might find useful: http://code.openark.org/blog/mysql/manually-installing-multiple-mysql-instances-on-linux-howto At least i used that last time i had to set 2 instances on my machine. iñigo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: different type column and keys for EXPLAIN
I don't think there's an actual problem as such, the optimiser is just making a decision to merge the lastname and firstname indices for the second query. At a guess, I'd say that the cardinality of clark in your lastname index is too high, so it uses both; the cardinality for clarke will be lower, and probably low enough that using just the one index becomes faster. No guarantees, though - that's just what it looks like from here. On Mon, Dec 14, 2009 at 1:03 PM, Manish Ranjan manish.ran...@stigasoft.comwrote: I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM. Please refer to the below two statements. First query is checking for lastname 'clarke' where as second query is checking for lastname 'clark'. Rest everything is same with these two queries. However, the explain output shows ref for the first query and uses only one key for the first query whereas second query uses index_merge and both keys. mysql explain select count(*) from tblList where fldFIRSTNAME='michael' and fldLASTNAME='clarke'; ++-+---+--+--+-- ---+-+---+---+-+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra | ++-+---+--+--+-- ---+-+---+---+-+ | 1 | SIMPLE | tblList | ref | fldLASTNAME,fldFIRSTNAME | fldLASTNAME | 31 | const | 35043 | Using where | ++-+---+--+--+-- ---+-+---+---+-+ 1 row in set (0.07 sec) mysql explain select count(*) from tblList where fldFIRSTNAME='michael' and fldLASTNAME='clark'; ++-+---+-+-- +--+-+--+--+ -+ | id | select_type | table | type| possible_keys | key | key_len | ref | rows | Extra | ++-+---+-+-- +--+-+--+--+ -+ | 1 | SIMPLE | tblList | index_merge | fldLASTNAME,fldFIRSTNAME | fldLASTNAME,fldFIRSTNAME | 31,31 | NULL | 2190 | Using intersect(fldLASTNAME,fldFIRSTNAME); Using where; Using index | ++-+---+-+-- +--+-+--+--+ -+ 1 row in set (0.02 sec) What could be the problem here. Please help. Thanks, Manish
Re: Delete table definition without .frm files.
No, if you were to try a create statement, mysql would notice the still-existing data in the InnoDB file. You just have to create a .frm file on the filesystem level, and assign it the correct permissions (on unix, ug=rw and owned by mysql:mysql or something similar). On Mon, Dec 14, 2009 at 2:13 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Thanks Johan, I tried doing this. When I try to delete that empty table it is giving me an error saying Unknow table 'tblename'. I have created empty .frm file at folder level. can I create one from mysql. Thanks in advance. -- Regards, Manasi Save Quoting Johan De Meersman vegiv...@tuxera.be: Heh. You'll need to just create an empty .frm file, and then issue the drop table statement. On Mon, Dec 14, 2009 at 6:11 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I really don't know how to elaborate this problem because it is quite strange- I have deleted .frm files but not by issuing Drop table command I deleted the folder from backend at folder level as there was some problem. Is there any way I can delete this definition from innodb datafile? Thanks in advance. -- Regards, Manasi Save Artificial Machines Private Limited -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
RE: different type column and keys for EXPLAIN
Thanks Johan. More to add here, execution time for query with 'clarke' is much higher than query with 'clark'. This is why it looks a bit strange behavior. From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Monday, December 14, 2009 8:33 PM To: Manish Ranjan Cc: mysql@lists.mysql.com Subject: Re: different type column and keys for EXPLAIN I don't think there's an actual problem as such, the optimiser is just making a decision to merge the lastname and firstname indices for the second query. At a guess, I'd say that the cardinality of clark in your lastname index is too high, so it uses both; the cardinality for clarke will be lower, and probably low enough that using just the one index becomes faster. No guarantees, though - that's just what it looks like from here. On Mon, Dec 14, 2009 at 1:03 PM, Manish Ranjan manish.ran...@stigasoft.com wrote: I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM. Please refer to the below two statements. First query is checking for lastname 'clarke' where as second query is checking for lastname 'clark'. Rest everything is same with these two queries. However, the explain output shows ref for the first query and uses only one key for the first query whereas second query uses index_merge and both keys. mysql explain select count(*) from tblList where fldFIRSTNAME='michael' and fldLASTNAME='clarke'; ++-+---+--+--+-- ---+-+---+---+-+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra | ++-+---+--+--+-- ---+-+---+---+-+ | 1 | SIMPLE | tblList | ref | fldLASTNAME,fldFIRSTNAME | fldLASTNAME | 31 | const | 35043 | Using where | ++-+---+--+--+-- ---+-+---+---+-+ 1 row in set (0.07 sec) mysql explain select count(*) from tblList where fldFIRSTNAME='michael' and fldLASTNAME='clark'; ++-+---+-+-- +--+-+--+--+ -+ | id | select_type | table | type| possible_keys | key | key_len | ref | rows | Extra | ++-+---+-+-- +--+-+--+--+ -+ | 1 | SIMPLE | tblList | index_merge | fldLASTNAME,fldFIRSTNAME | fldLASTNAME,fldFIRSTNAME | 31,31 | NULL | 2190 | Using intersect(fldLASTNAME,fldFIRSTNAME); Using where; Using index | ++-+---+-+-- +--+-+--+--+ -+ 1 row in set (0.02 sec) What could be the problem here. Please help. Thanks, Manish
RE: different type column and keys for EXPLAIN
Already did Analyze table and table is up to date. It seems like optimizer glitch only but I am not sure. Will try adding index hints. From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Monday, December 14, 2009 9:14 PM To: Manish Ranjan Cc: mysql@lists.mysql.com Subject: Re: different type column and keys for EXPLAIN Heh. Try running analyze table, so the index stats are correct. If that doesn't help, you may have stumbled upon an optimizer glitch, or maybe there's something happening that I'm not seeing. If all else fails, try to add index hints. On Mon, Dec 14, 2009 at 4:30 PM, Manish Ranjan manish.ran...@stigasoft.com wrote: Thanks Johan. More to add here, execution time for query with 'clarke' is much higher than query with 'clark'. This is why it looks a bit strange behavior. From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Monday, December 14, 2009 8:33 PM To: Manish Ranjan Cc: mysql@lists.mysql.com Subject: Re: different type column and keys for EXPLAIN I don't think there's an actual problem as such, the optimiser is just making a decision to merge the lastname and firstname indices for the second query. At a guess, I'd say that the cardinality of clark in your lastname index is too high, so it uses both; the cardinality for clarke will be lower, and probably low enough that using just the one index becomes faster. No guarantees, though - that's just what it looks like from here. On Mon, Dec 14, 2009 at 1:03 PM, Manish Ranjan manish.ran...@stigasoft.com wrote: I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM. Please refer to the below two statements. First query is checking for lastname 'clarke' where as second query is checking for lastname 'clark'. Rest everything is same with these two queries. However, the explain output shows ref for the first query and uses only one key for the first query whereas second query uses index_merge and both keys. mysql explain select count(*) from tblList where fldFIRSTNAME='michael' and fldLASTNAME='clarke'; ++-+---+--+--+-- ---+-+---+---+-+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra | ++-+---+--+--+-- ---+-+---+---+-+ | 1 | SIMPLE | tblList | ref | fldLASTNAME,fldFIRSTNAME | fldLASTNAME | 31 | const | 35043 | Using where | ++-+---+--+--+-- ---+-+---+---+-+ 1 row in set (0.07 sec) mysql explain select count(*) from tblList where fldFIRSTNAME='michael' and fldLASTNAME='clark'; ++-+---+-+-- +--+-+--+--+ -+ | id | select_type | table | type| possible_keys | key | key_len | ref | rows | Extra | ++-+---+-+-- +--+-+--+--+ -+ | 1 | SIMPLE | tblList | index_merge | fldLASTNAME,fldFIRSTNAME | fldLASTNAME,fldFIRSTNAME | 31,31 | NULL | 2190 | Using intersect(fldLASTNAME,fldFIRSTNAME); Using where; Using index | ++-+---+-+-- +--+-+--+--+ -+ 1 row in set (0.02 sec) What could be the problem here. Please help. Thanks, Manish
Re: different type column and keys for EXPLAIN
Heh. Try running analyze table, so the index stats are correct. If that doesn't help, you may have stumbled upon an optimizer glitch, or maybe there's something happening that I'm not seeing. If all else fails, try to add index hints. On Mon, Dec 14, 2009 at 4:30 PM, Manish Ranjan manish.ran...@stigasoft.comwrote: Thanks Johan. More to add here, execution time for query with 'clarke' is much higher than query with 'clark'. This is why it looks a bit strange behavior. From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Monday, December 14, 2009 8:33 PM To: Manish Ranjan Cc: mysql@lists.mysql.com Subject: Re: different type column and keys for EXPLAIN I don't think there's an actual problem as such, the optimiser is just making a decision to merge the lastname and firstname indices for the second query. At a guess, I'd say that the cardinality of clark in your lastname index is too high, so it uses both; the cardinality for clarke will be lower, and probably low enough that using just the one index becomes faster. No guarantees, though - that's just what it looks like from here. On Mon, Dec 14, 2009 at 1:03 PM, Manish Ranjan manish.ran...@stigasoft.com wrote: I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM. Please refer to the below two statements. First query is checking for lastname 'clarke' where as second query is checking for lastname 'clark'. Rest everything is same with these two queries. However, the explain output shows ref for the first query and uses only one key for the first query whereas second query uses index_merge and both keys. mysql explain select count(*) from tblList where fldFIRSTNAME='michael' and fldLASTNAME='clarke'; ++-+---+--+--+-- ---+-+---+---+-+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra | ++-+---+--+--+-- ---+-+---+---+-+ | 1 | SIMPLE | tblList | ref | fldLASTNAME,fldFIRSTNAME | fldLASTNAME | 31 | const | 35043 | Using where | ++-+---+--+--+-- ---+-+---+---+-+ 1 row in set (0.07 sec) mysql explain select count(*) from tblList where fldFIRSTNAME='michael' and fldLASTNAME='clark'; ++-+---+-+-- +--+-+--+--+ -+ | id | select_type | table | type| possible_keys | key | key_len | ref | rows | Extra | ++-+---+-+-- +--+-+--+--+ -+ | 1 | SIMPLE | tblList | index_merge | fldLASTNAME,fldFIRSTNAME | fldLASTNAME,fldFIRSTNAME | 31,31 | NULL | 2190 | Using intersect(fldLASTNAME,fldFIRSTNAME); Using where; Using index | ++-+---+-+-- +--+-+--+--+ -+ 1 row in set (0.02 sec) What could be the problem here. Please help. Thanks, Manish
Re: Help saving MySQL
Thank you John, You have hit on my point exactly. There are thousands on companies and 100's of thousands of jobs which are owed to this product. That is what we are defending. - michael dykman On Mon, Dec 14, 2009 at 4:27 AM, John Daisley mg_s...@hotmail.com wrote: Claudio Nanni wrote: Due to selling MySQL to Sun, Widenius earned about 16.6 million € in (...) This isn't about Monty or how much he earns. This is about protecting MySQL and open source, without which many of us wouldn't have a job. and besides, having dedicated 27 years of his life to MySQL I think he earned every last penny! === 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: Claudio Nanni claudio.na...@gmail.com Sent: 14 December 2009 06:30 To: mo...@askmonty.org Cc: mysql@lists.mysql.com Subject: Re: Help saving MySQL Due to selling MySQL to Sun, Widenius earned about 16.6 million € in capital gains http://en.wikipedia.org/wiki/Capital_gain in 2008 (16.8 million € total income), making the top 10 of highest earners in Finland that year. [wikipedia] Cheers Claudio Nanni Michael Widenius 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
Join Statement
Hi; I have the following: mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.00 sec) mysql select * from tem126080739853; +++--+-+--+ | ID | ProdID | Quantity | sizes | colorsShadesNumbersShort | +++--+-+--+ | 1 | 2 |2 | Extra-small | navy-blue:CC7722 | +++--+-+--+ 1 row in set (0.00 sec) mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.03 sec) mysql select SKU, Quantity, Name, Price, t.sizes, t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.00 sec) mysql select ID, SKU, Name, Price from products; ++--+---++ | ID | SKU | Name | Price | ++--+---++ | 2 | prodSKU1 | name1 | 555.22 | ++--+---++ 1 row in set (0.00 sec) So I'm at a loss as to why the above select join statement fails. Please advise. TIA, Victor
Re: Join Statement
... on t.ProdID-p.ID; Your join clause subtracts the two IDs, so it's on IDs that differ, and apparently there aren't any. PB - Victor Subervi wrote: Hi; I have the following: mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.00 sec) mysql select * from tem126080739853; +++--+-+--+ | ID | ProdID | Quantity | sizes | colorsShadesNumbersShort | +++--+-+--+ | 1 | 2 |2 | Extra-small | navy-blue:CC7722 | +++--+-+--+ 1 row in set (0.00 sec) mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.03 sec) mysql select SKU, Quantity, Name, Price, t.sizes, t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.00 sec) mysql select ID, SKU, Name, Price from products; ++--+---++ | ID | SKU | Name | Price | ++--+---++ | 2 | prodSKU1 | name1 | 555.22 | ++--+---++ 1 row in set (0.00 sec) So I'm at a loss as to why the above select join statement fails. Please advise. TIA, Victor No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.427 / Virus Database: 270.14.107/2564 - Release Date: 12/14/09 07:37:00
Re: Join Statement
On Mon, Dec 14, 2009 at 12:37 PM, Peter Brawley peter.braw...@earthlink.net wrote: ... on t.ProdID-p.ID; Your join clause subtracts the two IDs, so it's on IDs that differ, and apparently there aren't any. I beg to differ: mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID http://t.prodid-p.id/; Empty set (0.00 sec) mysql select * from tem126080739853; +++--+ -+--+ | ID | ProdID | Quantity | sizes | colorsShadesNumbersShort | +++--+-+--+ | 1 | 2 |2 | Extra-small | navy-blue:CC7722 | +++--+-+--+ 1 row in set (0.00 sec) mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID http://t.prodid-p.id/; Empty set (0.03 sec) mysql select SKU, Quantity, Name, Price, t.sizes, t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID http://t.prodid-p.id/; Empty set (0.00 sec) mysql select ID, SKU, Name, Price from products; ++--+---++ | ID | SKU | Name | Price | ++--+---++ | 2 | prodSKU1 | name1 | 555.22 | ++--+---++ 1 row in set (0.00 sec) t.ProdID == 2 p.ID == 2 That's a match. So why does my select join fail? TIA, V
Re: Help saving MySQL
i don't really thinks this is about open source or not. MySQL became more closed when Sun bought it and while i think that Oracle will try to get this even more closed IMO it's time to fork it as a really open source project. Monty could lead this. The are a lot of forks around that make MySQL even better that it is from SUN. All MySQL's Admin and developers will still get there jobs even with Oracle around, and maybe make more money of it. Linux situation in Oracle will change if Solaris became part of Oracle Umbrella as Oracle will push Solaris as there main operating system because it will get his proprietary software in all the stack. This is a good time to prove that OSS is really opensource and can survive this situation. Facundo. the difference between God and Larry Elison is that God doesn't think he is Larry Ellison ps: sorry my bad english, not my native language On 14/12/2009 02:09 p.m., Michael Dykman wrote: Thank you John, You have hit on my point exactly. There are thousands on companies and 100's of thousands of jobs which are owed to this product. That is what we are defending. - michael dykman On Mon, Dec 14, 2009 at 4:27 AM, John Daisleymg_s...@hotmail.com wrote: Claudio Nanni wrote: Due to selling MySQL to Sun, Widenius earned about 16.6 million € in (...) This isn't about Monty or how much he earns. This is about protecting MySQL and open source, without which many of us wouldn't have a job. and besides, having dedicated 27 years of his life to MySQL I think he earned every last penny! === 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: Claudio Nanniclaudio.na...@gmail.com Sent: 14 December 2009 06:30 To: mo...@askmonty.org Cc: mysql@lists.mysql.com Subject: Re: Help saving MySQL Due to selling MySQL to Sun, Widenius earned about 16.6 million € in capital gainshttp://en.wikipedia.org/wiki/Capital_gain in 2008 (16.8 million € total income), making the top 10 of highest earners in Finland that year. [wikipedia] Cheers Claudio Nanni Michael Widenius 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
Re: Join Statement
Victor Subervi wrote: On Mon, Dec 14, 2009 at 12:37 PM, Peter Brawley peter.braw...@earthlink.net wrote: ... on t.ProdID-p.ID; Your join clause subtracts the two IDs, so it's on IDs that differ, and apparently there aren't any. I beg to differ: ... So why does my select join fail? TIA, V It fails because you aren't using = but are using - . Switch your query to ... ON t.ProdID = p.ID and see if that works any better. -- 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: Join Statement
Victor, As far as I can see... change the '-' to '='. -- fixed query -- SELECT SKU , Quantity , Name , Price , p.sizes , p.colorsShadesNumbersShort FROM tem126080739853 t JOIN products p ON t.ProdID = p.ID ; -- end -- I didn't run this, or try to replicate it, it was just my observation on the query. Steven Staples -Original Message- From: Victor Subervi [mailto:victorsube...@gmail.com] Sent: December 14, 2009 11:26 AM To: mysql@lists.mysql.com Subject: Join Statement Hi; I have the following: mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.00 sec) mysql select * from tem126080739853; +++--+-+--+ | ID | ProdID | Quantity | sizes | colorsShadesNumbersShort | +++--+-+--+ | 1 | 2 |2 | Extra-small | navy-blue:CC7722 | +++--+-+--+ 1 row in set (0.00 sec) mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.03 sec) mysql select SKU, Quantity, Name, Price, t.sizes, t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.00 sec) mysql select ID, SKU, Name, Price from products; ++--+---++ | ID | SKU | Name | Price | ++--+---++ | 2 | prodSKU1 | name1 | 555.22 | ++--+---++ 1 row in set (0.00 sec) So I'm at a loss as to why the above select join statement fails. Please advise. TIA, Victor No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.716 / Virus Database: 270.14.101/2555 - Release Date: 12/14/09 02:37: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: Join Statement
Victor Subervi wrote: On Mon, Dec 14, 2009 a mysql select SKU, Quantity, Name, Price, t.sizes, t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID http://t.prodid-p.id/; Empty set (0.00 sec) mysql select ID, SKU, Name, Price from products; ++--+---++ | ID | SKU | Name | Price | ++--+---++ | 2 | prodSKU1 | name1 | 555.22 | ++--+---++ 1 row in set (0.00 sec) t.ProdID == 2 p.ID == 2 That's a match. So why does my select join fail? Because you're using a minus sign where you should be using an equals sign. This is what you're doing: select SKU, Quantity, Name, Price, t.sizes,t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID This is what you should be doing: select SKU, Quantity, Name, Price, t.sizes,t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID=p.ID Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join Statement
Victor, I beg to differ: mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID http://t.prodid-p.id/; ...on t.ProdID-p.ID... _subtracts_ the two IDs. To match them use '=' rather than '-'. PB - Victor Subervi wrote: On Mon, Dec 14, 2009 at 12:37 PM, Peter Brawley peter.braw...@earthlink.net wrote: ... on t.ProdID-p.ID; Your join clause subtracts the two IDs, so it's on IDs that differ, and apparently there aren't any. I beg to differ: mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID http://t.prodid-p.id/; Empty set (0.00 sec) mysql select * from tem126080739853; +++--+ -+--+ | ID | ProdID | Quantity | sizes | colorsShadesNumbersShort | +++--+-+--+ | 1 | 2 |2 | Extra-small | navy-blue:CC7722 | +++--+-+--+ 1 row in set (0.00 sec) mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID http://t.prodid-p.id/; Empty set (0.03 sec) mysql select SKU, Quantity, Name, Price, t.sizes, t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID http://t.prodid-p.id/; Empty set (0.00 sec) mysql select ID, SKU, Name, Price from products; ++--+---++ | ID | SKU | Name | Price | ++--+---++ | 2 | prodSKU1 | name1 | 555.22 | ++--+---++ 1 row in set (0.00 sec) t.ProdID == 2 p.ID == 2 That's a match. So why does my select join fail? TIA, V No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.427 / Virus Database: 270.14.107/2564 - Release Date: 12/14/09 07:37:00
all tables with certain type
hi list, is it possible to get a list of all tables with a certain type in one statement ? for now i collect all tables (show tables) and search for the type (show columns). Any way to circumvent that ? make it one statement ? re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: all tables with certain type
walter harms wrote: hi list, is it possible to get a list of all tables with a certain type in one statement ? for now i collect all tables (show tables) and search for the type (show columns). Any way to circumvent that ? make it one statement ? use information_schema; select column_name,data_type from columns where data_type='YOUR DATA TYPE HERE'; 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: Join Statement
On Mon, Dec 14, 2009 at 1:04 PM, Peter Brawley peter.braw...@earthlink.netwrote: ...on t.ProdID-p.ID... _subtracts_ the two IDs. To match them use '=' rather than '-'. Thank you for all of you that caught that. V
Optimization suggestions
I have following table structure, I have to use merge storage engine. Please have a look, and provide feedback if theres some thing wrong or if there's space for optimization. /*Table structure for table `messages2009` */ CREATE TABLE `messages2009` ( `id` varchar(36) NOT NULL default '', `folderid` varchar(36) NOT NULL default '', `fromid` int(11) NOT NULL default '0', `fromtype` varchar(10) NOT NULL default '', `toid` int(11) NOT NULL default '0', `totype` varchar(10) NOT NULL default '', `subject` varchar(255) default NULL, `body` text, `readbyrecipient` tinyint(1) NOT NULL default '0', `deletedbyauthor` tinyint(1) NOT NULL default '0', `deletedbyrecipient` tinyint(1) NOT NULL default '0', `threadid` varchar(36) NOT NULL default '', `senttime` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `folderid` (`folderid`), KEY `threadid` (`threadid`), KEY `inboxfolderindex` (`folderid`,`toid`,`totype`), KEY `sentitemsindex` (`fromid`,`fromtype`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*Table structure for table `messages` */ /*Merge table definition that covers all message tables*/ CREATE TABLE `messages` ( `id` varchar(36) NOT NULL default '', `folderid` varchar(36) NOT NULL default '', `fromid` int(11) NOT NULL default '0', `fromtype` varchar(10) NOT NULL default '', `toid` int(11) NOT NULL default '0', `totype` varchar(10) NOT NULL default '', `subject` varchar(255) default NULL, `body` text, `readbyrecipient` tinyint(1) NOT NULL default '0', `deletedbyauthor` tinyint(1) NOT NULL default '0', `deletedbyrecipient` tinyint(1) NOT NULL default '0', `threadid` varchar(36) NOT NULL default '', `senttime` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `folderid` (`folderid`), KEY `threadid` (`threadid`), KEY `inboxfolderindex` (`folderid`,`toid`,`totype`), KEY `sentitemsindex` (`fromid`,`fromtype`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`messages2009`); Sudhir NimavatSenior software engineer. Quick start global PVT LTD. Baroda - 390007 Gujarat, India Personally I'm always ready to learn, although I do not always like being taught The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: Help saving MySQL
On 12/14/09 1:49 AM, Claudio Nanni claudio.na...@gmail.com wrote: If he really cared about MySQL he would have not sold it or prevent from selling it to Sun. i don't see the logic in this sentence. -- 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 12/13/09 11:23 AM, Neil Aggarwal n...@jammconsulting.com wrote: Doug: I do not see anyone willing to put up anything to support mysql... you don't like it... put up a billion dollars an take control or shut the heck up! Assuming MySQL as it stands today is 100% open source, I think an easier path is to create a new project from the currently existing sources. Call it something else, dbXYZ for instance. Then, Oracle can do anything it wants to MySQL. I am pretty sure if Michael leads the project, the open source community will follow. Can that be done or did I miss something? i think it can be done. If that is the case, what can Oracle really do to hurt MySQL? my guess is that it would be better if mysql did not need to fork. the harm would be the confusion caused to users. it would be sad if users chose not to use mysql because they had listened to scare stories about its possible future. if trust busters can be cajoled into making sure oracle doesn't harm oss mysql, that would be better. -- 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
You build a green park where children can play. Then you sell the park to a private company. The company can: not mantain it so that the park becomes junkies place, have people pay to access it or even close it. I, the builder, would not start a crusade or weep after I have sold it, Once it is on the market it is like any other goods. I am on MySQL almost ten years but I am not scared of switching to Postgres, to a fork, start a new project, or quit dba for other real open source spirit journey. The community and open spirit is important not the product. Thats my view. Thanks Monty, always, for your gift. Claudio On 14 dec 2009 19:36, Tom Worster f...@thefsb.org wrote: On 12/14/09 1:49 AM, Claudio Nanni claudio.na...@gmail.com wrote: If he really cared about My... i don't see the logic in this sentence.
Re: Help saving MySQL
and thanks for sharing your view. here's mine: mysql was sold to sun, a company with a long and deep commitment to oos. while there were obviously risks to the sale, one plausible motive (among others) is that a company like sun would be better placed to further develop, market and support mysql, get it into the hands of more users (sun is a trusted name even among the conservative and risk-averse parts of the market), leverage their service and support organization, etc. and if they can make money off it then maybe they will invest in development too. so i see it as reasonable to have believed that sun would be good for mysql, indeed that sun would be good next step for mysql in its journey. hence i don't see that this sale necessarily implies that monty did not really care about mysql. i'm not advocating these arguments. i'm simply saying that, whether one agrees with such arguments or not, there could plausibly exist conditions under which sale of mysql to sun was compatible with really caring about it. On 12/14/09 2:11 PM, Claudio Nanni claudio.na...@gmail.com wrote: You build a green park where children can play. Then you sell the park to a private company. The company can: not mantain it so that the park becomes junkies place, have people pay to access it or even close it. I, the builder, would not start a crusade or weep after I have sold it, Once it is on the market it is like any other goods. I am on MySQL almost ten years but I am not scared of switching to Postgres, to a fork, start a new project, or quit dba for other real open source spirit journey. The community and open spirit is important not the product. Thats my view. Thanks Monty, always, for your gift. Claudio On 14 dec 2009 19:36, Tom Worster f...@thefsb.org wrote: On 12/14/09 1:49 AM, Claudio Nanni claudio.na...@gmail.com wrote: If he really cared about My... i don't see the logic in this sentence. -- 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 we are on the same line, I put it to excess to spotlight the crucial point, how it was based on the 'good will' of Sun, which I simply adhore and thank only for existing I was actually happy when that happened because with Sun, MySQL was perfectly fitting in the big picture of Sun products, and could have had the big jump integrating it with some application server / framework (glassfish, j2ee, etc). But big fish eat small fish, and once you are in the ocean.run! Thanks Tom Claudio Tom Worster wrote: and thanks for sharing your view. here's mine: mysql was sold to sun, a company with a long and deep commitment to oos. while there were obviously risks to the sale, one plausible motive (among others) is that a company like sun would be better placed to further develop, market and support mysql, get it into the hands of more users (sun is a trusted name even among the conservative and risk-averse parts of the market), leverage their service and support organization, etc. and if they can make money off it then maybe they will invest in development too. so i see it as reasonable to have believed that sun would be good for mysql, indeed that sun would be good next step for mysql in its journey. hence i don't see that this sale necessarily implies that monty did not really care about mysql. i'm not advocating these arguments. i'm simply saying that, whether one agrees with such arguments or not, there could plausibly exist conditions under which sale of mysql to sun was compatible with really caring about it. On 12/14/09 2:11 PM, Claudio Nanni claudio.na...@gmail.com wrote: You build a green park where children can play. Then you sell the park to a private company. The company can: not mantain it so that the park becomes junkies place, have people pay to access it or even close it. I, the builder, would not start a crusade or weep after I have sold it, Once it is on the market it is like any other goods. I am on MySQL almost ten years but I am not scared of switching to Postgres, to a fork, start a new project, or quit dba for other real open source spirit journey. The community and open spirit is important not the product. Thats my view. Thanks Monty, always, for your gift. Claudio On 14 dec 2009 19:36, Tom Worster f...@thefsb.org wrote: On 12/14/09 1:49 AM, Claudio Nanni claudio.na...@gmail.com wrote: If he really cared about My... i don't see the logic in this sentence. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Optimization suggestions
After one very quick look, the index on folderid alone is unnecessary since you have another index in which that field is the high-order field. On Mon, Dec 14, 2009 at 12:31 PM, Sudhir N sudhir_nima...@yahoo.com wrote: I have following table structure, I have to use merge storage engine. Please have a look, and provide feedback if theres some thing wrong or if there's space for optimization. /*Table structure for table `messages2009` */ CREATE TABLE `messages2009` ( `id` varchar(36) NOT NULL default '', `folderid` varchar(36) NOT NULL default '', `fromid` int(11) NOT NULL default '0', `fromtype` varchar(10) NOT NULL default '', `toid` int(11) NOT NULL default '0', `totype` varchar(10) NOT NULL default '', `subject` varchar(255) default NULL, `body` text, `readbyrecipient` tinyint(1) NOT NULL default '0', `deletedbyauthor` tinyint(1) NOT NULL default '0', `deletedbyrecipient` tinyint(1) NOT NULL default '0', `threadid` varchar(36) NOT NULL default '', `senttime` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `folderid` (`folderid`), KEY `threadid` (`threadid`), KEY `inboxfolderindex` (`folderid`,`toid`,`totype`), KEY `sentitemsindex` (`fromid`,`fromtype`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*Table structure for table `messages` */ /*Merge table definition that covers all message tables*/ CREATE TABLE `messages` ( `id` varchar(36) NOT NULL default '', `folderid` varchar(36) NOT NULL default '', `fromid` int(11) NOT NULL default '0', `fromtype` varchar(10) NOT NULL default '', `toid` int(11) NOT NULL default '0', `totype` varchar(10) NOT NULL default '', `subject` varchar(255) default NULL, `body` text, `readbyrecipient` tinyint(1) NOT NULL default '0', `deletedbyauthor` tinyint(1) NOT NULL default '0', `deletedbyrecipient` tinyint(1) NOT NULL default '0', `threadid` varchar(36) NOT NULL default '', `senttime` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `folderid` (`folderid`), KEY `threadid` (`threadid`), KEY `inboxfolderindex` (`folderid`,`toid`,`totype`), KEY `sentitemsindex` (`fromid`,`fromtype`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`messages2009`); Sudhir NimavatSenior software engineer. Quick start global PVT LTD. Baroda - 390007 Gujarat, India Personally I'm always ready to learn, although I do not always like being taught The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
RE: Optimization suggestions
Id should probably be an auto_incrementing INT, if you still need a unique text identifier, then I would make a separate field. Though my opinion isn't the only way; there is much debate on natural vs. surrogate keys. I would normalize folderid into a lookup in another table, and make folderid an INT value. Threadid is another field that would probably be better as an INT. As for your indexes, they depend completely on what type of queries you're going to be running. Once you know that, then you can test them using sample data and EXPLAIN. http://dev.mysql.com/doc/refman/5.0/en/explain.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html About normalization: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html Regards, Gavin Towey -Original Message- From: Sudhir N [mailto:sudhir_nima...@yahoo.com] Sent: Monday, December 14, 2009 10:31 AM To: Mysql Subject: Optimization suggestions I have following table structure, I have to use merge storage engine. Please have a look, and provide feedback if theres some thing wrong or if there's space for optimization. /*Table structure for table `messages2009` */ CREATE TABLE `messages2009` ( `id` varchar(36) NOT NULL default '', `folderid` varchar(36) NOT NULL default '', `fromid` int(11) NOT NULL default '0', `fromtype` varchar(10) NOT NULL default '', `toid` int(11) NOT NULL default '0', `totype` varchar(10) NOT NULL default '', `subject` varchar(255) default NULL, `body` text, `readbyrecipient` tinyint(1) NOT NULL default '0', `deletedbyauthor` tinyint(1) NOT NULL default '0', `deletedbyrecipient` tinyint(1) NOT NULL default '0', `threadid` varchar(36) NOT NULL default '', `senttime` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `folderid` (`folderid`), KEY `threadid` (`threadid`), KEY `inboxfolderindex` (`folderid`,`toid`,`totype`), KEY `sentitemsindex` (`fromid`,`fromtype`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*Table structure for table `messages` */ /*Merge table definition that covers all message tables*/ CREATE TABLE `messages` ( `id` varchar(36) NOT NULL default '', `folderid` varchar(36) NOT NULL default '', `fromid` int(11) NOT NULL default '0', `fromtype` varchar(10) NOT NULL default '', `toid` int(11) NOT NULL default '0', `totype` varchar(10) NOT NULL default '', `subject` varchar(255) default NULL, `body` text, `readbyrecipient` tinyint(1) NOT NULL default '0', `deletedbyauthor` tinyint(1) NOT NULL default '0', `deletedbyrecipient` tinyint(1) NOT NULL default '0', `threadid` varchar(36) NOT NULL default '', `senttime` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `folderid` (`folderid`), KEY `threadid` (`threadid`), KEY `inboxfolderindex` (`folderid`,`toid`,`totype`), KEY `sentitemsindex` (`fromid`,`fromtype`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`messages2009`); Sudhir NimavatSenior software engineer. Quick start global PVT LTD. Baroda - 390007 Gujarat, India Personally I'm always ready to learn, although I do not always like being taught The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.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
InnoDB Corrupted databases (innodb_force_recovery not working)
Hey there, I have recently imported the database files from a crashed server and I am currently trying to get the new server running with the old data. However, after starting the MySQL Server, I only get the following error message: 091214 20:51:46 mysqld started InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on InnoDB: Skipping log redo InnoDB: Error: page n:o stored in the page read in is 538976288, should be 3! InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 3. InnoDB: You may have to recover from a backup. 091214 20:51:47 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 2020202020202020202020202020202020202020202020202020202020202020202020202020 20202020202020202020202020202020202020202020202020202020202$ 091214 20:51:47 InnoDB: Page checksum 1605920614, prior-to-4.0.14-form checksum 3161919040 InnoDB: stored checksum 538976288, prior-to-4.0.14-form stored checksum 236985105 InnoDB: Page lsn 538976288 538976288, low 4 bytes of lsn at page end 10651 InnoDB: Page number (if stored to page already) 538976288, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 538976288 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 3. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. After that, the following error message is looped: 091214 20:51:47 InnoDB: Error: trying to access tablespace 538976288 page no. 538976288, InnoDB: but the tablespace does not exist or is just being dropped. The MySQL server then is unusable and is taking 99% CPU ressources. I tried to use the innodb_force_recovery option in the my.cfg file, but that did not change this behavior. Does anyone have an idea for me to restore the databases? I have no backup and rely on getting this files back to work. Any help is appreciated, thanks in advance, Lukas Lukas C. C. Hempel Delux Group - Approaching future. http://www.delux.me/ www.delux.me Postfach 10 02 10 D-48051 Münster This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. Delux Credit: http://www.delux-credit.com/ www.delux-credit.com Delux Host: http://www.delux-host.com/ www.delux-host.com Delux Software: http://www.delux.me/ www.delux.me Admin panel: http://admin.delux-host.com/ http://admin.delux-host.com Paypal: mailto:pay...@delux-host.com pay...@delux-host.com Delux is a company of Lukas Hempel, Tax ID: 337/5105/2023
Re: InnoDB Corrupted databases (innodb_force_recovery not working)
Lukas, If you can't get innodb_force_recovery to work, then you might have to try to recover the data with these tools: http://code.google.com/p/innodb-tools/ Regards Baron -- 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
MySQL is a huge and trusted brand, yes you could run with a fork but at the end of the day it will never be MySQL. Its that MySQL brand name which sells the underlying software, support packages, books, training, certifications and the services of a huge number of Sun and independent consultants. There are already a few forks out there but you rarely hear about them because they are not MySQL. MySQL is more than just some code, its hundreds of developers, its a huge community, its this list, its a trusted brand name, etc, etc, etc. Theres so many things which make MySQL what it is and you could copy every last line of code into a fork, give it a fancy name and spend millions on advertising and promotion but you still won't have anything even close to MySQL. The MySQL code will always survive in some form, regardless of who owns it but MySQL could be coming to an end if we don't support it. === 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: Tom Worster f...@thefsb.org Sent: 14 December 2009 20:02 To: claudio.na...@gmail.com Cc: mysql@lists.mysql.com Subject: Re: Help saving MySQL and thanks for sharing your view. here's mine: mysql was sold to sun, a company with a long and deep commitment to oos. while there were obviously risks to the sale, one plausible motive (among others) is that a company like sun would be better placed to further develop, market and support mysql, get it into the hands of more users (sun is a trusted name even among the conservative and risk-averse parts of the market), leverage their service and support organization, etc. and if they can make money off it then maybe they will invest in development too. so i see it as reasonable to have believed that sun would be good for mysql, indeed that sun would be good next step for mysql in its journey. hence i don't see that this sale necessarily implies that monty did not really care about mysql. i'm not advocating these arguments. i'm simply saying that, whether one agrees with such arguments or not, there could plausibly exist conditions under which sale of mysql to sun was compatible with really caring about it. On 12/14/09 2:11 PM, Claudio Nanni claudio.na...@gmail.com wrote: You build a green park where children can play. Then you sell the park to a private company. The company can: not mantain it so that the park becomes junkies place, have people pay to access it or even close it. I, the builder, would not start a crusade or weep after I have sold it, Once it is on the market it is like any other goods. I am on MySQL almost ten years but I am not scared of switching to Postgres, to a fork, start a new project, or quit dba for other real open source spirit journey. The community and open spirit is important not the product. Thats my view. Thanks Monty, always, for your gift. Claudio On 14 dec 2009 19:36, Tom Worster f...@thefsb.org wrote: On 12/14/09 1:49 AM, Claudio Nanni claudio.na...@gmail.com wrote: If he really cared about My... i don't see the logic in this sentence. -- 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: Help saving MySQL
Yeah, plus just think about all those job sites and resumes that would have to change from LAMP Developer to LAxP Developer if MySQL forked and changed names! Myself included. All those poor recruiters would need to be trained to know that dbXYZ == MySQL. LOL. How many LADP Developsers do you ever hear about?! ;-) (for those that live in a cave, the D would be for http://drizzle.org/;) ;-) -Original Message- From: John Daisley [mailto:mg_s...@hotmail.com] Sent: Monday, December 14, 2009 2:52 PM To: f...@thefsb.org ; claudio.na...@gmail.com Cc: mysql@lists.mysql.com Subject: RE: Help saving MySQL MySQL is a huge and trusted brand, yes you could run with a fork but at the end of the day it will never be MySQL. Its that MySQL brand name which sells the underlying software, support packages, books, training, certifications and the services of a huge number of Sun and independent consultants. There are already a few forks out there but you rarely hear about them because they are not MySQL. MySQL is more than just some code, its hundreds of developers, its a huge community, its this list, its a trusted brand name, etc, etc, etc. Theres so many things which make MySQL what it is and you could copy every last line of code into a fork, give it a fancy name and spend millions on advertising and promotion but you still won't have anything even close to MySQL. The MySQL code will always survive in some form, regardless of who owns it but MySQL could be coming to an end if we don't support it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to not lock anything?
All, I am using MySQL currently, but am starting to think that maybe I don't really need to use an RDBMS. The data I am storing ends up getting indexed with Sphinx because I have full-text indexes for about 40 million records. I have an items table that is heavily updated with 40 million records every 1 or 2 days and I need all those items indexed so they can be searched. The problem that I'm having is that the table is constantly locked because an insert or delete is being performed. I am playing with InnoDB vs MyIsam and have been trying to figure out how to get the best performance. I actually don't care about dirty reads, however, and wouldn't mind if all the 40 mm records could be read/inserted/updated/deleted without any locking at all. Are there known solutions for the kind of storage I am looking for? Anyone have any pointers? Is there a MySQL Storage Engine designed for this kind of usage, or is there a another server that is commonly used along with MySQL for this type of thing? -- Dante -- D. Dante Lorenso da...@larkspark.com 972-333-4139 -- -- D. Dante Lorenso da...@lorenso.com 972-333-4139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How to not lock anything?
I am using MySQL currently, but am starting to think that maybe I don't really need to use an RDBMS You can use flat file storage, but that would be inefficient. I guess you could try to create a custom system and keep all the records in RAM. That would require some decent coding to make it efficient. How big is your full dataset? If it is small, the NDB engine stores indexed columns in RAM. That might work for you. MyISAM tables is probabably going to be the easiest to implement and it will still be quite fast. Unless you really need something fancy, I think you should go with 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: How to not lock anything?
I am using MySQL currently, but am starting to think that maybe I don't really need to use an RDBMS I just thought of another thing: It is always faster to insert into a table without indexes than inserting into a table with indexes. I also think it might be faster to clear the table, insert your data, and then put the indexes back. Try those to see if they help. 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
mysql server optimization
Dear all, i am nowing having a problem with the mysql server optimization, i have 20 database on a server,each database is about 80Gb,the sql seems very slow,almost 5s.and the server i/o is so high,when i check the processlist,the 'copying to tmp table' state takes a long time. i have already use index,but the sql use lots of 'and','or','order by', and for some reason i can not optimization the sql,i hope to do some optimization on mysql server to mitigate this phenomenon，could any one give me some suggestion? thanks. my server is linux,8CPU and 4G memery,the my.cnf is: [mysqld] port= 3306 skip-locking skip-name-resolve key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512M net_buffer_length = 8K read_buffer_size = 512K read_rnd_buffer_size = 512M myisam_sort_buffer_size = 8M table_cache = 1024 log-bin=mysql-bin binlog_format=mixed -- Tianjing
CSV pain and suffering with punctuation and non-ASCII characters
I am trying to import a database of notable quotations. It is really quite simple, but I'm finding importing via phpMyAdmin is (IMHO) erroneous. The database came from FileMaker Pro 7, and there are no options for changing the output. There are two choices: 1) tabs between fields (with no opportunity to include the tab character in a field), and 2) CSV, which double-quotes fields and has commas in-between, which apparently doubles embedded quotes. I am using UTF-8 bin as the character set, and as the encoding for all fields. The problem is that numerous punctuation characters appear to terminate the reading of a field, whether imported as a TAB file or as a CSV file. For example, importing a quote with a Context field of: The Hitchhiker's Guide to the Galaxy results in a field containing: The Hitchhiker whether I use TAB or CSV. Note that this in an ASCII single quote character, not something exotic. I have also noted this on fields that contain curly quotes. I am using phpMyAdmin's CSV using LOAD DATA option, checking Replace table data with file, terminating fields with a comma, enclosing fields with double quotes, and blanking (or leaving the '\', it doesn't matter) the Fields escaped by field. It seems to me that using CSV, with double-quoted, comma separated fields should tell the import process, Hands off until you see another double quote! And that using TAB format, nothing between TAB characters should be interpreted. Why are characters inside the double quotes or tabs being interpreted? I have not tried mysqlimport from the command line, assuming (perhaps incorrectly) that phpMyAdmin was simply passing things along, and not interpreting them. Ah, one light in the tunnel: my Quote field was TEXT. Changing it to BLOB preserves non-ASCII characters, but I still see the strange behaviour noted above with a single quote, which was in a VARCHAR(256) UTF8 field. Thanks for whatever help you can offer! My pants just went on a wild rampage through a Long Island Bowling Alley! -- Zippy the Pinhead Jan Steinman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: CSV pain and suffering with punctuation and non-ASCII characters
Jan: For example, importing a quote with a Context field of: The Hitchhiker's Guide to the Galaxy results in a field containing: The Hitchhiker You are going to have to escape quotes, so your string should look like this: The Hitchhiker\'s Guide to the Galaxy See this page: http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html Whenever I run into this situation, I use the Ostermiller Excel CSV parser: http://ostermiller.org/utils/ExcelCSV.html with a custom class I wrote to escape strings using the MySQL format. I don't know if there is an off the shelf tool to do what you want. Maybe someone has one. 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
error while creating trigger
Hi, I am getting error while creating a simple trigger mysql CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW - begin - INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) - VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); - INSERT INTO DELETED_CONSUMER_ACTION_AUDIT(CONSUMER_ID,CAMPAIGN_ID,CAMPAIGN_ACTION_ID,STATE_ID,LAST_MOD_TIME) VALUES(old.CONSUMER_ID,old.CAMPAIGN_ID,old.CAMPAIGN_ACTION_ID,old.STATE_ID,old.LAST_MOD_TIME); - END; - // ERROR 1054 (42S22): Unknown column 'CAMPAIGN_ID' in 'OLD' But this column is exist in DELETED_CONSUMER_ACTION_AUDIT table. What are the possible causes of this error ? Thanks in advance Jeetendra Ranjan
Re: error while creating trigger
Jeetendra, ERROR 1054 (42S22): Unknown column 'CAMPAIGN_ID' in 'OLD' But this column is exist in DELETED_CONSUMER_ACTION_AUDIT table. But does it exist in consumer_profile? PB - Jeetendra Ranjan wrote: Hi, I am getting error while creating a simple trigger mysql CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW - begin - INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) - VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); - INSERT INTO DELETED_CONSUMER_ACTION_AUDIT(CONSUMER_ID,CAMPAIGN_ID,CAMPAIGN_ACTION_ID,STATE_ID,LAST_MOD_TIME) VALUES(old.CONSUMER_ID,old.CAMPAIGN_ID,old.CAMPAIGN_ACTION_ID,old.STATE_ID,old.LAST_MOD_TIME); - END; - // ERROR 1054 (42S22): Unknown column 'CAMPAIGN_ID' in 'OLD' But this column is exist in DELETED_CONSUMER_ACTION_AUDIT table. What are the possible causes of this error ? Thanks in advance Jeetendra Ranjan No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.427 / Virus Database: 270.14.107/2564 - Release Date: 12/14/09 07:37:00
Re: Optimization suggestions
Tables has varchar36 primary keys, because I use UUID and not auto increment columns. I have verified all the queries that are going to run on tables using Explain and it show correct index being used. Thanks SN Sudhir NimavatSenior software engineer. Quick start global PVT LTD. Baroda - 390007 Gujarat, India Personally I'm always ready to learn, although I do not always like being taught From: Gavin Towey gto...@ffn.com To: Sudhir N sudhir_nima...@yahoo.com; Mysql mysql@lists.mysql.com Sent: Tue, 15 December, 2009 2:16:53 AM Subject: RE: Optimization suggestions Id should probably be an auto_incrementing INT, if you still need a unique text identifier, then I would make a separate field. Though my opinion isn't the only way; there is much debate on natural vs. surrogate keys. I would normalize folderid into a lookup in another table, and make folderid an INT value. Threadid is another field that would probably be better as an INT. As for your indexes, they depend completely on what type of queries you're going to be running. Once you know that, then you can test them using sample data and EXPLAIN. http://dev.mysql.com/doc/refman/5.0/en/explain.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html About normalization: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html Regards, Gavin Towey -Original Message- From: Sudhir N [mailto:sudhir_nima...@yahoo.com] Sent: Monday, December 14, 2009 10:31 AM To: Mysql Subject: Optimization suggestions I have following table structure, I have to use merge storage engine. Please have a look, and provide feedback if theres some thing wrong or if there's space for optimization. /*Table structure for table `messages2009` */ CREATE TABLE `messages2009` ( `id` varchar(36) NOT NULL default '', `folderid` varchar(36) NOT NULL default '', `fromid` int(11) NOT NULL default '0', `fromtype` varchar(10) NOT NULL default '', `toid` int(11) NOT NULL default '0', `totype` varchar(10) NOT NULL default '', `subject` varchar(255) default NULL, `body` text, `readbyrecipient` tinyint(1) NOT NULL default '0', `deletedbyauthor` tinyint(1) NOT NULL default '0', `deletedbyrecipient` tinyint(1) NOT NULL default '0', `threadid` varchar(36) NOT NULL default '', `senttime` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `folderid` (`folderid`), KEY `threadid` (`threadid`), KEY `inboxfolderindex` (`folderid`,`toid`,`totype`), KEY `sentitemsindex` (`fromid`,`fromtype`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*Table structure for table `messages` */ /*Merge table definition that covers all message tables*/ CREATE TABLE `messages` ( `id` varchar(36) NOT NULL default '', `folderid` varchar(36) NOT NULL default '', `fromid` int(11) NOT NULL default '0', `fromtype` varchar(10) NOT NULL default '', `toid` int(11) NOT NULL default '0', `totype` varchar(10) NOT NULL default '', `subject` varchar(255) default NULL, `body` text, `readbyrecipient` tinyint(1) NOT NULL default '0', `deletedbyauthor` tinyint(1) NOT NULL default '0', `deletedbyrecipient` tinyint(1) NOT NULL default '0', `threadid` varchar(36) NOT NULL default '', `senttime` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `folderid` (`folderid`), KEY `threadid` (`threadid`), KEY `inboxfolderindex` (`folderid`,`toid`,`totype`), KEY `sentitemsindex` (`fromid`,`fromtype`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`messages2009`); Sudhir NimavatSenior software engineer. Quick start global PVT LTD. Baroda - 390007 Gujarat, India Personally I'm always ready to learn, although I do not always like being taught The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.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 The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: different type column and keys for EXPLAIN
Manish, On Mon, Dec 14, 2009 at 05:33:43PM +0530, Manish Ranjan wrote: I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM. Please refer to the below two statements. First query is checking for lastname 'clarke' where as second query is checking for lastname 'clark'. Rest everything is same with these two queries. However, the explain output shows ref for the first query and uses only one key for the first query whereas second query uses index_merge and both keys. ... What could be the problem here. Please help. ref and index_merge are two possible plans for both of the queries. The choice whether to use ref or index_merge depends on cost calculations, which, in turn, depends on estimates of numbers of records that one will get for conditions in the WHERE clause. It seems that the storage engine reports different estimates for number of matching records for lastname='clark' and lastname='clarke', and hence the query plans are different. This is a normal situation. Does that cause any problems for you? That is, do you observe that one of the queries is unccecessarily slow (i.e. much slower than you could make it to run by using some hint?) BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog -- 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
Tom Worster f...@thefsb.org : my guess is that it would be better if mysql did not need to fork. the harm would be the confusion caused to users. I really dont think it is a problem. _Users_ lazzy to follow the fork will stay with MySQL under Oracle. _Users_ less lazzy will switch. Developpers will always know what to do. -- 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
