Re: example when indexing hurts simple select?
Hi Gasper, MySql allows to package the index - to get its size smaller and to gain performance. Some information about that can be found here: http://www.mysqlperformanceblog.com/2006/05/13/to-pack-or-not-to-pack-myisam-key-compression/ Gaspar Bakos schrieb: Hi, RE: Have you tried analyze table x; This was quick: mysql analyze table TEST; Table Op Msg_typeMsg_text CAT.TEST analyze status Table is already up to date -- mysql show index from TEST; +---+++--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--+-+---+-+--++--++-+ | TEST | 1 | MMtestfiel |1 | MMtestfiel | A | 838 | NULL | NULL | | BTREE | NULL| | TEST | 1 | MMi_m |1 | MMi_m | A | 25857 | NULL | NULL | YES | BTREE | NULL| +---+++--+-+---+-+--++--++-+ --- I am trying to figure out what the Packed field means. Gaspar smime.p7s Description: S/MIME Cryptographic Signature
I cannot login MySQL Network
Hello, my name is Takanobu Kawabe. I have gotten a MySQL Network, and the service level is gold , I could login MySQL Network on May 20th , but now I can't login. I have tried some times last week, and today , but I cannot do it. If I try to login (URL is https://network.mysql.com), the following message is displayed. Your email address [EMAIL PROTECTED] has multiple contracts with MySQL Network. You must choose only one contract to use for this login session. As I can continue to login there, when I press the button Continue Login, the following error message is displayed. Error: You need to be a MySQL Network customer in order to have access to this service. I want to know what is the reason of this error, and to solve this problem. Where can I callto solve this problem? Please tell me the telephone number.
Re: I cannot login MySQL Network
Takanobu Kawabe schrieb: Hello, my name is Takanobu Kawabe. I have gotten a MySQL Network, and the service level is gold , I could login MySQL Network on May 20th , but now I can't login. I have tried some times last week, and today , but I cannot do it. If I try to login (URL is https://network.mysql.com), the following message is displayed. Your email address [EMAIL PROTECTED] has multiple contracts with MySQL Network. You must choose only one contract to use for this login session. As I can continue to login there, when I press the button Continue Login, the following error message is displayed. Error: You need to be a MySQL Network customer in order to have access to this service. I want to know what is the reason of this error, and to solve this problem. Where can I callto solve this problem? Please tell me the telephone number. こんにちわ！ One thing might be that your email adress is not valid. Is it the exact error you get? = [EMAIL PROTECTED] is this the email you see on the error? If yes, then it could be because of your character encoding. Use UTF-8 encoding in your browser. Otherwise here is a telephone number: +1-208-514-4780 (14:00 - 01:00 UTC, Mon-Fri) Greetings Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to backup and restore
Hello Ben, all! Ben Burford wrote: Hello, This is my first message, I'm an absolute beginner in Mysql. I did a defaul t installation of version 3.23.58 on RH Linux AS3.0. [[...]] I will not comment on the backup issue, that has been done already. However, I _strongly_ urge you to upgrade to 4.1 (or, even better, 5.0) as soon as possible, or to scrap your 3.23 and restart from scratch using 4.1 or 5.0. There have been so many improvements (both bug fixes and features) in comparison to 3.23 that I do not see any good reason to use that old version for which you will never get upgrades, as for any difficulty you will get the hint: Upgrade! Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limiting results from joins
Hey list; Consider this statement: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id; Now, each unique product_id from products can have more than one entry in the items table, but I only want to fetch the one which fullfills a certain criteria. In this case, I want the statement only to JOIN the row if the column item_updated from the items table equals 1. Is this the proper way to solve this: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id i.item_id = (SELECT item_id FROM items i2 WHERE i2.item_updated = 1); I find the above solution VERY slow, almost as if I have missed out on a very fundamental part of the logic - but it does get the work done. How could I speed this up, or solve it in another statement? Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limiting results from joins
Kim Christensen schrieb: Hey list; Consider this statement: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id; Now, each unique product_id from products can have more than one entry in the items table, but I only want to fetch the one which fullfills a certain criteria. In this case, I want the statement only to JOIN the row if the column item_updated from the items table equals 1. Is this the proper way to solve this: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id i.item_id = (SELECT item_id FROM items i2 WHERE i2.item_updated = 1); I find the above solution VERY slow, almost as if I have missed out on a very fundamental part of the logic - but it does get the work done. How could I speed this up, or solve it in another statement? Regards Use INNER JOIN :) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: {Spam?} Limiting results from joins
How about:
select * from products p
inner join manufactors m on p.manufactor_id=m.manufactor_id
inner join items i on p.product_id=i.product_id and i.item_updated=1
Donna
Kim Christensen [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
06/12/2006 06:15 AM
Please respond to
[EMAIL PROTECTED]
To
mysql@lists.mysql.com
cc
Subject
{Spam?} Limiting results from joins
Hey list;
Consider this statement:
SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id
= m.manufactor_id p.product_id = i.product_id;
Now, each unique product_id from products can have more than one
entry in the items table, but I only want to fetch the one which
fullfills a certain criteria. In this case, I want the statement only
to JOIN the row if the column item_updated from the items table
equals 1.
Is this the proper way to solve this:
SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id
= m.manufactor_id p.product_id = i.product_id i.item_id =
(SELECT item_id FROM items i2 WHERE i2.item_updated = 1);
I find the above solution VERY slow, almost as if I have missed out on
a very fundamental part of the logic - but it does get the work done.
How could I speed this up, or solve it in another statement?
Regards
--
Kim Christensen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
CONFIDENTIALITY NOTICE:This email is intended solely for the person or
entity to which it is addressed and may contain confidential and/or
protected health information. Any duplication, dissemination, action
taken in reliance upon, or other use of this information by persons or
entities other than the intended recipient is prohibited and may violate
applicable laws. If this email has been received in error, please notify
the sender and delete the information from your system. The views
expressed in this email are those of the sender and may not necessarily
represent the views of IntelliCare.
Re: Limiting results from joins
On 6/12/06, Barry [EMAIL PROTECTED] wrote: Kim Christensen schrieb: Hey list; Consider this statement: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id; Now, each unique product_id from products can have more than one entry in the items table, but I only want to fetch the one which fullfills a certain criteria. In this case, I want the statement only to JOIN the row if the column item_updated from the items table equals 1. Is this the proper way to solve this: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id i.item_id = (SELECT item_id FROM items i2 WHERE i2.item_updated = 1); I find the above solution VERY slow, almost as if I have missed out on a very fundamental part of the logic - but it does get the work done. How could I speed this up, or solve it in another statement? Regards Use INNER JOIN :) That worked great, really cut the time on my queries! However, how would one filter out the INNER JOIN statement if it returns more than one row to JOIN? I have reworked my previous table setup and query so that I need the row that has the lowest value of item_updated, not particularly 1. Here's what it looks like right now: SELECT * FROM products p INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id INNER JOIN items i ON i.product_id = p.product_id The problem is, that each entry in products may occur more than once in items, and they are identified by product_id in both tables. How do I filter out the results from the last INNER JOIN by certain criterias? I want the INNER JOIN to only return the row from items which has the lowest value in the column item_updated. As it is right now, MySQL returns a row for each time the product occurs in the items table, which is not what I want :-) Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limiting results from joins
On 6/12/06, Barry [EMAIL PROTECTED] wrote: Kim Christensen schrieb: Hey list; Consider this statement: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id; Now, each unique product_id from products can have more than one entry in the items table, but I only want to fetch the one which fullfills a certain criteria. In this case, I want the statement only to JOIN the row if the column item_updated from the items table equals 1. Is this the proper way to solve this: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id i.item_id = (SELECT item_id FROM items i2 WHERE i2.item_updated = 1); I find the above solution VERY slow, almost as if I have missed out on a very fundamental part of the logic - but it does get the work done. How could I speed this up, or solve it in another statement? Regards Use INNER JOIN :) That worked great, really cut the time on my queries! However, how would one filter out the INNER JOIN statement if it returns more than one row to JOIN? I have reworked my previous table setup and query so that I need the row that has the lowest value of item_updated, not particularly 1. Here's what it looks like right now: SELECT * FROM products p INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id INNER JOIN items i ON i.product_id = p.product_id The problem is, that each entry in products may occur more than once in items, and they are identified by product_id in both tables. How do I filter out the results from the last INNER JOIN by certain criterias? I want the INNER JOIN to only return the row from items which has the lowest value in the column item_updated. As it is right now, MySQL returns a row for each time the product occurs in the items table, which is not what I want :-) (Sorry if this is a dupe post, but I sent it from the wrong address) -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Limiting results from joins
-- Here's what it looks like right now: SELECT * FROM products p INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id INNER JOIN items i ON i.product_id = p.product_id The problem is, that each entry in products may occur more than once in items, and they are identified by product_id in both tables. How do I filter out the results from the last INNER JOIN by certain criterias? I want the INNER JOIN to only return the row from items which has the lowest value in the column item_updated. -- Just add: ORDER BY i.item_updated GROUP BY i.product_id (assuming that item_updated and product_id are in table i) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Search and Replace a word in memo field
Hello friends. In need your help. I have a table with a field Name: Varchar(255). In this field Name I have a detail and I need replace in all records a word for another. For example: I have vhs and I want to change the word vhs for the word dvd in all records. How can I do this? -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-huge.cnf quite outdated
I was getting a little OT there Gaspar. It might be better for me to file a feature request on mysl bug reporting page for any un-necessary functionality and the associated documentation to be removed from mysql in version 6.0.x, rather than hijacking this thread! Regards Keith On Sun, 11 Jun 2006, Gaspar Bakos wrote: To: Keith Roberts [EMAIL PROTECTED] From: Gaspar Bakos [EMAIL PROTECTED] Subject: Re: my-huge.cnf quite outdated Hi, Keith, RE: This seems to be the way things are with mysql nowdays. Is it not time for the developers to take a serious look into culling all the outdated and multiple ways of accomplishing the same thing from mysql and the documentation? This is a somewhat different subject. But you are right about it. On the other hand, I have been using MySQL since 2001, and I enjoy looking at the old syntax, and seeing how it changed helps me understanding what the new syntax means. Back to my-huge.cnf, I am sure there are many people reading the list who run MySQL on big-big servers, and they must have figured out how to optimize it. I am curious about their advice. Any opinions of the following : ? [mysqld] key_buffer_size=1024M myisam_sort_buffer_size=256M sort_buffer_size=256M bulk_insert_buffer_size=64M join_buffer_size=64M max_connections=5 read_buffer_size=8M read_rnd_buffer_size=8M net_buffer_length=1M max_allowed_packet=16M Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search and Replace a word in memo field
update table set field=REPLACE(field, 'vhs', 'dvd'); Gabriel Mahiques [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hello friends. In need your help. I have a table with a field Name: Varchar(255). In this field Name I have a detail and I need replace in all records a word for another. For example: I have vhs and I want to change the word vhs for the word dvd in all records. How can I do this? -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search and Replace a word in memo field
but I have a problem with this solution, because I have the words in the middle of the phrase. For example: Movie Kill Bill Vol 1 in vhs format, and I want to replace IT with Movie Kill Bill Vol 1 in dvd format. In Another case I have: VHS Trade Konami. . and I want to replace with DVD Trade Konami. so basically the word that I need to replace is located in the middle of the phrase. João Cândido de Souza Neto escribió: update table set field=REPLACE(field, 'vhs', 'dvd'); Gabriel Mahiques [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hello friends. In need your help. I have a table with a field Name: Varchar(255). In this field Name I have a detail and I need replace in all records a word for another. For example: I have vhs and I want to change the word vhs for the word dvd in all records. How can I do this? -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search and Replace a word in memo field
On Monday 12 June 2006 14:16, Gabriel Mahiques wrote: but I have a problem with this solution, because I have the words in the middle of the phrase. For example: Movie Kill Bill Vol 1 in vhs format, and I want to replace IT with Movie Kill Bill Vol 1 in dvd format. In Another case I have: VHS Trade Konami. . and I want to replace with DVD Trade Konami. so basically the word that I need to replace is located in the middle of the phrase. That's what the string function replace() does. Scanned by mailCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search and Replace a word in memo field
Thanks friends. Duncan Hill escribió: On Monday 12 June 2006 14:16, Gabriel Mahiques wrote: but I have a problem with this solution, because I have the words in the middle of the phrase. For example: Movie Kill Bill Vol 1 in vhs format, and I want to replace IT with Movie Kill Bill Vol 1 in dvd format. In Another case I have: VHS Trade Konami. . and I want to replace with DVD Trade Konami. so basically the word that I need to replace is located in the middle of the phrase. That's what the string function replace() does. Scanned by mailCritical. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-huge.cnf quite outdated
Gaspar Bakos schrieb: Hi, Isn't the my-huge.cnf in the MySQL 5.0.22 distribution quite outdated? It says for systems with 512Mb RAM or more. Nowdays this is pretty basic setup, and 'huge' is probably something in excess of 4Gb RAM. I wonder if anyone has a recommendation for truly huge systems. For example a dual CPU AMD opteron system with 4Gb (or 8Gb) RAM that is fully devoted to serving the mysql daemon. The config I have (see below) has been tuned to be optimal for creating indexes on a large (100Gb+) single database table. It works fine (although not satisfactory), but I worry that some parameters may have an optimal value or range, and it does not make sense to increase them like crazy. Any opinions of the following : ? [mysqld] key_buffer_size=1024M myisam_sort_buffer_size=256M sort_buffer_size=256M bulk_insert_buffer_size=64M join_buffer_size=64M max_connections=5 read_buffer_size=8M read_rnd_buffer_size=8M net_buffer_length=1M max_allowed_packet=16M # Cheers, # Gaspar Seriously. You should get a hang on the mysql.cnf vars and values. Guess we would answer to everyone on the list who wishes to optimize his cnf. Oh, i have add super X RAMs with latencies of blah blah. Please i think my cnf is outdated can somone help me? Or: Oh, i have added a HD with 2times more rounds per/m can you update my cnf PLZ? And yes. You can tweak the shit out of the mysql.cnf files. You have to test yourself on your system. My only opinion would be to test it on a server which has the same measures, so that the live project don't start acting weird. And btw. the cnf files wrk with even bigger tables than you have. Not optimal but okay. Every special server needs special handling. there is no the one and only you have to do it this way way Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limiting results from joins
On 6/12/06, Peter Lauri [EMAIL PROTECTED] wrote: -- Here's what it looks like right now: SELECT * FROM products p INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id INNER JOIN items i ON i.product_id = p.product_id The problem is, that each entry in products may occur more than once in items, and they are identified by product_id in both tables. How do I filter out the results from the last INNER JOIN by certain criterias? I want the INNER JOIN to only return the row from items which has the lowest value in the column item_updated. -- Just add: ORDER BY i.item_updated GROUP BY i.product_id (assuming that item_updated and product_id are in table i) That gives me the result that I'm after, the only problem seems to be that the sorting is made before the matching - on all the rows (huge execution time) - how can I prevent this? MVH -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: increase the search speed
On Sunday 11 June 2006 12:25 am, Octavian Rasnita wrote:
Hi,
I have the following table:
CREATE TABLE `z` (
`hash` varchar(16) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`body` text NOT NULL,
FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
I have tried the following query:
select sql_calc_found_rows hash, title, substr(body, 1, 250) as preview,
match(title, body) against('deputat') as rank from z where match(title,
body) against('deputat' in boolean mode) order by rank desc limit 0,20;
Depending on the language at task, you might be able to get a bit of a speed
increase if you pull the preview from the database and pull it into the
language. I'm not quite sure how optimized MySQL's substr operation is vs.
other languages.
The problem is that this query takes more than 12 seconds, and for some
other one-word searches it takes almost 30 seconds, and this is very
much for a table with only less than 200.000 records.
I'm not quite sure how much that can be optimized. You might want to just
check the MySQL manual entry on fulltext searches for more information (and
user comments):
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
--
Chris White
PHP Programmer/DB Fawn
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-huge.cnf quite outdated (fwd)
-- Forwarded message --
Date: Mon, 12 Jun 2006 11:39:11 -0400 (EDT)
From: Gaspar Bakos
To: Barry [EMAIL PROTECTED]
Subject: Re: my-huge.cnf quite outdated
Hello, Barry,
RE:
Guess we would answer to everyone on the list who wishes to optimize his
cnf.
I don't guess, and don't even expect that you answer to everyone.
Oh, i have add super X RAMs with latencies of blah blah. Please i
think my cnf is outdated can somone help me? Or: Oh, i have added a
HD with 2times more rounds per/m can you update my cnf PLZ?
These are not what I asked, they are pretty negative exaggarations.
And yes. You can tweak the shit out of the mysql.cnf files.
You have to test yourself on your system.
This is what I am doing, and in the meantime, looking for experience,
and also sharing mine.
And btw. the cnf files wrk with even bigger tables than you have.
Not optimal but okay.
How big?
Every special server needs special handling. there is no the one and
only you have to do it this way way
OK, so why is there a my-{small,large,huge}.cnf ?
They are guidelines for typical systems and applications.
But they are quite outdated, as typical systems changed.
All in all: I was looking for _typical_ configs for 4GB+ machines and
100Gb+ tables.
G
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL naming convention?
Hi! When writting a SQL scripts, do you follow any standard SQL naming convention? Similar to what described in http://kurafire.net/articles/sql-convention Does MySQL provides such naming convention guidelines and best practices? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Limiting results from joins
Take away the GROUP BY thing. And after that you just check if the rows are in the order that you want. The upper row would be the one that GROUP BY will take. Are you sure that you want the lowest value in the item_update? I would like to have the highest value. If you want the latest date, then you do ORDER BY i.item_updated DESC GROUP BY i.product_id -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kim Christensen Sent: Monday, June 12, 2006 9:15 PM To: Peter Lauri Cc: MySQL List Subject: Re: Limiting results from joins On 6/12/06, Peter Lauri [EMAIL PROTECTED] wrote: -- Here's what it looks like right now: SELECT * FROM products p INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id INNER JOIN items i ON i.product_id = p.product_id The problem is, that each entry in products may occur more than once in items, and they are identified by product_id in both tables. How do I filter out the results from the last INNER JOIN by certain criterias? I want the INNER JOIN to only return the row from items which has the lowest value in the column item_updated. -- Just add: ORDER BY i.item_updated GROUP BY i.product_id (assuming that item_updated and product_id are in table i) That gives me the result that I'm after, the only problem seems to be that the sorting is made before the matching - on all the rows (huge execution time) - how can I prevent this? MVH -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4 Kernel Panic-ing
I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is restarting itself every 2-3 days. It appears that it is due to a kernel panic, though I don't have direct access to the machine (colocated) to verify when it occurs. There are no crash.logs from MySQL, and watchdog and system.logs show nothing. I have culled the following panic.log - can anyone tell if MySQL is the cause of such? Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I have tried two totally different servers, and the problems continue. - John * Mon Jun 12 06:53:02 2006 Unresolved kernel trap(cpu 0): 0x300 - Data access DAR=0xFF864A5C PC=0x00056218 Latest crash info for cpu 0: Exception state (sv=0x3E6ABC80) PC=0x00056218; MSR=0x9030; DAR=0xFF864A5C; DSISR=0x4000; LR=0x00053AB4; R1=0x1C17BC70; XCP=0x000C (0x300 - Data access) Backtrace: 0x 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4 Proceeding back via exception chain: Exception state (sv=0x3E6ABC80) previously dumped as Latest state. skipping... Exception state (sv=0x0091F280) PC=0x; MSR=0xD030; DAR=0x; DSISR=0x; LR=0x; R1=0x; XCP=0x (Unknown) Kernel version: Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC panic(cpu 0): copyin/out has no recovery point Latest stack backtrace for cpu 0: Backtrace: 0x00083498 0x0008397C 0x0001EDA4 0x00090C38 0x0009402C Proceeding back via exception chain: Exception state (sv=0x3E6ABC80) PC=0x00056218; MSR=0x9030; DAR=0xFF864A5C; DSISR=0x4000; LR=0x00053AB4; R1=0x1C17BC70; XCP=0x000C (0x300 - Data access) Backtrace: 0x 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4 Exception state (sv=0x0091F280) PC=0x; MSR=0xD030; DAR=0x; DSISR=0x; LR=0x; R1=0x; XCP=0x (Unknown) Kernel version: Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] Professional Lasso / PHP / MySQL / FileMaker Pro Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4 Kernel Panic-ing
John May wrote: I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is restarting itself every 2-3 days. It appears that it is due to a kernel panic, though I don't have direct access to the machine (colocated) to verify when it occurs. There are no crash.logs from MySQL, and watchdog and system.logs show nothing. I have culled the following panic.log - can anyone tell if MySQL is the cause of such? Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I have tried two totally different servers, and the problems continue. - John * Mon Jun 12 06:53:02 2006 Unresolved kernel trap(cpu 0): 0x300 - Data access DAR=0xFF864A5C PC=0x00056218 Latest crash info for cpu 0: Exception state (sv=0x3E6ABC80) PC=0x00056218; MSR=0x9030; DAR=0xFF864A5C; DSISR=0x4000; LR=0x00053AB4; R1=0x1C17BC70; XCP=0x000C (0x300 - Data access) Backtrace: 0x 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4 Proceeding back via exception chain: Exception state (sv=0x3E6ABC80) previously dumped as Latest state. skipping... Exception state (sv=0x0091F280) PC=0x; MSR=0xD030; DAR=0x; DSISR=0x; LR=0x; R1=0x; XCP=0x (Unknown) Kernel version: Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC panic(cpu 0): copyin/out has no recovery point Latest stack backtrace for cpu 0: Backtrace: 0x00083498 0x0008397C 0x0001EDA4 0x00090C38 0x0009402C Proceeding back via exception chain: Exception state (sv=0x3E6ABC80) PC=0x00056218; MSR=0x9030; DAR=0xFF864A5C; DSISR=0x4000; LR=0x00053AB4; R1=0x1C17BC70; XCP=0x000C (0x300 - Data access) Backtrace: 0x 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4 Exception state (sv=0x0091F280) PC=0x; MSR=0xD030; DAR=0x; DSISR=0x; LR=0x; R1=0x; XCP=0x (Unknown) Kernel version: Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC How could a user space program like mysqld possibly cause a kernel panic? Sounds like bad kernel or bad hardware. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with Travel Industry site
Last year, after the effects of Hurrican Katrina, I decided to use my expereince in the travel industry to develop a cause related marketing site to help fund beach renourishment. My first attempt resulted in www.texasfunbeaches.com, which is a static website I taught myself to build. The backend travel bookings are handle by a third party. My interests is in taking the program to the next level (see http://beta.hotelsbycity.com/funbeaches/).I believe rather than developing a page for each destination, it would be more efficient to have destination info, photos, etc in a database that could then population with page after a click on the hyperlink. This is beyind my capabilities, and I'm looking for an individual or company who would be willing to assist in this effort in exchange for recognition as a sponsor of the program. My hosting provider is network solutions, which means I have MySQL and PHP available. My email adress is [EMAIL PROTECTED] Thanks Gil Langley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4 Kernel Panic-ing
Hi John - Yes, at my former workplace, we had a couple of different instances where we experienced severe crashing problems on G4-based machines. Prior to using a G4 Xserve, we were using a dual-G4 tower as our MySQL and Intranet server, and as we ramped up MySQL usage, the hard crashing got to where it was nearly every morning. Finally traced it to our backup process, Retrospect, accessing files while MySQL was trying to access them. Specifically excluding MySQL's data directory from your backup process is a must. Excluding temp and log dirs is also probably a good idea. We were already dumping out data out to another directory daily, but didn't realize that the simultaneous access would be such a problem. This was with MySQL 3.23 and later 4.0, and OS 10.1 through 10.2 I think. When we moved to a G4 Xserve (serving MySQL and our Intranet), we again had horrible crashing problems. Weird, like none I'd ever seen, where the OS basically just entered a slow spiral of death that led to it either rebooting on its own or us noticing the problem first and hard-resetting it. Apps would stop opening, cli processes would no longer run, running apps would slowly stop responding. Traced it to a pair of Kingston RAM modules. Once those were out, the machine righted itself and I believe continues to serve data (though no longer MySQL) to this day. Weird I know but I swear that was it. This was with MySQL 4.0 and (I think) OS 10.2 through 10.3. HTH, Dan John May wrote: I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is restarting itself every 2-3 days. It appears that it is due to a kernel panic, though I don't have direct access to the machine (colocated) to verify when it occurs. There are no crash.logs from MySQL, and watchdog and system.logs show nothing. I have culled the following panic.log - can anyone tell if MySQL is the cause of such? Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I have tried two totally different servers, and the problems continue. - John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-huge.cnf quite outdated (fwd)
On 6/12/06, Gaspar Bakos [EMAIL PROTECTED] wrote:
-- Forwarded message --
Date: Mon, 12 Jun 2006 11:39:11 -0400 (EDT)
From: Gaspar Bakos
To: Barry [EMAIL PROTECTED]
Subject: Re: my-huge.cnf quite outdated
Hello, Barry,
RE:
Guess we would answer to everyone on the list who wishes to optimize his
cnf.
That was unpolite, since the OP asked for simple guidelines and
pointed out that default example config files were outdated, not
begging for advice and/or help.
I don't guess, and don't even expect that you answer to everyone.
Oh, i have add super X RAMs with latencies of blah blah. Please i
think my cnf is outdated can somone help me? Or: Oh, i have added a
HD with 2times more rounds per/m can you update my cnf PLZ?
These are not what I asked, they are pretty negative exaggarations.
The OP got a point here, if you don't wanna help, don't bother answer...
And yes. You can tweak the shit out of the mysql.cnf files.
You have to test yourself on your system.
This is what I am doing, and in the meantime, looking for experience,
and also sharing mine.
I've had a big time looking for configs over the net and manuals,
ended search with this:
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
Quite handy if used with:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
And in your particular case, with:
http://dev.mysql.com/doc/refman/5.0/en/memory-use.html
and
http://dev.mysql.com/doc/refman/5.0/en/disk-issues.html
Got a very fast setup here, but my machine is not as powerful as
yours, so, won't post it...
And btw. the cnf files wrk with even bigger tables than you have.
Not optimal but okay.
How big?
Get a default set is something hard to do. You say machines with
more than 1GB of RAM are standard, but where I live, that's not
true. For big companies with HUGE databases and servers, MySQL
provides specific help with optimization (pay contracts), and the
default files served as a base for me for the last 5 years or so.
Every special server needs special handling. there is no the one and
only you have to do it this way way
OK, so why is there a my-{small,large,huge}.cnf ?
They are guidelines for typical systems and applications.
But they are quite outdated, as typical systems changed.
Simple guidelines for complete newbies to start with MySQL and learn
their way easily... They are not for typical systems, they are
specially SAFE and minimally optimized configs so your server won't
crash, but still use some of the resources of the machine. Keep in
mind that for me a HUGE server has 2GB of RAM and tables of 80GB, but
not for you, and I would not want a config file to simply crash
because its expecting 4GB of RAM... Neither a disk outbreak because
its caching my small tables (compared to yours) completely using swap
and spinning up my disks to a overheat...
All in all: I was looking for _typical_ configs for 4GB+ machines and
100Gb+ tables.
There are not such configs, sorry, but you gotta test your configs
because I don't think anyone would give you a config set that may
crash/overload/put in risk your server. Simply grab a set of features
and play in a test database. There are some stress tests for mysql
over the web...
--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How To Pronounce MySQL
Um, did anyone actually answer Jesse's question? The consensus seems to be that the correct way to pronounce it is My S-Q-L, Not My Sequel. So, that's the way I'm going to pronounce it. Actually, most of the people I say the name to have no idea what I'm talking about anyway. I only rarely have vocal communication with someone who would even know what a database is, let alone what MySQL is. So, I guess in the long run, it doesn't really matter, but just for those rare occassions when I'm talking with someone who knows what it is, I want to pronounce it right. Most of my technical conversations occur through e-mail or newsgroups like this one, in which I spell it MySQL, and the way it's pronounced doesn't matter. :-) Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Physical Restore
I've had a devil of a time trying to import some data into a table in my database, and one of the methods that I tried to use was the visual MySQL Migration Tool. I figured I could use it to import data into my existing database. I only had one table I wanted to import data into. Well, I got the data into the table, but along the way, it appears to have deleted my entire database and all it's tables. Screwed! I want to go back to my backup's, and restore the physical files, which I've tried to do, but evidentially, I didn't get them all. I didn't have a MySQL backup of the data and that's why I'm going this route. I'm using MySQL 5.0 on a Windows XP Pro machine (my development machine). Could someone please point me in the right direction for restoring all the physical files? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How To Pronounce MySQL
On Mon, 2006-06-12 at 13:32 -0400, Jesse wrote: Um, did anyone actually answer Jesse's question? The consensus seems to be that the correct way to pronounce it is My S-Q-L, Not My Sequel. So, that's the way I'm going to pronounce it. Actually, most of the people I say the name to have no idea what I'm talking about anyway. I only rarely have vocal communication with someone who would even know what a database is, let alone what MySQL is. So, I guess in the long run, it doesn't really matter, but just for those rare occassions when I'm talking with someone who knows what it is, I want to pronounce it right. Most of my technical conversations occur through e-mail or newsgroups like this one, in which I spell it MySQL, and the way it's pronounced doesn't matter. :-) According to the manual (http://dev.mysql.com/doc/refman/5.0/en/what-is.html): The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we don't mind if you pronounce it as “my sequel” or in some other localized way. -- omni Check out the Dallas Music Wiki http://digitaldarkness.com The information contained in this e-mail, if any, is often incorrect and probably plagiarized. It is intended solely for the amusement of the addressee. If you are not the intended recipient, my bad. Any action taken or omitted to be taken in reliance on the information in this message is your problem. Please notify me immediately if you have received it in error by reply e-mail and then delete this message from your system and any files in it's vicinity. I endeavour to ensure that my emails and any attachments are free from viruses, content, value or other contaminants. However, I cannot accept any responsibility might something worthwhile accidentally slip in. I therefore recommend you do not read them at all just to be sure. Please note that the statements and views expressed in this email and any attachments are completely chosen at random by the author and do not necessarily represent anything coherent, relevant or useful. signature.asc Description: This is a digitally signed message part
Re: my-huge.cnf quite outdated (fwd)
Hi, Daniel, RE: I've had a big time looking for configs over the net and manuals, ended search with this: http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html [...] Thanks for the links, I will check them again. I read most of those that are available on the web. their way easily... They are not for typical systems, they are specially SAFE and minimally optimized configs so your server won't crash, but still use some of the resources of the machine. Keep in I see. To re-iterate the question, I think the missing info is 1. the useful domain of system parameters. For example, read_buffer 64M does not help at all because of other limitations. (the default is 8K if I recall correctly). 2. the relation between the parameters, as they have a complicated relation. It is usually quite meaningless to increase/decrease a sinle parameter without changing others. So later on I will probably ask or share experience on the reasonable range on specific parameters. because I don't think anyone would give you a config set that may crash/overload/put in risk your server. Simply grab a set of features and play in a test database. There are some stress tests for mysql over the web... Good idea to google for stress tests. Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4 Kernel Panic-ing
Nope, no backups happening at the time. Anyone other ideas? - John At 11:42 AM -0500 6/12/06, Dan Buettner wrote: Hi John - Yes, at my former workplace, we had a couple of different instances where we experienced severe crashing problems on G4-based machines. Prior to using a G4 Xserve, we were using a dual-G4 tower as our MySQL and Intranet server, and as we ramped up MySQL usage, the hard crashing got to where it was nearly every morning. Finally traced it to our backup process, Retrospect, accessing files while MySQL was trying to access them. Specifically excluding MySQL's data directory from your backup process is a must. Excluding temp and log dirs is also probably a good idea. We were already dumping out data out to another directory daily, but didn't realize that the simultaneous access would be such a problem. This was with MySQL 3.23 and later 4.0, and OS 10.1 through 10.2 I think. When we moved to a G4 Xserve (serving MySQL and our Intranet), we again had horrible crashing problems. Weird, like none I'd ever seen, where the OS basically just entered a slow spiral of death that led to it either rebooting on its own or us noticing the problem first and hard-resetting it. Apps would stop opening, cli processes would no longer run, running apps would slowly stop responding. Traced it to a pair of Kingston RAM modules. Once those were out, the machine righted itself and I believe continues to serve data (though no longer MySQL) to this day. Weird I know but I swear that was it. This was with MySQL 4.0 and (I think) OS 10.2 through 10.3. HTH, Dan John May wrote: I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is restarting itself every 2-3 days. It appears that it is due to a kernel panic, though I don't have direct access to the machine (colocated) to verify when it occurs. There are no crash.logs from MySQL, and watchdog and system.logs show nothing. I have culled the following panic.log - can anyone tell if MySQL is the cause of such? Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I have tried two totally different servers, and the problems continue. - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] Professional Lasso / PHP / MySQL / FileMaker Pro Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unknown tables
When I do a SHOW TABLES in MySQL, it shows me a list of tables. But when I try to do a select * from countries, I get the error ERROR 1146 (42S02): Table 'karate.countries' doesn't exist. I cannot drop the table. I cannot drop the entire schema, and I cannot view the tables themselves. Does anyone know how to fix this problem? I'm using MySQL 5.0 on a Windows XP Pro (my development machine) environment. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limiting results from joins
Kim Christensen wrote: Hey list; Consider this statement: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id; Now, each unique product_id from products can have more than one entry in the items table, but I only want to fetch the one which fullfills a certain criteria. In this case, I want the statement only to JOIN the row if the column item_updated from the items table equals 1. Adding WHERE i.item_updated = 1 is the simplest solution. Is this the proper way to solve this: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id i.item_id = (SELECT item_id FROM items i2 WHERE i2.item_updated = 1); Definitely not, as you've seen. Your subquery returns a *lot* of unwanted item_ids. I find the above solution VERY slow, almost as if I have missed out on a very fundamental part of the logic - but it does get the work done. How could I speed this up, or solve it in another statement? [EMAIL PROTECTED] wrote: How about: select * from products p inner join manufactors m on p.manufactor_id=m.manufactor_id inner join items i on p.product_id=i.product_id and i.item_updated=1 I'd have suggested SELECT * FROM products p JOIN manufactors m ON p.manufactor_id = m.manufactor_id JOIN items i ON p.product_id = i.product_id WHERE i.item_updated=1 Although they appear different in theory, the optimizer will almost certainly treat them the same. (Check each with EXPLAIN to see.) In either case, the efficient way to execute the query will depend largely on what indexes are in the items table. With an index on items.item_updated, the optimizer may choose to use that index to select the appropriate rows from items, then join to the other two tables. On the other hand, if items has a multi-column index on (product_id, item_updated), the optimizer would be able to join table items last, using the index to match the one matching row for each row of table products. Without the multi-column index, it couldn't do that. Kim Christensen wrote: That worked great, really cut the time on my queries! However, how would one filter out the INNER JOIN statement if it returns more than one row to JOIN? I have reworked my previous table setup and query so that I need the row that has the lowest value of item_updated, not particularly 1. Well, that's different. This is an example of why you shouldn't try to simplify your query for the list. When your question doesn't include your real query, you are quite likely to get answers that don't apply to your real query. Here's what it looks like right now: SELECT * FROM products p INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id INNER JOIN items i ON i.product_id = p.product_id The problem is, that each entry in products may occur more than once in items, and they are identified by product_id in both tables. How do I filter out the results from the last INNER JOIN by certain criterias? I want the INNER JOIN to only return the row from items which has the lowest value in the column item_updated. This is a FAQ, with solutions given in the manual, http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html. (While the subquery solution looks more elegant, the temporary table solution is frequently more efficient.) As it is right now, MySQL returns a row for each time the product occurs in the items table, which is not what I want :-) Peter Lauri wrote: Just add: ORDER BY i.item_updated GROUP BY i.product_id (assuming that item_updated and product_id are in table i) That definitely will NOT work. You cannot select columns not included in the grouped columns and expect to get meaningful results. See the manual for why, http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html. Kim Christensen wrote: That gives me the result that I'm after, the only problem seems to be that the sorting is made before the matching - on all the rows (huge execution time) - how can I prevent this? I doubt that really gives the result you want, unless you were very lucky, or still haven't told us the real query. In any case, this query will not work reliably. Peter Lauri wrote: Take away the GROUP BY thing. And after that you just check if the rows are in the order that you want. The upper row would be the one that GROUP BY will take. Are you sure that you want the lowest value in the item_update? I would like to have the highest value. If you want the latest date, then you do ORDER BY i.item_updated DESC GROUP BY i.product_id Again, no. ORDER BY does not affect which rows are chosen by GROUP BY, which is what you are hoping for with this query. In fact, GROUP BY does not even select rows. It returns group identifiers and aggregate functions. If you try to select a column which is not included in the GROUP BY clause, you get a
