Re: Differences between numbers of rows in tables
Hi Dan, all! Dan Trainor wrote: Thanks for the prompt reply, Augusto - I completely understand what you're saying. To have anything such as a real-time measurement to the exact number of tables would be an incredible preformance degration, not to mention overhead and the like. Right. This is the conflict between fast operation (wanted by everybody) and maintaining statistics (shared data = bottleneck). I think I'm willing to accept the fact that while data is being sent to the database server, I won't get an exact reading of database/table/row size. This makes complete sense. However, what I am after, is how to get the exact size of a database/table/row when NO connections are being made. What is the exact size you refer too? Is it a) the resources consumed on the disk (file size etc), b) the data, index, and metadata stored (not including any gaps), c) the valid user data which would be returned by SELECT statements? Remember: In order to speed up further operations (inserts), a DBMS may not shrink disk structures even if data get deleted (freeing up space). So as long as the data are only growing, these three aspects may correlate closely, but when updates and deletes start, this may change. (Compare the use of heap space by malloc() and free() in C.) In the MySQL case, different table handlers will show different behaviour, further complicating the picture. Say, if I started MySQL with no networking. This way, I could positively ensure that no data modification would be taking place. Is this possible? For a), I recommend using the operating system means (Unix: du). For b) and c), I do not know the answer, but there may be ways. Depending on the database size, getting any exact answer to b) or c) may take longer than the typical user (a vague term, I know) is willing to wait. Regards, Jörg -- 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]
Type and Size of JOIN fields
Hello All, More than once I read on this list that problems may occur, because of unequal types and/or sizes of join fields. Suppose a Countries table with primary key ID SmallInt(5), and a Accounts table with CountryID SmallInt(4). Does this have any negative affect, eg. on performance ? By the way, I am using MyISAM tables for MySQL 5.0. Cor
Re: Rows counted but not returned after join
It seems like a job for a LEFT JOIN. To see the records which are present in table A and not present in table B use this query: SELECT A.* FROM A LEFT JOIN B USING(common_field) WHERE B.common_field is NULL. Thanks Jeb, I¹ve been working with this idea this evening and it¹s worked well for both the main search engine and the diagnostic effort to find the missing B.common_field entries. The search engine query is now SELECT datetime, title, body, author.person AS author_name FROM publication LEFT JOIN author USING (email) WHERE ( [WHERE DETAILS DELETED] ) ORDER BY datetime DESC and this returns all the hits, irrespective of whether there is a corresponding email address in author.email, my B.common_field. The textbook chapter you referenced now has me working on some ³IFNULL² options to try and fill-in the gap for future missing values. I've used your NULL suggestion above and identified the (six) problem rows ... now I'm off to fix them. Your short response has helped me solve a troubling problem, taught me a heap about joins, and now has me off chasing further knowledge about using MySQL flow control rather than relying on the PHP to tidyup unexpected values ... From my perspective a very, very valuable few words. Thanks very much for taking the time. Cheers Dougal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error 1146 X.1 does not exist
Can someone point me in the right direction. I had 4.1 running and all was well with my little program. I decided to test the code with the newer 5.0.18. I updated my DB and then tried to access it using the MYSQl Control Centre - 0.9.4-Beta and query browser 1.0.4 alpha. The Administrator allows me to view table structure but not to add any numerical columns. Using the Control centre, If I double click on the table I wish to view data I get two lines in the message area Empty set (0.00) sec [local] ERROR 1146: Table 'llcopy.1' doesn't exist llcopy being my test database name. These messages are got no matter what the table content is. I do note that the error message on this app is different numerically to the previous Also my insert program now fails to insert data. So I extracted one of the SQL insert commands that worked with 4.1 and manually run it using the mysql.exe command interface. This is the manual command. INSERT INTO BanksC (Bank,BankName,AccountNo,Balance,PMClosingBalance,EOMTempBalance,STTS) VALUES (C1,Client Bank,NONE,0.00,0.00,0.00,C); The error was ERROR 1054 (42S22): Unknown column 'C1' in 'field list' The settings of the table are char(2), char(20), char(12), decimal(12,2),decimal(12,2),decimal(12,2),char(1) respectively. This is driving me crazy. I am obviously doing something stupid but I have got so close to the problem I can't see the wood for the trees. Kerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1146 X.1 does not exist
I have seen the problem with the INSERT syntax. The new version doesn't like the use of 's to surround text and prefers single quotes. I still don't see where the dbname.1 error is from Kerry -Original Message- From: Kerry Frater [mailto:[EMAIL PROTECTED] Sent: 02 February 2006 10:05 To: mysql@lists.mysql.com Subject: error 1146 X.1 does not exist Can someone point me in the right direction. I had 4.1 running and all was well with my little program. I decided to test the code with the newer 5.0.18. I updated my DB and then tried to access it using the MYSQl Control Centre - 0.9.4-Beta and query browser 1.0.4 alpha. The Administrator allows me to view table structure but not to add any numerical columns. Using the Control centre, If I double click on the table I wish to view data I get two lines in the message area Empty set (0.00) sec [local] ERROR 1146: Table 'llcopy.1' doesn't exist llcopy being my test database name. These messages are got no matter what the table content is. I do note that the error message on this app is different numerically to the previous Also my insert program now fails to insert data. So I extracted one of the SQL insert commands that worked with 4.1 and manually run it using the mysql.exe command interface. This is the manual command. INSERT INTO BanksC (Bank,BankName,AccountNo,Balance,PMClosingBalance,EOMTempBalance,STTS) VALUES (C1,Client Bank,NONE,0.00,0.00,0.00,C); The error was ERROR 1054 (42S22): Unknown column 'C1' in 'field list' The settings of the table are char(2), char(20), char(12), decimal(12,2),decimal(12,2),decimal(12,2),char(1) respectively. This is driving me crazy. I am obviously doing something stupid but I have got so close to the problem I can't see the wood for the trees. Kerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
latin1 -utf8 conversion
Hi list, I guess this is a classic problem...! I found that on the web: http://www.oreillynet.com/pub/wlg/9022?wlg=yes, where basically the guy did dump data, change the charset in the table definition and reinsert the records into an utf8 database and ended up with some problems... I saw somewhere in the mysql doc (unable to find the link back though) that converting between charsets can be tricky, especially if you're not sure of what you actually have. Which is my problem: the tables are latin1, but some people may have executed queries from the command line (utf8) and inserted data (which are then utf8 right?), some may have used an utf8 phpmyadmin (producing utf8 data?) and some a old isolatin one.(which would then give latin1 data?) I think the majority of the data are latin1, but there may be some utf8 at some places. I have mostly basic characters, and a few names with accents. I saw somewhere that you can convert to binary before to be sure of keeping things right. From my understanding, the database itself never do any conversion, meaning if you insert utf8 data into tables declared as latin1 it doesn't really matter if you retrieve the data as utf8 on the client side(true?) I strongly suspect that I'm kind of intolerant to encodings and how to manage them, I just don't get it. Does anybody knows what is the best way to do? Would a dump be enough? Does the dump itself need to be utf8 encoded to be loaded properly? Do I need to load it through an utf8 interface? I have an old iso latin shell, an utf8 one, and 2 versions of phpmyadmin, one latin1 and one utf8: does it matter where I will load the dump from? Any help more than welcome! Thanks, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
items quantity
Hi ALL! Please help I have 2 tables... header -- id type_ DATA: --- id type_ 11 21 32 items -- header_id item_id quantity DATA: header_iditem_idquant 1110 1220 21100 22200 3120 3215 header is the moving type, and items is the items table. If header.type_ is 1 then incoming move, if 2 outgoing move. I would like to calculate incoming and outgoing quantity of items. (stock) I have a query: SELECT i1.item_ID, SUM(i1.quant) as Incoming, SUM(IF(i2.quant is NULL, 0,i2.quant)) as Outgoing FROM header h1 INNER JOIN items i1 ON i1.header_ID=h1.id LEFT JOIN header h2 ON h2.type_=2 LEFT JOIN items i2 ON i2.header_ID=h2.id AND i1.item_ID=i2.item_ID WHERE h1.type_=1 GROUP BY i1.item_ID Result: item_IDIncomingOutgoing 111040 222030 The incoming is OK, but the outgoing is wrong I can't find solution! I have mysql v4.0.18 I can't use subselect!! Thanx! Best Regards! --- USE test; # # Table structure for table 'header' # DROP TABLE IF EXISTS header; CREATE TABLE header ( id char(18) NOT NULL default '', type_ tinyint(3) unsigned default NULL, PRIMARY KEY (id), UNIQUE KEY id (id), KEY id_2 (id) ) TYPE=MyISAM; # # Dumping data for table 'header' # INSERT INTO header VALUES(1, 1); INSERT INTO header VALUES(2, 1); INSERT INTO header VALUES(3, 2); # # Table structure for table 'items' # DROP TABLE IF EXISTS items; CREATE TABLE items ( header_id char(18) NOT NULL default '', item_id char(18) NOT NULL default '', quant int(3) unsigned default NULL, PRIMARY KEY (header_id,item_id), KEY header_id (header_id,item_id) ) TYPE=MyISAM; # # Dumping data for table 'items' # INSERT INTO items VALUES(1, 1, 10); INSERT INTO items VALUES(1, 2, 20); INSERT INTO items VALUES(2, 1, 100); INSERT INTO items VALUES(2, 2, 200); INSERT INTO items VALUES(3, 1, 20); INSERT INTO items VALUES(3, 2, 15);
Re: Help please
Hello. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/crashing.html If you feel that there are too much sockets in a TIME_WAIT have a look here: http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html Logg, Connie A. wrote: Two days ago, a system that has been running fine started crashing...It could be for a variety of reasons which I am researchinig. However (running mysql 5.0.18) I notice the following from netstat: tcp0 0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38672 ESTABLISHED tcp0 0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38775 TIME_WAIT tcp0 0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38781 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38780 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38781 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38782 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38783 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38776 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38777 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38778 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38779 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38772 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38773 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38774 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38768 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38769 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38770 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38771 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38764 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38765 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38766 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38760 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38761 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38762 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38763 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38756 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38757 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38758 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38759 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38752 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38753 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38754 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38755 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38748 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38749 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38750 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38751 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38744 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38745 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38746 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38747 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38742 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38743 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38672 iepm-bw.slac.stanford.:1000 ESTABLISHED One of the messages in /var/log/messages is too many orphaned sockets. Do the above indicate orphaned sockets? I logged into mysql root and did a 'show full processlist' and there were only one or two mysql processes. Can someone explain why there might be so many tcp sockets taken up to connect to mysql (which is running on port 1000). Thanks, Connie Logg SLAC -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ /
Re: Problem storing lonf files
Hello. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html I suggest you to check the max_allowed_packet. See: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html Celestino Gomez Cid wrote: Dear All, I am trying to store (using the C API) a long field in a longblob table field. The size of the sentence is 2361408 Bytes and when using the function mysql_real_query it returns an error saying that the MySQL server has gone. However, if I reduce the data to be stored in a factor of 4. Then the data is stored without problems. Does anybody know what is happenning ? Thanks very much, Celestino. __ Este mensaje, y en su caso, cualquier fichero anexo al mismo, puede contener informacion clasificada por su emisor como confidencial en el marco de su Sistema de Gestion de Seguridad de la Informacion siendo para uso exclusivo del destinatario, quedando prohibida su divulgacion copia o distribucion a terceros sin la autorizacion expresa del remitente. Si Vd. ha recibido este mensaje erroneamente, se ruega lo notifique al remitente y proceda a su borrado. Gracias por su colaboracion. __ This message including any attachments may contain confidential information, according to our Information Security Management System, and intended solely for a specific individual to whom they are addressed. Any unauthorised copy, disclosure or distribution of this message is strictly forbidden. If you have received this transmission in error, please notify the sender immediately and delete it. __ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type and Size of JOIN fields
Hello. In my opinion it shouldn't because, according to the manual the value in brackets affects only the display characteristics of the fields. See: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html C.R.Vegelin wrote: Hello All, More than once I read on this list that problems may occur, because of unequal types and/or sizes of join fields. Suppose a Countries table with primary key ID SmallInt(5), and a Accounts table with CountryID SmallInt(4). Does this have any negative affect, eg. on performance ? By the way, I am using MyISAM tables for MySQL 5.0. Cor -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: items quantity
Gyurasits Zoltán wrote: Hi ALL! Please help DATA: header_iditem_idquant 1110 1220 21100 22200 3120 3215 header is the moving type, and items is the items table. If header.type_ is 1 then incoming move, if 2 outgoing move. I would like to calculate incoming and outgoing quantity of items. (stock) select i.item_id, sum(if(h.header_id=1,i.quant,0)) as Incoming, sum(if(h.header_id=2,i.quant,0)) as Outgoing sum(if(h.header_id is null or h.header_id not in (1,2),i.quant,0)) as OtherMovement FROM header h INNER JOIN items i on i.header_ID=h.id GROUP BY i.item_id Hope this helps Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sporadically empty result set
Hi, I am trying to get the following query in MySQL 4.1 to return all rows with the next larger value of the one generated by RAND()*MAX(field) and then to randomly choose a row from these. SELECT * FROM table WHERE field= (SELECT field FROM table WHERE field (select ROUND(RAND()*MAX(field)) from table) ORDER BY field LIMIT 1) ORDER BY RAND() LIMIT 1 So far it also seems to work, however every third or fourth run results in an empty result set and I do not really know why, especially because the first subquery always returns an existent value. SELECT field FROM table WHERE field (select ROUND(RAND()*MAX(field)) from table) ORDER BY field LIMIT 1 I would appreciate any comment or suggestion. Thanks, Alexander -- Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko! Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 1146 X.1 does not exist
Hello. MySQL CC is not supported now, and could have some problems with a fresh versions of MySQL. If error doesn't appear in latest MySQL Administrator then everything is ok. [local] ERROR 1146: Table 'llcopy.1' doesn't exist Have a look here: http://dev.mysql.com/doc/refman/5.0/en/cannot-find-table.html The error was ERROR 1054 (42S22): Unknown column 'C1' in 'field list' Check if the sql_mode has ANSI_QUOTES enabled. See: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html Kerry Frater wrote: Can someone point me in the right direction. I had 4.1 running and all was well with my little program. I decided to test the code with the newer 5.0.18. I updated my DB and then tried to access it using the MYSQl Control Centre - 0.9.4-Beta and query browser 1.0.4 alpha. The Administrator allows me to view table structure but not to add any numerical columns. Using the Control centre, If I double click on the table I wish to view data I get two lines in the message area Empty set (0.00) sec [local] ERROR 1146: Table 'llcopy.1' doesn't exist llcopy being my test database name. These messages are got no matter what the table content is. I do note that the error message on this app is different numerically to the previous Also my insert program now fails to insert data. So I extracted one of the SQL insert commands that worked with 4.1 and manually run it using the mysql.exe command interface. This is the manual command. INSERT INTO BanksC (Bank,BankName,AccountNo,Balance,PMClosingBalance,EOMTempBalance,STTS) VALUES (C1,Client Bank,NONE,0.00,0.00,0.00,C); The error was ERROR 1054 (42S22): Unknown column 'C1' in 'field list' The settings of the table are char(2), char(20), char(12), decimal(12,2),decimal(12,2),decimal(12,2),char(1) respectively. This is driving me crazy. I am obviously doing something stupid but I have got so close to the problem I can't see the wood for the trees. Kerry -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: latin1 -utf8 conversion
Hello. Start from reading this part of the manual: http://dev.mysql.com/doc/refman/5.0/en/charset.html From my understanding, the database itself never do any conversion, meaning if you insert utf8 data into tables declared as latin1 it doesn't really matter if you retrieve the data as utf8 on the client side(true?) Database does do conversion. If fields in a table have latin1 character set, all characters that are not present in latin1, will be converted most probably to '?'. utf8 can held all symbols which are in latin1, and in my opinion there shouldn't be any problems. mel list_php wrote: Hi list, I guess this is a classic problem...! I found that on the web: http://www.oreillynet.com/pub/wlg/9022?wlg=yes, where basically the guy did dump data, change the charset in the table definition and reinsert the records into an utf8 database and ended up with some problems... I saw somewhere in the mysql doc (unable to find the link back though) that converting between charsets can be tricky, especially if you're not sure of what you actually have. Which is my problem: the tables are latin1, but some people may have executed queries from the command line (utf8) and inserted data (which are then utf8 right?), some may have used an utf8 phpmyadmin (producing utf8 data?) and some a old isolatin one.(which would then give latin1 data?) I think the majority of the data are latin1, but there may be some utf8 at some places. I have mostly basic characters, and a few names with accents. I saw somewhere that you can convert to binary before to be sure of keeping things right. From my understanding, the database itself never do any conversion, meaning if you insert utf8 data into tables declared as latin1 it doesn't really matter if you retrieve the data as utf8 on the client side(true?) I strongly suspect that I'm kind of intolerant to encodings and how to manage them, I just don't get it. Does anybody knows what is the best way to do? Would a dump be enough? Does the dump itself need to be utf8 encoded to be loaded properly? Do I need to load it through an utf8 interface? I have an old iso latin shell, an utf8 one, and 2 versions of phpmyadmin, one latin1 and one utf8: does it matter where I will load the dump from? Any help more than welcome! Thanks, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: items quantity
Gyurasits,
I have a query:
SELECT
i1.item_ID,
SUM(i1.quant) as Incoming,
SUM(IF(i2.quant is NULL, 0,i2.quant)) as Outgoing
FROM header h1
INNER JOIN items i1 ON i1.header_ID=h1.id
LEFT JOIN header h2 ON h2.type_="2"
LEFT JOIN items i2 ON i2.header_ID=h2.id AND i1.item_ID=i2.item_ID
WHERE h1.type_="1"
GROUP BY i1.item_ID
I think you need just one join. Let aggregation do the work:
SELECT
i.item_ID,
SUM(IF( h.type_=1, IFNULL( i.quant, 0 ), 0 )) AS Incoming,
SUM(IF( h.type_=2, IFNULL( i.quant, 0 ), 0 )) AS Outgoing
FROM header AS h
INNER JOIN items AS i ON h.id=i.header_ID
GROUP BY i.item_ID;
PB
-
Gyurasits Zoltán wrote:
Hi ALL!
Please help
I have 2 tables...
header
--
id
type_
DATA:
---
id type_
11
21
32
items
--
header_id
item_id
quantity
DATA:
header_iditem_idquant
1110
1220
21100
22200
3120
3215
"header" is the moving type, and "items" is the items table.
If header.type_ is "1" then incoming move, if "2" outgoing move.
I would like to calculate incoming and outgoing quantity of items. (stock)
I have a query:
SELECT
i1.item_ID,
SUM(i1.quant) as Incoming,
SUM(IF(i2.quant is NULL, 0,i2.quant)) as Outgoing
FROM header h1
INNER JOIN items i1 ON i1.header_ID=h1.id
LEFT JOIN header h2 ON h2.type_="2"
LEFT JOIN items i2 ON i2.header_ID=h2.id AND i1.item_ID=i2.item_ID
WHERE h1.type_="1"
GROUP BY i1.item_ID
Result:
item_IDIncomingOutgoing
111040
222030
The incoming is OK, but the outgoing is wrong I can't find solution!
I have mysql v4.0.18 I can't use subselect!!
Thanx!
Best Regards!
---
USE test;
#
# Table structure for table 'header'
#
DROP TABLE IF EXISTS header;
CREATE TABLE header (
id char(18) NOT NULL default '',
type_ tinyint(3) unsigned default NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY id_2 (id)
) TYPE=MyISAM;
#
# Dumping data for table 'header'
#
INSERT INTO header VALUES("1", "1");
INSERT INTO header VALUES("2", "1");
INSERT INTO header VALUES("3", "2");
#
# Table structure for table 'items'
#
DROP TABLE IF EXISTS items;
CREATE TABLE items (
header_id char(18) NOT NULL default '',
item_id char(18) NOT NULL default '',
quant int(3) unsigned default NULL,
PRIMARY KEY (header_id,item_id),
KEY header_id (header_id,item_id)
) TYPE=MyISAM;
#
# Dumping data for table 'items'
#
INSERT INTO items VALUES("1", "1", "10");
INSERT INTO items VALUES("1", "2", "20");
INSERT INTO items VALUES("2", "1", "100");
INSERT INTO items VALUES("2", "2", "200");
INSERT INTO items VALUES("3", "1", "20");
INSERT INTO items VALUES("3", "2", "15");
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.0/248 - Release Date: 2/1/2006
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.0/248 - Release Date: 2/1/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database/table size
Hi there,
As far as I know, MySQL does not have a CREATE TABLE command do that.
The only thing I can think of is if by size you mean number of
rows. (Which is directly proportional to the size in bytes if you do
not use variable-length fields, anyway.)
If so, you can have an autoincrement field, and write a trigger to
delete the first record once you get to the newest record.
For instance, if you want your table to always have 100 rows, the
trigger would have the algorithm:
(use this before the insert statement runs!)
if ( max(autoincrement_field) - min(autoincrement_field) 99)
then
{ INSERT }
else {
delete from table where autoincrement_field=min(autoincrement_field)
INSERT
}
Please note that I've never really looked at triggers, so my pseudo
code may or may not be possible.
-Sheeri
On 1/31/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Hi, there!
I would like to know whether mysql has built-in capabilities/config options
in order to limid a database size or a table size.
I want a table to grow up to a limit and, when reached, for a new row to be
inserted the oldest one be deleted. Has mysql got this
functionality built-in? If not, what other approaches could I use, triggers
...?
Kinf regards.
Prueba el Nuevo Correo Terra; Seguro, Rápido, Fiable.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unclear about key_len in EXPLAIN output
according to: http://dev.mysql.com/doc/refman/5.0/en/explain.html The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL. Note that the value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses. there are special exceptions to that for index_merge and range join types. The manual agrees; you are correct; it's the length of the key. Where did you find that other statement? It might be the index_merge join type's key_len... Sincerely, Sheeri On 2/1/06, James Harvard [EMAIL PROTECTED] wrote: According to the manual the value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses. However, it doesn't specify quite how one can determine that. It _looks_ like the number of bytes in the key (or key part) that is used - is that correct? TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fastest way to log IP's
I'm logging IP addresses and also searching for existing ones. Does anyone happen to know which is more resource intensive: storing the IP's as integers using INET_NTOA() and INET_ATON() to convert them at each query; or simply storing the IP as a varchar? In each case the IP field would be the primary key. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Out of Range value adjusted?
I am trying to test some code on the new 5.0 version and am getting
problems.
I currently have two MYSQL's running on two differing machines. Both have
the same schema set up by the my own program. The difference is that Machine
1 was set up on version 4.1 and then upgraded to 5. Machine 2 was a fresh
installation of version 5.
My Data insertion code works perfectly well with the Version 5 instance that
was upgraded from version 4, but fails on the pure version 5. The
Administrator interface tells me that the tables are OK and I cannot see any
difference between the V4 upgraded to 5 table against the pure V5 install.
The code being used is
INSERT INTO Invs
(InvRef,InvDate,Outgoingref,Outgoingref2,BillDescription,BillAmount,VatCode,
VatRate)
VALUES
('1234#6','19991016','C','19990731','\Orig Bill
£728.50\',364.68,'A',17.50);
The table columns are set to
char(12),date,char(1),date,char(40),decimal(12,2),char(1),decimal(3,2). The
table is MyISAM.
There error produced on the pure version 5 installation is:
Out of Range value adjusted for column VATRate at row 1
and then quits. I have let the installation of MySQL use its defaults and is
of charset latin1 if it is an issue. The above statement does not error on
the upgraded version of MySQL but adds the row with no problem.
The set up program is using a function
with SQLBatch.SQL do
begin
Add('CREATE TABLE Invs (');
Add('InvRef char(12) default NULL,');
Add('InvDate date,');
Add('OutgoingRef char(1),');
Add('OutgoingRef2 date,');
Add('BillDescription char(40),');
Add('BillAmount decimal(12,2),');
Add('VATCode char(1),');
Add('VATRate decimal(3,2),');
Add(' index (OutgoingRef2)');
Add(') TYPE=MyISAM;');
end;
SQLBatch.ExecSql;
This seems to work fine. Any ideas on why it works with one and not the
other?
Kerry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Speed
Sorry, but you gave us a best guess situation. Your tables do not have
any PRIMARY KEYs defined on them so I had to guess at what made each row
in each table unique from all other rows in that table based only on your
sample query.
What value or combination of values will allow me to uniquely identify a
single record from each table? Armed with that information I can rework
my solution to accurately identify what you want to know. My suggestion is
that you add two integer-based auto_increment columns, one to each table,
and make them the PRIMARY KEYs and foreign keys as appropriate.
For example: What makes a single row of traffic_log different from each of
the others? Is it the `recipient_id` column or a combination of values?
Same for the `status_log` table. What makes each row different from all
others? How do I uniquely identify a single row in `traffic_log` that
corresponds to any random row from `status_log`?
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
سيد هادی راستگوی حقی [EMAIL PROTECTED] wrote on 02/02/2006
01:14:35 AM:
Thanks for your suggestion,
I forget to tell that each message in traffic_log may has at least 2
status
in status_log and I use to columns recipients_id and mobile_retry
to uniquely find each message's statuses.
May be I have to change my tables structure. I don't know.
It's really important for me to show each message with it's last status.
So
I have to use group by because in other way such as
SELECT * FROM status_log
ORDER BY time;
returns all statuses in order of time regards to multiple statuses for
any
message. so I think that the query may be like this
(SELECT status, recipient_id, time, mobile_retry FROM status_log GROUP
BY
recipient_id HAVING time=MAX(time)) AS sts*
JOIN traffic_log ON traffic_log.recipient_id=sts.recipient_id AND
traffic_log.mobile_retry=sts.mobile_retry
*sts -- to find last status of each message
On 2/1/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
سيد هادی راستگوی حقی [EMAIL PROTECTED] wrote on 02/01/2006
11:07:49
AM:
Dear All,
I need your suggestions please.
have to large tables with these schemas:
Table: traffic_log
Create Table: CREATE TABLE `traffic_log` (
`recipient_id` int(11) NOT NULL default '0',
`retry` smallint(4) NOT NULL default '0',
`mobile_retry` tinyint(1) NOT NULL default '0',
`orig` varchar(13) default NULL,
`dest` varchar(13) default NULL,
`message` text,
`account_id` int(11) NOT NULL default '0',
`service_id` int(11) NOT NULL default '0',
`dir` enum('IN','OUT') NOT NULL default 'IN',
`plugin` varchar(30) NOT NULL default 'UNKNOWN',
`date_entered` datetime NOT NULL default '-00-00 00:00:00',
`replied` tinyint(4) default '0',
KEY `account_id` (`account_id`,`recipient_id`,`mobile_retry`),
KEY `account_id_2` (`account_id`,`date_entered`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Table: status_log
Create Table: CREATE TABLE `status_log` (
`recipient_id` int(11) NOT NULL default '0',
`retry` smallint(4) NOT NULL default '0',
`mobile_retry` tinyint(1) NOT NULL default '0',
`status` smallint(5) NOT NULL default '0',
`time` datetime NOT NULL default '-00-00 00:00:00',
`smsc` varchar(20) NOT NULL default '',
`priority` tinyint(2) unsigned NOT NULL default '0',
`ack` varchar(30) NOT NULL default '',
KEY `recipient_id_2`
(`recipient_id`,`mobile_retry`,`time`,`status`),
KEY `time` (`time`,`recipient_id`,`mobile_retry`,`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
I want to execute a query to find out each last message's status. So
my
query is :
select * from traffic_log LEFT JOIN status_log ON
traffic_log.recipient_id=status_log.recipient_id and
traffic_log.mobile_retry=status_log.mobile_retry WHERE account_id =
32
group by status_log.recipient_id HAVING time=max(time) order by
time;
And MySQL explanation about this query is:
*** 1. row ***
id: 1
select_type: SIMPLE
table: traffic_log
type: ref
possible_keys: account_id,account_id_2
key: account_id
key_len: 4
ref: const
rows: 1049598
Extra: Using temporary; Using filesort
*** 2. row ***
id: 1
select_type: SIMPLE
table: status_log
type: ref
possible_keys: recipient_id_2
key: recipient_id_2
key_len: 5
ref: smse.traffic_log.recipient_id,
smse.traffic_log.mobile_retry
rows: 2
Extra:
as you see return records are 1049598.
But it's very slow.
Do you have any suggestions to fast it?
--
Sincerely,
Hadi Rastgou
a href=http://www.spreadfirefox.com/?q=affiliatesamp;
Re: Fastest way to log IP's
Brian Dunning [EMAIL PROTECTED] wrote on 02/02/2006 11:21:25 AM: I'm logging IP addresses and also searching for existing ones. Does anyone happen to know which is more resource intensive: storing the IP's as integers using INET_NTOA() and INET_ATON() to convert them at each query; or simply storing the IP as a varchar? In each case the IP field would be the primary key. You will get much faster searches if you convert to integers first. Your indexes will be smaller (more likely to sit and stay in memory), each term will be numerically compared( 5-50 times faster than string comparisons on average), and your data footprint will be smaller (more likely to stay in the file cache of the OS or the query cache of the server). The conversion routines INET_NTOA() and INET_ATON() are small and tight so you should not hit too much overhead by calling them whenever you need them. My advice? Stick with the numeric form of the address. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Sporadically empty result set
Hi Alexander, Very weird! I tried the query myself, setting it up as such: CREATE TABLE TEMP (id int not null AUTO_INCREMENT primary_key); and inserted 18 values into it. I am amazed that: SELECT * FROM temp WHERE id=( (SELECT id FROM temp WHERE id (SELECT ROUND(RAND()*MAX(id)) from temp) ORDER BY id LIMIT 1)); sometimes gives more than 1 result, other times gives an empty set. I would guess that it gives empty set when the random number picks the maximum number, but in practice it seems to happen more often than that. Plus, when I tried SELECT * FROM temp WHERE id = (SELECT id FROM temp WHERE id (SELECT ROUND(RAND()*(MAX(id)-1)) from temp) ORDER BY id LIMIT 1) ORDER BY RAND() LIMIT 1; Empty set (0.00 sec) so even getting the max minus 1 didn't help. I have no idea why that would happen, but I can confirm you're not crazy. -Sheeri On 2/2/06, Alexander Mueller [EMAIL PROTECTED] wrote: Hi, I am trying to get the following query in MySQL 4.1 to return all rows with the next larger value of the one generated by RAND()*MAX(field) and then to randomly choose a row from these. SELECT * FROM table WHERE field= (SELECT field FROM table WHERE field (select ROUND(RAND()*MAX(field)) from table) ORDER BY field LIMIT 1) ORDER BY RAND() LIMIT 1 So far it also seems to work, however every third or fourth run results in an empty result set and I do not really know why, especially because the first subquery always returns an existent value. SELECT field FROM table WHERE field (select ROUND(RAND()*MAX(field)) from table) ORDER BY field LIMIT 1 I would appreciate any comment or suggestion. Thanks, Alexander -- Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko! Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fastest way to log IP's
Storing the IP addresses as integers requires less memory but incurrs the cost of a call to these functions for every IP address. If you are going to use these addresses in string comparisons it's best to store them as varchars. - Asad On Thu, 2 Feb 2006, Brian Dunning wrote: I'm logging IP addresses and also searching for existing ones. Does anyone happen to know which is more resource intensive: storing the IP's as integers using INET_NTOA() and INET_ATON() to convert them at each query; or simply storing the IP as a varchar? In each case the IP field would be the primary key. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tmpdir option
Hello. It'll say that the disk is full, and will not make any attemt to ignore it and use the second partition. Eamon Daly wrote: This is wishful thinking, but I figured I'd ask anyway: the manual states: Starting from MySQL 4.1, the --tmpdir option can be set to a list of several paths that are used in round-robin fashion. Are these directories promotable at all? In other words, if I specify an 8G partition and a 20G partition, and a temporary table runs out of space in the former, will MySQL attempt to move it to the latter? We've been considering the purchase of a flash drive as a temporary disk for some of our larger reports, but on occassion we'll run a Very Large Report that would easily overrun the smaller disk. Eamon Daly -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of Range value adjusted?
Kerry Frater [EMAIL PROTECTED] wrote on 02/02/2006 11:21:44
AM:
I am trying to test some code on the new 5.0 version and am getting
problems.
I currently have two MYSQL's running on two differing machines. Both
have
the same schema set up by the my own program. The difference is that
Machine
1 was set up on version 4.1 and then upgraded to 5. Machine 2 was a
fresh
installation of version 5.
My Data insertion code works perfectly well with the Version 5 instance
that
was upgraded from version 4, but fails on the pure version 5. The
Administrator interface tells me that the tables are OK and I cannot see
any
difference between the V4 upgraded to 5 table against the pure V5
install.
The code being used is
INSERT INTO Invs
(InvRef,InvDate,Outgoingref,Outgoingref2,BillDescription,BillAmount,VatCode,
VatRate)
VALUES
('1234#6','19991016','C','19990731','\Orig Bill
£728.50\',364.68,'A',17.50);
The table columns are set to
char(12),date,char(1),date,char(40),decimal(12,2),char(1),decimal(3,2).
The
table is MyISAM.
There error produced on the pure version 5 installation is:
Out of Range value adjusted for column VATRate at row 1
and then quits. I have let the installation of MySQL use its defaults
and is
of charset latin1 if it is an issue. The above statement does not
error on
the upgraded version of MySQL but adds the row with no problem.
The set up program is using a function
with SQLBatch.SQL do
begin
Add('CREATE TABLE Invs (');
Add('InvRef char(12) default NULL,');
Add('InvDate date,');
Add('OutgoingRef char(1),');
Add('OutgoingRef2 date,');
Add('BillDescription char(40),');
Add('BillAmount decimal(12,2),');
Add('VATCode char(1),');
Add('VATRate decimal(3,2),');
Add(' index (OutgoingRef2)');
Add(') TYPE=MyISAM;');
end;
SQLBatch.ExecSql;
This seems to work fine. Any ideas on why it works with one and not the
other?
Kerry
In older versions, the range checking for DECIMAL() columns was lax (a
bug). The range checking has been fixed, which may be causing your
problem. For example: in the older versions a DECIMAL(12,3) column could
actually contain a positive number with 10 digits to the left of the
decimal place, instead of the 9 allowed by the definition, because the
extra digit was being allowed to reside in the space set aside for the
sign indicator. By fixing the implementation to actually honor the
definition of the column, a few programs that used that extra, illegal
space started having the exact problems you describe.
Now for the example you provided, you defined VATRate as DECIMAL(3,2)
which meant you wanted that field to have the space for 3 digits with 2 of
them to the right of the decimal place. Your potential range of values for
a field defined this way is from -9.99 to +9.99. You tried to insert the
value 17.50 . With the older, buggy, implementation that would have fit
into a (3,2) because the 1 of the 17 would have fit into the space the +
sign would have occupied if it were displayed. In the fixed
implementation, 17 is too large and will no longer fit.
My suggestion is to resize the field to (4,2) so that you have the range
-99.99 to +99.99 .
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Unclear about key_len in EXPLAIN output
'That other statement' that I quoted is actually just part of the paragraph from the manual that you quoted! My point was that it doesn't say what units the key length is given in or explain _how_ to determine the number of parts used. However the more I think about it the more I see that it must be bytes, and I have posted a comment to that effect on the manual page. James At 10:58 am -0500 2/2/06, sheeri kritzer wrote: according to: http://dev.mysql.com/doc/refman/5.0/en/explain.html The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL. Note that the value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses. there are special exceptions to that for index_merge and range join types. The manual agrees; you are correct; it's the length of the key. Where did you find that other statement? It might be the index_merge join type's key_len... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fastest way to log IP's
I'm logging IP addresses and also searching for existing ones. Does anyone happen to know which is more resource intensive: storing the IP's as integers using INET_NTOA() and INET_ATON() to convert them at each query; or simply storing the IP as a varchar? In each case the IP field would be the primary key. I'm actually doing this but with Apache access log files. I tested both methods and found that the INET_NTOA() and INET_ATON() were fairly quick and didn't have much of a performance hit. I also tested the queries for pulling the ips back out. Using the ints vs the ips. Both were indexed, which made the insert a little slower but drastically improved the search. The ints were a bit faster on the search than the varchar ips, but in my case there wasn't a huge difference and I would rather not have to do the extra processing and extra space for storing the ips as ints and index them as I only run reports off the access log once a week. One thing I might suggest is to find how many rows you really need to run your reports (or dates etc whatever it may be). The smaller your row size the faster your searches will be and the fewer indexes you have the faster your inserts will be. You could just archive what you don't need into another table/database and mess with the indexes and run your reports. Hope that helped. :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences between numbers of rows in tables
Joerg Bruehe wrote: Hi Dan, all! Dan Trainor wrote: Thanks for the prompt reply, Augusto - I completely understand what you're saying. To have anything such as a real-time measurement to the exact number of tables would be an incredible preformance degration, not to mention overhead and the like. Right. This is the conflict between fast operation (wanted by everybody) and maintaining statistics (shared data = bottleneck). I think I'm willing to accept the fact that while data is being sent to the database server, I won't get an exact reading of database/table/row size. This makes complete sense. However, what I am after, is how to get the exact size of a database/table/row when NO connections are being made. What is the exact size you refer too? Is it a) the resources consumed on the disk (file size etc), b) the data, index, and metadata stored (not including any gaps), c) the valid user data which would be returned by SELECT statements? Remember: In order to speed up further operations (inserts), a DBMS may not shrink disk structures even if data get deleted (freeing up space). So as long as the data are only growing, these three aspects may correlate closely, but when updates and deletes start, this may change. (Compare the use of heap space by malloc() and free() in C.) In the MySQL case, different table handlers will show different behaviour, further complicating the picture. Say, if I started MySQL with no networking. This way, I could positively ensure that no data modification would be taking place. Is this possible? For a), I recommend using the operating system means (Unix: du). For b) and c), I do not know the answer, but there may be ways. Depending on the database size, getting any exact answer to b) or c) may take longer than the typical user (a vague term, I know) is willing to wait. Regards, Jörg Good morning, Jörg - I think what I was going for, was resources consumed on disk. However, when talking about the ndbcluster table type, in a Cluster environment, resources consumed in memory. I believe that's more appropriate for the cluster@ list, which I'll check in a few here. I believe that 'du' would give some appropriate numbers, and a close estimate as to what I'll be needing to look for here. I appreciate your help. Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Find the Max/Min from Multiple Columns (in each row)
Hello everyone, I have a table where measurement values are collected in mulitple columns. Table Schema == ID, measurement_01, measurement_02, measurement_03 == 1, 300, 350, 325(max is 350, min is 300) 2, 225, 275, 400(max is 400, min is 225) 3, 100, 500, 300(max is 500, min is 100) == My question is that for each row, what's the sql query that determine the max value and the min value from all the columns?? (I realize that each measurment should be it's own row, but I must use the table as it is.) Any help would be most appreciated. Thanks! Henry
MySQL LEFT JOIN Optimization Using LIMIT CLAUSE
I have a table `requirement` which is left joining to a table `inventory` based on a matching `partNumber` column. The inventory table has millions of records, the requirement table has tens of thousands. I'm noticing that the left join between requirement and inventory doesn't take advantage of a LIMIT clause. So whether I select all records from requirement or limit it to 50, the LEFT JOIN operation still seems to be calculating for ALL requirement records against ALL inventory records. (The query takes the exact same amount of time, whether I pull 50 requirement records or 10,000). How can I force mysql to only join the inventory table for the those 50 records brought back by the LIMIT clause? What I would do in a more powerful DB like SQL Server, is build a temporary table with my 50 requirement rows, and then perform the inventory join on the temp table. But due to MySQL SPROC limitations (ie, LIMIT clauses must have integer constants, not parameters) and View limititations (ie, no indexing of views), I'd have to build this temporary table and the rest of query in PHP first, which is really ugly. I'm hoping there is a nice SQL trick I can use with MySQL to restrict the join to only those records that would come back from the limit set. Thanks, Scott Klarenbach
Re: NOT IN vs IS NULL
Shawn, I've just found out that most of my emails during this discussion were NOT posted to the list because I was sending mail in HTML format and Yahoo was not delivering the bounce notices to me. That explains why you believed I was not listening to Peter's input - only _his_ messages were appearing on the list! I'm adding my last two emails to the end of this one so that it doesn't appear as though I have ignored everyone, and in the hope that the conclusions I reached (with help from both of you) will be of use to someone else. My sincere apologies for the confusion that I inadvertently caused. Best regards, Devananda Actually, what was missing all along was pab.login_id=1 in the ON clause, not the WHERE clause!!! I guess I've not used that sort of condition in a JOIN before... If I rewrite your query like this, which is much simpler, it does in fact work very, very well! SELECT pt.offer_id FROM paytable AS pt INNER JOIN offers AS o USING (offer_id) LEFT JOIN publisher_advertiser_blocks AS pab ON o.advertiser_id=pab.advertiser_id AND pab.login_id=1 WHERE pt.login_id=1 AND pab.advertiser_id IS NULL; That is just the answer I was hoping for from the start! Thanks :) On a side note, I looked at the MySQL reference manual for JOINs ( http://dev.mysql.com/doc/refman/5.0/en/join.html ) again, and while the syntax of putting a row-limiting condition on the right table in a LEFT JOIN isn't discussed in the actual documentation (that's where I do most of my reading...) it IS mentioned in a post at the bottom of the page! Damn, I wish I had seen that last week ... Thanks for the help Peter [EMAIL PROTECTED] wrote: You are correct, that the situation you describe was not clearly presented in your previous reply. I humbly apologize for any offense. Apology humbly accepted. I only took offense because I have been paying close attention to all the suggestions that Peter and you have been offering, trying to learn from them (as I do with most of your posts to the mailing list). Using the EXPLAIN you posted in your latest reply, you can translate your subquery into the following JOINed query SELECT p.offer_id FROM paytable p LEFT JOIN offers o ON o.advertiser_id = p.advertiser_id WHERE pt.login_id=1 AND o.offer_id is null; Which is not what I think you were actually trying to write. The terms selecting values from publisher_advertiser_blocks disappeared because they are on the *right* side of a LEFT JOIN and played no part in actually limiting your final results. Here is the needs statement from your first post: The goal of these queries is to select all offer_id's from `paytable` for a known login_id where that login_id is not blocked from that offer_id. I would write it this way CREATE TEMPORARY TABLE tmpBlocks SELECT DISTINCT o.offer_id FROM offers INNER JOIN publisher_advertiser_blocks pab ON pab.advertiser_id = o.advertiser_id AND pab.login_id = 1; ALTER TABLE tmpBlocks ADD KEY(offer_id); SELECT p.offer_id FROM paytable p LEFT JOIN tmpBlocks tb ON tb.offer_id = p.offer_id WHERE tb.offer_id IS NULL; DROP TEMPORARY TABLE tmpBlocks; With a slight change, what you suggested does work quite well. The final SELECT needs AND p.login_id=1 in the WHERE clause, or else it will return data for all login_id that have records in paytable. One trick to working efficiently with larger datasets (millions of rows per table) is to minimize the number of rows being joined at one time. By breaking this query into two statements we keep our JOIN combinations to a minimum so that at each successive stage we are working with smaller sets of data than if we had written it as a single statement. Indeed! I often use temporary tables, since some of the datasets I work with are in the tens or hundreds of millions of rows. The term pab.login_id=1 is in the ON clause because your index on publisher_advertiser_block is defined in such a way that makes it better to be in the ON clause than in the WHERE clause (also information from your original post). There was a posting not long ago that said that the statistics of a temporary table's indexes were not updated if they exist before you fill the table with data. That's very good to know! I will keep it in mind, and probably could get more performance out of queries I've written in the past by doing this. Again, I apologize for any offense I may have caused, No harm done, we all live and learn :) Best regards, and thanks again, Devananda -- http://devananda-vdv.blogspot.com/ http://mycat.sourceforge.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Find the Max/Min from Multiple Columns (in each row)
Henry, My question is that for each row, what's the sql query that determine the max value and the min value from all the columns?? SELECT ... GREATEST(col1,col2,...), /// PB - Henry Chang wrote: Hello everyone, I have a table where measurement values are collected in mulitple columns. Table Schema == ID, measurement_01, measurement_02, measurement_03 == 1, 300, 350, 325(max is 350, min is 300) 2, 225, 275, 400(max is 400, min is 225) 3, 100, 500, 300(max is 500, min is 100) == My question is that for each row, what's the sql query that determine the max value and the min value from all the columns?? (I realize that each measurment should be it's own row, but I must use the table as it is.) Any help would be most appreciated. Thanks! Henry No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.0/248 - Release Date: 2/1/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.0/248 - Release Date: 2/1/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SSL connection problems with Fedora Core 4
I'm trying to set up SSL connectivity to a Fedora Core 4 server running
mysql-server-4.1.16-1.FC4.1 and not having much success. I keep getting
ERROR 2026 (HY000): SSL connection error
no matter what I do.
I followed the directions on
http://dev.mysql.com/doc/refman/4.1/en/secure-create-certs.html to the
letter, and I've cranked up all the logging possible on the server. I
don't see any error messages on the server at all, and SHOW STATUS
indicates that mysql is accepting SSL connections ('ssl_accepts'
increments).
In desperation I tried to strace(1) mysqld, but all I see is it handing
the connection to a cloned child thread... and I don't know how to trace
into the child (with '-f' it hangs mysqld presumably b/c the parent is
wait(2)-ing on the child):
select(5, [3 4], NULL, NULL, NULL) = 1 (in [3])
fcntl64(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(3, {sa_family=AF_INET, sin_port=htons(38973),
sin_addr=inet_addr(192.168.0.182)}, [16]) = 47
fcntl64(3, F_SETFL, O_RDWR) = 0
getsockname(47, {sa_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr(192.168.0.145)}, [16]) = 0
fcntl64(47, F_SETFL, O_RDONLY) = 0
fcntl64(47, F_GETFL)= 0x2 (flags O_RDWR)
fcntl64(47, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(47, SOL_IP, IP_TOS, [8], 4) = 0
setsockopt(47, SOL_TCP, TCP_NODELAY, [1], 4) = 0
time(NULL) = 1138854239
clone(child_stack=0xb3c244c4, flags=CLONE_VM|CLONE_FS|CLONE_FILES|
CLONE_SIGHAND|CLONE_THREAD|CLONE_SYSVSEM|CLONE_SETTLS|
CLONE_PARENT_SETTID|CLONE_CHILD_CLEARTID|CLONE_DETACHED,
parent_tidptr=0xb3c24bf8, {entry_number:6, base_addr:0xb3c24bb0,
limit:1048575, seg_32bit:1, contents:0, read_exec_only:0,
limit_in_pages:1, seg_not_present:0, useable:1},
child_tidptr=0xb3c24bf8) = 6918
select(5, [3 4], NULL, NULL, NULL
Here's my relevant my.cnf snippets:
8 cut here 8
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
ssl
ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem
ssl-cipher=ALL
log
log-error
8 cut here 8
Can anyone help?
- Julian
--
Julian C. Dunn
Systems Administrator
e: [EMAIL PROTECTED]
p: 416-363-6316 x292
f: 416-363-6102
Devlin eBusiness Architects
185 Frederick St.
Toronto, ON
M5A 4L4
http://www.devlin.ca/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Find the Max/Min from Multiple Columns (in each row)
select max(greatest(measurement_01, measurement_02, measurement_03)) from table select min(least(measurement_01, measurement_02, measurement_03)) from table Henry Chang wrote: Hello everyone, I have a table where measurement values are collected in mulitple columns. Table Schema == ID, measurement_01, measurement_02, measurement_03 == 1, 300, 350, 325(max is 350, min is 300) 2, 225, 275, 400(max is 400, min is 225) 3, 100, 500, 300(max is 500, min is 100) == My question is that for each row, what's the sql query that determine the max value and the min value from all the columns?? (I realize that each measurment should be it's own row, but I must use the table as it is.) Any help would be most appreciated. Thanks! Henry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connection Problem
When testing my connection via Dreamweave MX, I get the following error message: 2002 Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2) It was suggested to me that the server may not be running, though when I go to my System Preferences Panel (MacOSX), I have a green light and it says that it is currently running. --Casey Rhodes
Re: MySQL Connection Problem
do # my_print_defaults mysqld --port=3306 --socket=/var/lib/mysql/mysql.sock --skip-locking --key_buffer=16M --max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --myisam_sort_buffer_size=8M --server-id=1 To give you the defaults for your mysqld server. It may be listening on the wrong socket for connections. If your mysqld is listening on the same port as DW MX is trying to connect to, then maybe mysql is not running after all. Keith In theory, theory and practice are the same; In practice they are not. On Thu, 2 Feb 2006, Rhodes, Casey wrote: To: mysql@lists.mysql.com From: Rhodes, Casey [EMAIL PROTECTED] Subject: MySQL Connection Problem When testing my connection via Dreamweave MX, I get the following error message: 2002 Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2) It was suggested to me that the server may not be running, though when I go to my System Preferences Panel (MacOSX), I have a green light and it says that it is currently running. --Casey Rhodes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL LEFT JOIN Optimization Using LIMIT CLAUSE
Try this: [EMAIL PROTECTED]:ule select * from a; ++--+ | id | data | ++--+ | 1 | a| | 2 | b| | 3 | c| | 4 | d| | 5 | e| ++--+ 5 rows in set (0.00 sec) [EMAIL PROTECTED]:ule select * from b; ++--+ | id | data | ++--+ | 1 | aa | | 3 | bb | | 4 | cc | | 3 | bb | ++--+ 4 rows in set (0.00 sec) [EMAIL PROTECTED]:ule select A, a.data, b.id as B, b.data FROM (select a.id as A, a.data from a limit 3) a LEFT JOIN b on A=b.id; +---+--+--+--+ | A | data | B| data | +---+--+--+--+ | 1 | a|1 | aa | | 2 | b| NULL | NULL | | 3 | c|3 | bb | | 3 | c|3 | bb | +---+--+--+--+ 4 rows in set (0.00 sec) -- Augusto Bott augusto.bott (at) gmail.com On 2/2/06, Scott Klarenbach [EMAIL PROTECTED] wrote: I have a table `requirement` which is left joining to a table `inventory` based on a matching `partNumber` column. The inventory table has millions of records, the requirement table has tens of thousands. I'm noticing that the left join between requirement and inventory doesn't take advantage of a LIMIT clause. So whether I select all records from requirement or limit it to 50, the LEFT JOIN operation still seems to be calculating for ALL requirement records against ALL inventory records. (The query takes the exact same amount of time, whether I pull 50 requirement records or 10,000). How can I force mysql to only join the inventory table for the those 50 records brought back by the LIMIT clause? What I would do in a more powerful DB like SQL Server, is build a temporary table with my 50 requirement rows, and then perform the inventory join on the temp table. But due to MySQL SPROC limitations (ie, LIMIT clauses must have integer constants, not parameters) and View limititations (ie, no indexing of views), I'd have to build this temporary table and the rest of query in PHP first, which is really ugly. I'm hoping there is a nice SQL trick I can use with MySQL to restrict the join to only those records that would come back from the limit set. Thanks, Scott Klarenbach
update a Blob field using UPDATE
I am importing data from a non MySQL table into MySQL. In the table there is a text field of up to length 4000 chars. I have defined the column as blob in the MySQL table. I can read the text field of the source table into a variable e.g. mystring$. The MySQL table has been set, except for this data. I thought to use SQLString = UPDATE TheTable SET Notes = + mystring$ + WHERE TheTableRef = ' + Myref$ + '; I get error: You have an error in the SQL syntax I have tried to search the manual for an example of updating a blob column from a variable and cannot find one. I don't want to save the content of the var to disk and then load from file because of the time it takes. Do I have another option? Kerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
回复： Re: MySQL 4.1.13 and utf-8 lang uage encoding
--- Chenzhou Cui [EMAIL PROTECTED]写道: 由于MySQL 列表中绝大多数都是英文用户，我建议你在给大家发送或者回复消息时 不要带上许多汉字，这样会让那些非中文的朋友很尴尬。 Chinese chracters are automatically added by Yahoo, not me. Regards, Lionel ___ 雅虎1G免费邮箱百分百防垃圾信 http://cn.mail.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
