question on auto increment field
Hi, If it possible to limit the max value of an auto increment field to say, , and how to do it? I am using MySQL 4.0.18. Regards, -- Wong
Re: question on auto increment field
on 06/23/2004 12:14 AM, Joe Wong at [EMAIL PROTECTED] wrote: If it possible to limit the max value of an auto increment field to say, , and how to do it? I am using MySQL 4.0.18. I am not sure, as a 'hack' you could simply insert a blank record with the value set to ,, once you reach that limit and try to insert a record, a error would be generated, no new records would be able to be inserted until this was resolved. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.1?
Hi, Is there any idea of a release date for a MySQL 5.0.1 build that includes stored procedures on a per database basis? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
Shawn, I uncovered a problem this morning. I wonder if you (or anyone else) can help me out again? mysql select * from url_visit where url_scheme_ID=3 limit 10; +-+---+---+-+---+--+ | urlid | url_scheme_ID | url_server_ID | url_path_ID | query | category | +-+---+---+-+---+--+ | 23392 | 3 | 1070 | 1 | NULL | none | | 1346269 | 3 | 1070 | 1 | NULL | none | +-+---+---+-+---+--+ 2 rows in set (0.00 sec) This is the insert statement I'm using: INSERT IGNORE url_visit (url_scheme_ID, url_server_ID, url_path_ID, query, category) SELECT DISTINCT uc.ID, us.ID, up.ID,bt.query,bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN url_paths up ON up.path=bt.path INNER JOIN url_schemes uc ON uc.scheme=bt.scheme Do I need brackets after the distinct? e.g SELECT DISTINCT (uc.ID, us.ID, up.ID,bt.query,bt.category) Thanks, js. Great catch! I believe you found the problem. Sorry about that! ;-D So... just curious... from the original 60GB of text data, how much space did the final data require (including indices)? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/22/2004 07:55 Subject: Re: load data into 2 tables and set id AM I think I fixed it! INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring, category) SELECT DISTINCT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path After this I ran the sql to insert into internet_usage and this time it finished after 10m32. I'll do some more testing and let you know how it goes. Thanks, js. Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT url_visit (url_server_ID, url_path_ID, querystring, category) SELECT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path js. mysql select * from url_visit where urlid=1631; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1631 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.01 sec) mysql select * from url_visit where urlid=1753; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1753 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.00 sec) Shawn, Thanks for your reply below. I found it extremely useful. I have followed your instructions and got good results up to the url_visits table. I have a perl script to parse the values out of the log. The log has 3,770,246 lines and is gzipped. I then applied your SQL statements with the following times: 1. Parse script: 10m31 2. Load infile: 2m39 3. Insert url_servers and url_paths: 5m38 4. Insert url_visits 9m50 However when I ran the sql to insert the internet usage table, it just ran until all the log space was filled up: -rw-rw 1 mysqlmysql379694680 Jun 22 10:05 bulk_table.MYD
Re: question on auto increment field
Scott Haneda [EMAIL PROTECTED] wrote: on 06/23/2004 12:14 AM, Joe Wong at [EMAIL PROTECTED] wrote: If it possible to limit the max value of an auto increment field to say, , and how to do it? I am using MySQL 4.0.18. No. The maximum value for the auto_increment column can be limited by the maximum value of the column type(tinyint, int, mediumint etc.). I am not sure, as a 'hack' you could simply insert a blank record with the value set to ,, once you reach that limit and try to insert a record, a error would be generated, no new records would be able to be inserted until this was resolved. It will not work, because if you insert dummy row and set auto_increment value to , the next generated auto_increment value will be 1. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: mysql action history
web tur [EMAIL PROTECTED] wrote: I have mysql database on the web server. Is there any way to see mysql actions from any log? I want to see what my visitors did on the sql databases. You can turn on general query log: http://dev.mysql.com/doc/mysql/en/Query_log.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
Clustering platform
Hi, I am going to setup mysql clustering, anybody knows... Which distribution of Linux is best for clustering test? I mean easy to setup and stable to use. Usually I prefer RedHat product, is Fedora a good platform? Thanks, Joseph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question on auto increment field
Hi Egor, Thanks for your reply. In addition to this, how I can make MySQL to reuse the number which has been deleted? I tried to do a test as follow 1. Create a dummy table with a auto increment field 'UID' set to MED INT 2. Manually insert a record that set UID to Max of MED INT, ie 16777215 3. Insert another record without specifying the value of UID At 3, it failed and said: Duplicate entry '16777215' for key 1 But I have only 1 record in the table. Regards, - Wong - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 23, 2004 4:10 PM Subject: Re: question on auto increment field Scott Haneda [EMAIL PROTECTED] wrote: on 06/23/2004 12:14 AM, Joe Wong at [EMAIL PROTECTED] wrote: If it possible to limit the max value of an auto increment field to say, , and how to do it? I am using MySQL 4.0.18. No. The maximum value for the auto_increment column can be limited by the maximum value of the column type(tinyint, int, mediumint etc.). I am not sure, as a 'hack' you could simply insert a blank record with the value set to ,, once you reach that limit and try to insert a record, a error would be generated, no new records would be able to be inserted until this was resolved. It will not work, because if you insert dummy row and set auto_increment value to , the next generated auto_increment value will be 1. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sorting strings as integers
Hi, I have a database column (VARCHAR) consisting of the following kind of data: 1 1.1 1.2.1.2 1.10.1 1.2 1.4.1 I need to sort this colum so that the result will be 1 1.1 1.2 1.2.1.2 1.4.1 1.10.1 I was hoping that just using ORDER BY [column] ASC would work, but alas, it only works for number less than 10 (current collation considers 1.10 to be before 1.2 but after 1.1, logically enough). So I was just wondering if anyone had some nice solutions or SQL statements containing black magic which would do what I want. Maybe a special collation? -- Ole Kasper Olsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[q] 4.1.2 Collation how to...
SQL Hi, All! This is configure scrypt haw I have compiled mysql 4.1.2 ./configure \ --prefix=/usr/local/mysql_4 \ --without-debug \ --with-charset=koi8r \ --with-collation=koi8r_general_ci \ --with-extra-charset=koi8u,cp1251 \ --with-mysqld-user=mysql \ --without-berkeley-db \ --without-isam \ --without-innodb \ --enable-thread-safe-client After that any programs using libmysqlclient has the problem with collation if they try compare char fields. I try ask query show variables and see: Variable_name=character_set_client Value=latin1 Variable_name=character_set_connection Value=latin1 Variable_name=character_set_databaseValue=koi8r Variable_name=character_set_results Value=latin1 Variable_name=character_set_server Value=koi8r Variable_name=character_set_system Value=utf8 Variable_name=character_sets_dir Value=/usr/local/mysql_4/share/mysql/charsets/ Variable_name=collation_connection Value=latin1_swedish_ci Variable_name=collation_databaseValue=koi8r_general_ci Variable_name=collation_server Value=koi8r_general_ci What options for configure should I set that my programs/clients have character_set_client and etc is set to koi8r ? Is this possible? May be it is bug? Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on hex expression of strings
I got the following result when I tried to display strings with hex expression. x'B4C1BBFA' OK X'B4C1BBFA' OK 0xB4C1BBFA OK 0XB4C1BBFA Error I was checking how MySQL treats upper- and lower-case 'x'. '0X' didn't work and I am wondering if this is decided by the specification of MySQL. Will you let me know if this is specified so or this is unexpected behavior? Hirofumi Fujiwara, Knowledge Engineering Center 26-27 Saka-machi Shinjuku-ku, Tokyo 160-0002 Japan Time Intermedia Corporation, http://www.timedia.co.jp/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [q] 4.1.2 Collation how to...
Andrey, thanks for your report! You're right, There was a bug that the client library didn't take in account --with-charset and --with-collation configure attributes. This bug was fixed in 4.1.3 which is going to be released soon. A temporary solution with 4.1.2 is to use SET NAMES koi8r after connection. Or run mysqld with --init-command=SET NAMES koi8r. Regards. Andrey Kotrekhov wrote: SQL Hi, All! This is configure scrypt haw I have compiled mysql 4.1.2 ./configure \ --prefix=/usr/local/mysql_4 \ --without-debug \ --with-charset=koi8r \ --with-collation=koi8r_general_ci \ --with-extra-charset=koi8u,cp1251 \ --with-mysqld-user=mysql \ --without-berkeley-db \ --without-isam \ --without-innodb \ --enable-thread-safe-client After that any programs using libmysqlclient has the problem with collation if they try compare char fields. I try ask query show variables and see: Variable_name=character_set_client Value=latin1 Variable_name=character_set_connection Value=latin1 Variable_name=character_set_databaseValue=koi8r Variable_name=character_set_results Value=latin1 Variable_name=character_set_server Value=koi8r Variable_name=character_set_system Value=utf8 Variable_name=character_sets_dir Value=/usr/local/mysql_4/share/mysql/charsets/ Variable_name=collation_connection Value=latin1_swedish_ci Variable_name=collation_databaseValue=koi8r_general_ci Variable_name=collation_server Value=koi8r_general_ci What options for configure should I set that my programs/clients have character_set_client and etc is set to koi8r ? Is this possible? May be it is bug? Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-4 4 -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Barkov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia ___/ www.mysql.com +7-912-856-80-21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
one on one joins
Hi list, I have a problem concerning two tables. Basically, I need a strict one on one join. Simplyfied, the problem is as follows: I need a check on two tables: T1: containing four records, with the value of field id being 1, 2, 3, 4 T2, same structure, containing the records 1, 2, 3, 5 for field id. I want to delete anything in table T1 which is not in T2. That is simple: delete t1 from t1 a left join t2 b using(id) where b.id is not null; However, Now my problem: the problem is, that the id in both tables can contain duplicates. When the tables have the following recs: T1: field id: 1,1,2,3,4 T2: field id: 1,2,3,5 I want the delete command to delete ONE 1, the 2 and the 3, thus resulting in a table T1 having left only two recrods, with id 1 and 4. However, the delete matches both 1-records of T1 to the single 1- record of T2, so both of them are deleted. So, summarizing: I need a sort of one on one join, which joins only one single 1 in table T1 to a single 1 in table T2, and when there's no 1 record left in T2, the other 1 in T1 should be unmatched. How can I do this? Or if this is not possible, does somebody know some smart workaround trick? Thanks, rinke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A Complicated Group Query
Thank you! Just what I needed. 8-D I hope these template queries can help you to see the patterns that evolve while using the GROUP BY with JOINed tables. You can exclude any unwanted results from the GROUP BY phase of the query by applying a set of HAVING restrictions. The HAVING clause works *exactly* like a WHERE clause except it is applied to the *results* of the GROUP BY and not to the temporary results being aggregated. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine How many bookings happened at a particular location that are part of project 'x': SELECT u.User_Location, count(b.bookingID) FROM Projects p INNER JOIN Users u ON u.Client_ID = p.ClientID AND p.Project_Name = 'x' INNER JOIN Bookings b ON b.User_ID = u.User_ID GROUP BY u.User_Location How many locations have been booked more than 3 times for the same project : SELECT p.Project_Name, u.User_Location, count(b.Booking_ID) FROM Projects p INNER JOIN Users u ON u.Client_ID = p.ClientID INNER JOIN Bookings b ON b.User_ID = u.User_ID GROUP BY p.Project_Name, u.User_Location HAVING count(b.Booking_ID) 3 How many locations have been booked more than 3 times regardless of project: SELECT u.User_Location, count(b.Booking_ID) FROM Users u INNER JOIN Bookings b ON b.User_ID = u.User_ID GROUP BY u.User_Location HAVING count(b.Booking_ID) 3 List all locations for all clients participating in project X and how often they have been booked: SELECT c.Client_Name, u.User_Location, count(b.bookingID) FROM Projects p INNER JOIN Users u ON u.Client_ID = p.ClientID AND p.Project_Name = 'X' INNER JOIN Clients c on c.Client_ID = p.Client_ID AND p.Project_Name='X' LEFT JOIN Bookings b ON b.User_ID = u.User_ID GROUP BY c.Client_Name, u.User_Location - or - SELECT c.Client_Name, u.User_Location, count(b.bookingID) FROM Projects p INNER JOIN Users u ON u.Client_ID = p.ClientID INNER JOIN Clients c on c.Client_ID = p.Client_ID LEFT JOIN Bookings b ON b.User_ID = u.User_ID WHERE p.Project_Name = 'X' GROUP BY c.Client_Name, u.User_Location (Both will work but one should be faster. I would try them both to see which one works faster for you.) shaun thornburgh [EMAIL PROTECTED]To: [EMAIL PROTECTED], [EMAIL PROTECTED] otmail.com cc: Fax to: 06/22/2004 04:18 Subject: Re: A Complicated Group Query PM Hi Shawn, A slight correction(!) Client_ID is contained in the Projects table as a Client can have many projects. Therefore c.Project_ID will cause an error... Here is a definition of the tables: mysql DESCRIBE Users; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | User_ID | int(11) | | PRI | NULL| auto_increment | | Client_ID| int(3) | YES | | NULL| | | User_Username| varchar(40) | | | | | | User_Password| varchar(20) | YES | | NULL| | | User_Name| varchar(100) | | | | | | User_Type| varchar(20) | | | Nurse | | | User_Email | varchar(100) | YES | | NULL| | | User_Location| varchar(40) | YES | | NULL| | +--+--+--+-+-++ 15 rows in set (0.00 sec) mysql DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | || | Client_ID| int(11) | | | 0 || +--+--+--+-+-++ 4 rows
Re: How do you deal with URL's?
Robert, The original posting's project is cataloging the states from countries all over the world not just the US. In this case, he needs a numeric ID as I don't think the USPS keeps a list of state abbreviations for other countries. Otherwise, I would agree with you. ;-) Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Robert A. Rosenberg To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: Fax to: Subject: Re: How do you deal with URL's? 06/22/2004 05:09 PM At 22:38 -0300 on 06/21/2004, Sergio Salvi wrote about Re: How do you deal with URL's?: Separate data from how it's displayed. I mean, create a table called states with the fields state_id, state_name and state_url. Put the data in the according field: state_id state_name state_url 1 Alabama http://www.alabama.gov 2 Washington http://access.wa.gov ...and so on Instead of a auto_increment state_id, go with the USPS 2 letter code (AL, WA, etc). That way you can pull up the state name from an address. -- 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: load data into 2 tables and set id
J S, Check to see if the url_scheme_ID part of a unique constraint/key or the Primary Key? If it isn't, you will get dupes even if you use INSERT IGNORE unless you pre-screen your INSERTs some other way to avoid duplication. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/23/2004 04:13 Subject: Re: load data into 2 tables and set id AM Shawn, I uncovered a problem this morning. I wonder if you (or anyone else) can help me out again? mysql select * from url_visit where url_scheme_ID=3 limit 10; +-+---+---+-+---+--+ | urlid | url_scheme_ID | url_server_ID | url_path_ID | query | category | +-+---+---+-+---+--+ | 23392 | 3 | 1070 | 1 | NULL | none | | 1346269 | 3 | 1070 | 1 | NULL | none | +-+---+---+-+---+--+ 2 rows in set (0.00 sec) This is the insert statement I'm using: INSERT IGNORE url_visit (url_scheme_ID, url_server_ID, url_path_ID, query, category) SELECT DISTINCT uc.ID, us.ID, up.ID,bt.query,bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN url_paths up ON up.path=bt.path INNER JOIN url_schemes uc ON uc.scheme=bt.scheme Do I need brackets after the distinct? e.g SELECT DISTINCT (uc.ID, us.ID, up.ID,bt.query,bt.category) Thanks, js. Great catch! I believe you found the problem. Sorry about that! ;-D So... just curious... from the original 60GB of text data, how much space did the final data require (including indices)? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/22/2004 07:55 Subject: Re: load data into 2 tables and set id AM I think I fixed it! INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring, category) SELECT DISTINCT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path After this I ran the sql to insert into internet_usage and this time it finished after 10m32. I'll do some more testing and let you know how it goes. Thanks, js. Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT url_visit (url_server_ID, url_path_ID, querystring, category) SELECT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path js. mysql select * from url_visit where urlid=1631; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1631 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.01 sec)
Re: one on one joins
You will have to use some criteria other than table position to delete just the first match in table 1. The concept of first and last only apply to ordered sets of data and there is _no_ guarantee that records entered sequentially will be _stored_ sequentially in the actual data structure. Are there other columns that make the second #1 in your example worthy of keeping? What makes that record special enough to survive when the other matching row won't? Sorry I couldn't help, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] rinkeh To: [EMAIL PROTECTED] cc: 06/23/2004 08:24 Fax to: AM Subject: one on one joins Please respond to rinkeh Hi list, I have a problem concerning two tables. Basically, I need a strict one on one join. Simplyfied, the problem is as follows: I need a check on two tables: T1: containing four records, with the value of field id being 1, 2, 3, 4 T2, same structure, containing the records 1, 2, 3, 5 for field id. I want to delete anything in table T1 which is not in T2. That is simple: delete t1 from t1 a left join t2 b using(id) where b.id is not null; However, Now my problem: the problem is, that the id in both tables can contain duplicates. When the tables have the following recs: T1: field id: 1,1,2,3,4 T2: field id: 1,2,3,5 I want the delete command to delete ONE 1, the 2 and the 3, thus resulting in a table T1 having left only two recrods, with id 1 and 4. However, the delete matches both 1-records of T1 to the single 1- record of T2, so both of them are deleted. So, summarizing: I need a sort of one on one join, which joins only one single 1 in table T1 to a single 1 in table T2, and when there's no 1 record left in T2, the other 1 in T1 should be unmatched. How can I do this? Or if this is not possible, does somebody know some smart workaround trick? Thanks, rinke -- 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: load data into 2 tables and set id
Hi Shawn, Here's the url_Schemes table (it's the same as the url_paths and url_servers). This means url_scheme_ID is part of a unique constraint/key ? mysql desc url_schemes; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | ID | int(11) | | MUL | NULL| auto_increment | | scheme | varchar(20) | | PRI | || ++-+--+-+-++ 2 rows in set (0.00 sec) mysql desc url_visit; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | urlid | mediumint(9) | | PRI | NULL| auto_increment | | url_scheme_ID | int(11) | | MUL | 0 || | url_server_ID | int(11) | | | 0 || | url_path_ID | int(11) | | | 0 || | query | text | YES | | NULL|| | category | varchar(50) | YES | | NULL|| +---+--+--+-+-++ 6 rows in set (0.00 sec) mysql select * from url_schemes; +++ | ID | scheme | +++ | 1 | http | | 2 | tcp| | 3 | - | | 4 | ftp| | 5 | https | +++ 5 rows in set (0.00 sec) J S, Check to see if the url_scheme_ID part of a unique constraint/key or the Primary Key? If it isn't, you will get dupes even if you use INSERT IGNORE unless you pre-screen your INSERTs some other way to avoid duplication. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/23/2004 04:13 Subject: Re: load data into 2 tables and set id AM Shawn, I uncovered a problem this morning. I wonder if you (or anyone else) can help me out again? mysql select * from url_visit where url_scheme_ID=3 limit 10; +-+---+---+-+---+--+ | urlid | url_scheme_ID | url_server_ID | url_path_ID | query | category | +-+---+---+-+---+--+ | 23392 | 3 | 1070 | 1 | NULL | none | | 1346269 | 3 | 1070 | 1 | NULL | none | +-+---+---+-+---+--+ 2 rows in set (0.00 sec) This is the insert statement I'm using: INSERT IGNORE url_visit (url_scheme_ID, url_server_ID, url_path_ID, query, category) SELECT DISTINCT uc.ID, us.ID, up.ID,bt.query,bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN url_paths up ON up.path=bt.path INNER JOIN url_schemes uc ON uc.scheme=bt.scheme Do I need brackets after the distinct? e.g SELECT DISTINCT (uc.ID, us.ID, up.ID,bt.query,bt.category) Thanks, js. Great catch! I believe you found the problem. Sorry about that! ;-D So... just curious... from the original 60GB of text data, how much space did the final data require (including indices)? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/22/2004 07:55 Subject: Re: load data into 2 tables and set id AM I think I fixed it! INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring, category) SELECT DISTINCT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path After this I ran the sql to insert into internet_usage and this time it finished after 10m32. I'll do some more testing and let you know how it goes. Thanks, js. Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT url_visit (url_server_ID, url_path_ID, querystring, category) SELECT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path js. mysql select * from
Re: load data into 2 tables and set id
No, url_scheme_ID has key type MUL, which means that that multiple occurences of a given value are allowed within the field. To prevent duplicate entries in url_visit, decide which combination of columns should have no duplicates, then add a unique index on that combination. Michael J S wrote: Hi Shawn, Here's the url_Schemes table (it's the same as the url_paths and url_servers). This means url_scheme_ID is part of a unique constraint/key ? mysql desc url_schemes; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | ID | int(11) | | MUL | NULL| auto_increment | | scheme | varchar(20) | | PRI | || ++-+--+-+-++ 2 rows in set (0.00 sec) mysql desc url_visit; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | urlid | mediumint(9) | | PRI | NULL| auto_increment | | url_scheme_ID | int(11) | | MUL | 0 || | url_server_ID | int(11) | | | 0 || | url_path_ID | int(11) | | | 0 || | query | text | YES | | NULL|| | category | varchar(50) | YES | | NULL|| +---+--+--+-+-++ 6 rows in set (0.00 sec) mysql select * from url_schemes; +++ | ID | scheme | +++ | 1 | http | | 2 | tcp| | 3 | - | | 4 | ftp| | 5 | https | +++ 5 rows in set (0.00 sec) J S, Check to see if the url_scheme_ID part of a unique constraint/key or the Primary Key? If it isn't, you will get dupes even if you use INSERT IGNORE unless you pre-screen your INSERTs some other way to avoid duplication. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/23/2004 04:13 Subject: Re: load data into 2 tables and set id AM Shawn, I uncovered a problem this morning. I wonder if you (or anyone else) can help me out again? mysql select * from url_visit where url_scheme_ID=3 limit 10; +-+---+---+-+---+--+ | urlid | url_scheme_ID | url_server_ID | url_path_ID | query | category | +-+---+---+-+---+--+ | 23392 | 3 | 1070 | 1 | NULL | none | | 1346269 | 3 | 1070 | 1 | NULL | none | +-+---+---+-+---+--+ 2 rows in set (0.00 sec) This is the insert statement I'm using: INSERT IGNORE url_visit (url_scheme_ID, url_server_ID, url_path_ID, query, category) SELECT DISTINCT uc.ID, us.ID, up.ID,bt.query,bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN url_paths up ON up.path=bt.path INNER JOIN url_schemes uc ON uc.scheme=bt.scheme Do I need brackets after the distinct? e.g SELECT DISTINCT (uc.ID, us.ID, up.ID,bt.query,bt.category) Thanks, js. Great catch! I believe you found the problem. Sorry about that! ;-D So... just curious... from the original 60GB of text data, how much space did the final data require (including indices)? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/22/2004 07:55 Subject: Re: load data into 2 tables and set id AM I think I fixed it! INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring, category) SELECT DISTINCT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path After this I ran the sql to insert into internet_usage and this time it finished after 10m32. I'll do some more testing and let you know how it goes. Thanks, js. Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT
Re: load data into 2 tables and set id
I can't tell from a DESCRIBE output. Please post the results of SHOW CREATE TABLE url_visit; Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/23/2004 09:57 Subject: Re: load data into 2 tables and set id AM Hi Shawn, Here's the url_Schemes table (it's the same as the url_paths and url_servers). This means url_scheme_ID is part of a unique constraint/key ? mysql desc url_schemes; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | ID | int(11) | | MUL | NULL| auto_increment | | scheme | varchar(20) | | PRI | || ++-+--+-+-++ 2 rows in set (0.00 sec) mysql desc url_visit; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | urlid | mediumint(9) | | PRI | NULL| auto_increment | | url_scheme_ID | int(11) | | MUL | 0 || | url_server_ID | int(11) | | | 0 || | url_path_ID | int(11) | | | 0 || | query | text | YES | | NULL|| | category | varchar(50) | YES | | NULL|| +---+--+--+-+-++ 6 rows in set (0.00 sec) mysql select * from url_schemes; +++ | ID | scheme | +++ | 1 | http | | 2 | tcp| | 3 | - | | 4 | ftp| | 5 | https | +++ 5 rows in set (0.00 sec) -- previous responses clipped for space - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL backup not backing up all tables
I am having trouble with a nightly backup of our MySQL database. Using the mysqldump command, we dump our entire database to a backup directory. But consistently the backup file contains only 33 of the 88 tables in the database. The 33 getting backed up are the first 33 of the 88 as sorted alphabetically by table name. Here are the relevant lines in the backup script: mysqldump --opt ACA /home/sites/home/users/admin/dump/ACA/`date --date '1 day ago' +%m%d`.all.ACA mysqldump mysql /home/sites/home/users/admin/dump/ACA/`date --date '1 day ago' +%m%d`.mysql I'm a newbie with MySQL, so I don't know what other info to provide. I appreciate your help. Danny S. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
Sorry! I'm not that experienced with databases as you probably realised! url_visit | CREATE TABLE `url_visit` ( `urlid` mediumint(9) NOT NULL auto_increment, `url_scheme_ID` int(11) NOT NULL default '0', `url_server_ID` int(11) NOT NULL default '0', `url_path_ID` int(11) NOT NULL default '0', `query` text, `category` varchar(50) default NULL, PRIMARY KEY (`urlid`), KEY `url_scheme_ID` (`url_scheme_ID`,`url_server_ID`,`url_path_ID`) ) TYPE=MyISAM I can't tell from a DESCRIBE output. Please post the results of SHOW CREATE TABLE url_visit; Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/23/2004 09:57 Subject: Re: load data into 2 tables and set id AM Hi Shawn, Here's the url_Schemes table (it's the same as the url_paths and url_servers). This means url_scheme_ID is part of a unique constraint/key ? mysql desc url_schemes; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | ID | int(11) | | MUL | NULL| auto_increment | | scheme | varchar(20) | | PRI | || ++-+--+-+-++ 2 rows in set (0.00 sec) mysql desc url_visit; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | urlid | mediumint(9) | | PRI | NULL| auto_increment | | url_scheme_ID | int(11) | | MUL | 0 || | url_server_ID | int(11) | | | 0 || | url_path_ID | int(11) | | | 0 || | query | text | YES | | NULL|| | category | varchar(50) | YES | | NULL|| +---+--+--+-+-++ 6 rows in set (0.00 sec) mysql select * from url_schemes; +++ | ID | scheme | +++ | 1 | http | | 2 | tcp| | 3 | - | | 4 | ftp| | 5 | https | +++ 5 rows in set (0.00 sec) -- previous responses clipped for space - _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
Do this to prevent duplication on those three columns in the future:
ALTER TABLE url_visit DROP KEY `url_scheme_ID`, add UNIQUE KEY
(url_scheme_ID, url_server_ID, url_path_id);
The way I have composed that key (table-column order), it will force you to
include the url_scheme_ID if you want to use the index to find
url_server_id and url_path_id. Maybe a better Idea is to organize that new
key so that the columns are listed in their order of prevalence in your
queries, if you search by url_sever_ID most often, list it first. If
url_scheme_ID is not something you need as often put it last. That
changes the statement to look like:
ALTER TABLE url_visit DROP KEY `url_scheme_ID`, add UNIQUE KEY
(url_server_ID, url_path_ID, url_scheme_ID);
This way you can search on {url_server_ID}, {url_server_ID, url_path_ID},
or (url_server_ID, url_path_ID, url_scheme_ID} and MySQL will still use the
index. Because it's designated as UNIQUE key, there will always be at most
1 record with any combination of those three values.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
J S
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
com cc: [EMAIL PROTECTED]
Fax to:
06/23/2004 10:38 Subject: Re: load data into 2 tables
and set id
AM
Sorry! I'm not that experienced with databases as you probably realised!
url_visit | CREATE TABLE `url_visit` (
`urlid` mediumint(9) NOT NULL auto_increment,
`url_scheme_ID` int(11) NOT NULL default '0',
`url_server_ID` int(11) NOT NULL default '0',
`url_path_ID` int(11) NOT NULL default '0',
`query` text,
`category` varchar(50) default NULL,
PRIMARY KEY (`urlid`),
KEY `url_scheme_ID` (`url_scheme_ID`,`url_server_ID`,`url_path_ID`)
) TYPE=MyISAM
I can't tell from a DESCRIBE output. Please post the results of SHOW
CREATE TABLE url_visit;
Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
J S
[EMAIL PROTECTED]To:
[EMAIL PROTECTED]
com cc:
[EMAIL PROTECTED]
Fax to:
06/23/2004 09:57 Subject: Re: load data
into 2 tables and set id
AM
Hi Shawn,
Here's the url_Schemes table (it's the same as the url_paths and
url_servers). This means url_scheme_ID is part of a unique constraint/key
?
mysql desc url_schemes;
++-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-++
| ID | int(11) | | MUL | NULL| auto_increment |
| scheme | varchar(20) | | PRI | ||
++-+--+-+-++
2 rows in set (0.00 sec)
mysql desc url_visit;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| urlid | mediumint(9) | | PRI | NULL| auto_increment |
| url_scheme_ID | int(11) | | MUL | 0 ||
| url_server_ID | int(11) | | | 0 ||
| url_path_ID | int(11) | | | 0 ||
| query | text | YES | | NULL||
| category | varchar(50) | YES | | NULL||
+---+--+--+-+-++
6 rows in set (0.00 sec)
mysql select * from url_schemes;
+++
| ID | scheme |
+++
| 1 | http |
| 2 | tcp|
| 3 | - |
| 4 | ftp|
| 5 | https |
+++
5 rows in set (0.00 sec)
-- previous responses clipped for space -
_
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
--
MySQL
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
So, if I understand you correctly, somewhere in the middle of a 20,000 row insert, a row gets inserted with auto_increment id = 87,123,456, say, then the next row tries to insert with the value 87,123,457 but fails. You fix this by skipping the next value with ALTER TABLE yourtable AUTO_INCREMENT=87123458 After that, the auto_increment id column resumes working as expected, until the next time. Is that right? I can't imagine why that would happen. I have some suggestions: (You may have done some of these already.) First, run a CHECK TABLE on your table. Assuming that indicated no problems, the next time this happens, start by running CHECK TABLE again. Then, do a SHOW TABLE STATUS LIKE 'yourtable' to verify that the next auto_increment value (87,123,457 in my example) is the one that produced the error, then run a SELECT * FROM yourtable WHERE id=87123457 (use the value that produced the error) to verify that there is no row with that id. Then try manually inserting a row to verify you get the same error. Assuming you do, try manually inserting a row with the id explicitly set to 87123457 to see if the problem is with the value as opposed to the auto_increment. Have you tried resetting the auto_increment id by manually inserting a larger number? INSERT INTO yourtable (id) values (87123458); Under normal circumstances, this would cause the auto_increment counter to change to the next value. It would also be a lot quicker than an ALTER TABLE statement. One more thing. When you do a multiple row INSERT statement, LAST_INSERT_ID() returns the auto_increment id of the first row inserted. (See http://dev.mysql.com/doc/mysql/en/Information_functions.html) So, if you've just done a multiple row insert, LAST_INSERT_ID()+1 should already exist as an ID in the table. Michael Kevin Brock wrote: On Jun 19, 2004, at 10:37 AM, Michael Stassen wrote: Something about your description doesn't quite fit, however. You say that you are nowhere near the limit, but you say that resetting the auto_increment starting point fixes the problem. Those seem contradictory to me. To me as well, that's why I posted. What kind of table is it (MyISAM, InnoDB,...)? MyISAM. Have you tried SHOW TABLE STATUS LIKE 'yourtable' Nope. I'm sure we'll be able to reproduce the problem shortly though, and I'll try it then. when this happens? What is the next auto_increment value (in the Auto_increment column) according to the output? Well, since I didn't try it I don't know for sure :-) When I check LAST_INSERT_ID, it's correct. I.e., the ID that fails would be the next ID after LAST_INSERT_ID. Have you looked at the data in the table? Are the values in the auto_increment column consecutive, as expected? What's the max value in that column? The max value is one less than the value that failed, the number of rows is correct, and the values in the column are consecutive. Are you inserting 87 million rows in an empty or existing table? If the latter, how many rows are already there? In either case, is this a large bulk insert that fails before it's done, or are you saying that in normal operations it fails every 87 million or so inserts? The table has been accumulating data for about a month, starting with an empty table. Each insert is on the order of 10-20,000, inserting using INSERT INTO doing multiple rows at a time. How are you resetting the AUTO_INCREMENT starting point, exactly? How do you choose the starting value? What is the value you choose? Do you verify it worked with SHOW TABLE STATUS? Note that if you try to set the next AUTO_INCREMENT to a value less than the largest value currently in the column, the ALTER silently fails. I did an ALTER TABLE to set AUTO_INCREMENT to one greater than the value that failed. We were able to insert more data after that, and the data inserted had the expected values for the ID column... Normally, auto_increment columns do not reuse values, so if you insert then delete a row, that number is still gone. Are you doing just inserts, or are there deletions mixed in? If, for example, you insert 80 million rows a day and delete rows that were inserted yesterday, you'll run out of INT keys in under a month, even though there are only 80 million rows in the table. We hadn't done any deletes on the table. The first ID value is one. Finally, just to cover all the bases, that really is 87 million inserts, not 8.7 million, right? I only ask because a MEDIUMINT column runs out a little past 8.3 million. Right. 87 million rows in the table. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Some BLOB help please.
You might save some space if you compress() before storing. Depending on
file content I'm seeing 0-50% savings?
select length(load_file('c:/temp/SomeFile.pdf')) as old_size
,length(compress(load_file('c:/temp/SomeFile.pdf'))) as new_size
Ed
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 22, 2004 11:01 PM
To: Justin Crone
Cc: [EMAIL PROTECTED]
Subject: Re: Some BLOB help please.
Justin Crone wrote:
snip
So as I said, I am rather pleased with the performance and the ease at
which I was able to get this up and running. However The problem
is
I do have limits, and one of those being disk space. Those 10,000
files
are taking up 21 GB of space in the database. However the actual space
required by the files is around 5GB on the file system. The average
file
size is about 1.9MB, so it would seem that each row inserted into the
database is conforming to that 1.9MB average, giving me this 21GB
table.
Could you explain that again? If average file size is 1.9Mb, then 21 Gb
for
10,780 files is about right. On the other hand, if the total is 5 Gb,
then
5Gb/10,780 yields about .47 Mb average per file. So which is it? Do
your
files average 1.9Mb, in which case we must wonder how you stored them in
only 5Gb, or do your files average .47 Mb, in which case we must figure
out
why they are roughly 4 times as big when stored as BLOBs? (The manual
http://dev.mysql.com/doc/mysql/en/Storage_requirements.html says BLOBs
take length + 2bytes to store .)
I would like to know if there is something that I can change to get
these
numbers in line with each other, or if this is just the way of things.
Current projections for the total documents needed to complete the
rotation
of these files is 720,000 documents. Which if the 1.9MB average keeps,
that
puts me in the neighborhood of 1.4TB of storage.
Even at .47Mb per file, that's about 330 Gb of storage required just for
the
720,000 files.
Michael
--
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: MySQL backup not backing up all tables
I assume you are running this with cron. Do you get an error message from cron? Do you have enough room on the destination disk for all 88 tables? How big is the backup file? For completeness, what is your OS, and what is your mysql version? Michael Danny Smitherman wrote: I am having trouble with a nightly backup of our MySQL database. Using the mysqldump command, we dump our entire database to a backup directory. But consistently the backup file contains only 33 of the 88 tables in the database. The 33 getting backed up are the first 33 of the 88 as sorted alphabetically by table name. Here are the relevant lines in the backup script: mysqldump --opt ACA /home/sites/home/users/admin/dump/ACA/`date --date '1 day ago' +%m%d`.all.ACA mysqldump mysql /home/sites/home/users/admin/dump/ACA/`date --date '1 day ago' +%m%d`.mysql I'm a newbie with MySQL, so I don't know what other info to provide. I appreciate your help. Danny S. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql-administrator-1.0.4 beta on SuSE 9.1, can not compile
Hello, Who can help? About 2 weeks ago I downloaded the source of mysql-administrator-1.0.4_beta and since then I am fighting with it. I can not compile it on SuSE linux 9.1 (32 bit). The reported problem from ./configure is that it can not find the package gdk-2.0.pc. This complaint is not shown in config.log, it appeared on the console. Also the output is complaining about libxml2 being 2.6.2. This is not true, libxml2 is version 2.6.7, checked with rpm -qi libxml2. Here is config.log: === start == This file contains any messages produced by compilers while running configure, to aid debugging if configure makes a mistake. It was created by configure, which was generated by GNU Autoconf 2.59. Invocation command line was $ ./configure --enable-maintainer-mode ## - ## ## Platform. ## ## - ## hostname = lxp2 uname -m = i686 uname -r = 2.6.4-52-default uname -s = Linux uname -v = #1 Wed Apr 7 02:08:30 UTC 2004 /usr/bin/uname -p = unknown /bin/uname -X = unknown /bin/arch = i686 /usr/bin/arch -k = unknown /usr/convex/getsysinfo = unknown hostinfo = unknown /bin/machine = unknown /usr/bin/oslevel = unknown /bin/universe = unknown PATH: /home/lxuser/bin PATH: /usr/local/bin PATH: /usr/bin PATH: /usr/X11R6/bin PATH: /bin PATH: /usr/games PATH: /opt/gnome/bin PATH: /opt/kde3/bin PATH: /usr/lib/java/jre/bin ## --- ## ## Core tests. ## ## --- ## configure:1356: checking for a BSD-compatible install configure:1411: result: /usr/bin/install -c configure:1422: checking whether build environment is sane configure:1465: result: yes configure:1522: checking for gawk configure:1538: found /usr/bin/gawk configure:1548: result: gawk configure:1558: checking whether make sets $(MAKE) configure:1582: result: no configure:1749: checking build system type configure:1767: result: i686-pc-linux-gnu configure:1775: checking host system type configure:1789: result: i686-pc-linux-gnu configure:1843: checking for gcc configure:1859: found /usr/bin/gcc configure:1869: result: gcc configure:2113: checking for C compiler version configure:2116: gcc --version /dev/null 5 gcc (GCC) 3.3.3 (SuSE Linux) Copyright (C) 2003 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. configure:2119: $? = 0 configure:2121: gcc -v /dev/null 5 Reading specs from /usr/lib/gcc-lib/i586-suse-linux/3.3.3/specs Configured with: ../configure --enable-threads=posix --prefix=/usr --with-local-prefix=/usr/local --infodir=/usr/share/info --mandir=/usr/share/man --enable-languages=c,c++,f77,objc,java,ada --disable-checking --libdir=/usr/lib --enable-libgcj --with-gxx-include-dir=/usr/include/g++ --with-slibdir=/lib --with-system-zlib --enable-shared --enable-__cxa_atexit i586-suse-linux Thread model: posix gcc version 3.3.3 (SuSE Linux) configure:2124: $? = 0 configure:2126: gcc -V /dev/null 5 gcc: `-V' option must have argument configure:2129: $? = 1 configure:2152: checking for C compiler default output file name configure:2155: gccconftest.c 5 configure:2158: $? = 0 configure:2204: result: a.out configure:2209: checking whether the C compiler works configure:2215: ./a.out configure:2218: $? = 0 configure:2235: result: yes configure:2242: checking whether we are cross compiling configure:2244: result: no configure:2247: checking for suffix of executables configure:2249: gcc -o conftestconftest.c 5 configure:2252: $? = 0 configure:2277: result: configure:2283: checking for suffix of object files configure:2304: gcc -c conftest.c 5 configure:2307: $? = 0 configure:2329: result: o configure:2333: checking whether we are using the GNU C compiler configure:2357: gcc -c conftest.c 5 configure:2363: $? = 0 configure:2367: test -z || test ! -s conftest.err configure:2370: $? = 0 configure:2373: test -s conftest.o configure:2376: $? = 0 configure:2389: result: yes configure:2395: checking whether gcc accepts -g configure:2416: gcc -c -g conftest.c 5 configure:2422: $? = 0 configure:2426: test -z || test ! -s conftest.err configure:2429: $? = 0 configure:2432: test -s conftest.o configure:2435: $? = 0 configure:2446: result: yes configure:2463: checking for gcc option to accept ANSI C configure:2533: gcc -c -g -O2 conftest.c 5 configure:2539: $? = 0 configure:2543: test -z || test ! -s conftest.err configure:2546: $? = 0 configure:2549: test -s conftest.o configure:2552: $? = 0 configure:2570: result: none needed configure:2588: gcc -c -g -O2 conftest.c 5 conftest.c:2: error: parse error before me configure:2594: $? = 1 configure: failed program was: | #ifndef __cplusplus | choke me | #endif configure:2738: checking for style of include used by make configure:2766: result: none configure:2794: checking dependency style of gcc configure:2879: result: none
INDEX DESC
Does anyone know when INDEX DESC will be implemented? I'm storing time values and want to access the data from the most recent time value without sorting the result set. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some BLOB help please.
[EMAIL PROTECTED] wrote: You might save some space if you compress() before storing. Depending on file content I'm seeing 0-50% savings? Good idea, but note that COMPRESS() and UNCOMPRESS() weren't introduced until MySQL 4.1.1. With earlier versions you may be able to compress and uncompress in your application before inserting and after selecting (using PHP's gzcompress() and gzuncompress(), for example). -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
My Mysql Info ?
I am setting up an invision power board forum , and the installer requires mysql info , (SQL database name , SQL user name, SQL Password) i am using apache for windows , and i host mysql and php and apache on my computer , i dont know of any passwords, i havnt enetered any passwords as far as mysql , apache, and php go . Can You Help Me To Find Where I Can Get This Information Please . Yours. Chris - Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage!
Form + database
Dear colleagues.. I am new to mysql and I am trying to do the following, to create a php file that may contain some kind of form or survey and the data entered may be added to a database. Here's the code of the html form - html head titleSurvey/title meta http-equiv=Content-Type content=text/html; charset=iso-8859-1 /head body p strongSurvey/strong/p form action= method=post name=importance id=importance table width=605 border=0 tr td strongIs reading important? /strong/td /tr tr td yes input name=radiobutton type=radio value=radiobutton/td /tr tr td no input name=radiobutton type=radio value=radiobutton/td /tr tr td maybe input name=radiobutton type=radio value=radiobutton/td /tr tr td i don't know input name=radiobutton type=radio value=radiobutton/td /tr tr td iquest;Why? /td /tr tr td textarea name=textarea cols=85 rows=5 /textarea/td /tr /table /form br /body /html __ msc. tomas alberto ramirez.andujar webmaster - cejisoft - +53 32 26 24 51 universidad pedagogica jose marti. camaguey circunvalacion norte km 51/2 cp 74670. cuba email : [EMAIL PROTECTED] url: http://www.esicm.cu/cejisoft -Mensaje original- De: tommie ramirez.andujar [mailto:[EMAIL PROTECTED] Enviado el: Saturday, May 29, 2004 12:14 PM Para: [EMAIL PROTECTED] Asunto: Transfer records from one table to another Hi,, I have database about national historical events and another one about world events.. Here's the structure: National events (d: day m: month a: year hecho: fact) |---| | id| d | m | a |hecho | |---| World events (d: day m: month y: year event: fact) |---| | id| d | m | y | event | |---| Now, I want to copy the records from National to World events, is it possible? Yours tommie ___ msc. tomas alberto ramirez.andujar webmaster - cejisoft - +5332 262451 /261410 universidad pedagogica jose marti. camaguey circunvalacion norte km 51/2 cp 74670. cuba [EMAIL PROTECTED] - www.esicm.cu/cejisoft Este mensaje ha sido analizado por MDaemon Antivirus v2.21. Instituto Superior Pedagsgico de Camaguey, Cuba. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Este mensaje ha sido analizado por MDaemon Antivirus v2.21. Instituto Superior Pedagsgico de Camaguey, Cuba. Este mensaje ha sido analizado por MDaemon Antivirus v2.21. Instituto Superior Pedagógico de Camaguey, Cuba. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Form + database
Hi Tommie, From the HTML you have sent there are a number of things you need to do. Firstly you need to group your radio buttons correctly and give them appropriate values. You will need to construct a table in MySQL to take the results. You will need to create the relevant PHP script to receive the form data and build a SQL query to submit to the database. If you are entirely new to this then I would suggest taking a look at some examples on the web. Try: http://www.phpbuilder.com Another option would be to use Dreamweaver and have it write the code for you. It is a great way to get started with PHP and MySQL. http://www.macromedia.com And look for the Dreamweaver MX Trial download. I hope that helps Nic Skitt Real Productions UK Limited http://www.realproductions.co.uk -Original Message- From: tommie ramirez.andujar [mailto:[EMAIL PROTECTED] Sent: 23 June 2004 20:32 To: [EMAIL PROTECTED] Subject: Form + database Dear colleagues.. I am new to mysql and I am trying to do the following, to create a php file that may contain some kind of form or survey and the data entered may be added to a database. Here's the code of the html form - html head titleSurvey/title meta http-equiv=Content-Type content=text/html; charset=iso-8859-1 /head body p strongSurvey/strong/p form action= method=post name=importance id=importance table width=605 border=0 tr td strongIs reading important? /strong/td /tr tr td yes input name=radiobutton type=radio value=radiobutton/td /tr tr td no input name=radiobutton type=radio value=radiobutton/td /tr tr td maybe input name=radiobutton type=radio value=radiobutton/td /tr tr td i don't know input name=radiobutton type=radio value=radiobutton/td /tr tr td iquest;Why? /td /tr tr td textarea name=textarea cols=85 rows=5 /textarea/td /tr /table /form br /body /html __ msc. tomas alberto ramirez.andujar webmaster - cejisoft - +53 32 26 24 51 universidad pedagogica jose marti. camaguey circunvalacion norte km 51/2 cp 74670. cuba email : [EMAIL PROTECTED] url: http://www.esicm.cu/cejisoft -Mensaje original- De: tommie ramirez.andujar [mailto:[EMAIL PROTECTED] Enviado el: Saturday, May 29, 2004 12:14 PM Para: [EMAIL PROTECTED] Asunto: Transfer records from one table to another Hi,, I have database about national historical events and another one about world events.. Here's the structure: National events (d: day m: month a: year hecho: fact) |---| | id| d | m | a |hecho | |---| World events (d: day m: month y: year event: fact) |---| | id| d | m | y | event | |---| Now, I want to copy the records from National to World events, is it possible? Yours tommie ___ msc. tomas alberto ramirez.andujar webmaster - cejisoft - +5332 262451 /261410 universidad pedagogica jose marti. camaguey circunvalacion norte km 51/2 cp 74670. cuba [EMAIL PROTECTED] - www.esicm.cu/cejisoft Este mensaje ha sido analizado por MDaemon Antivirus v2.21. Instituto Superior Pedagsgico de Camaguey, Cuba. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Este mensaje ha sido analizado por MDaemon Antivirus v2.21. Instituto Superior Pedagsgico de Camaguey, Cuba. Este mensaje ha sido analizado por MDaemon Antivirus v2.21. Instituto Superior Pedagógico de Camaguey, Cuba. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.707 / Virus Database: 463 - Release Date: 15/06/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.707 / Virus Database: 463 - Release Date: 15/06/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question on auto increment field
At 17:33 +0800 6/23/04, Joe Wong wrote: Hi Egor, Thanks for your reply. In addition to this, how I can make MySQL to reuse the number which has been deleted? I tried to do a test as follow AUTO_INCREMENT columns never automatically generate numbers that are less that the maximum value currently in the column. If you want to reuse numbers, you'll have to handle this in your application logic. 1. Create a dummy table with a auto increment field 'UID' set to MED INT 2. Manually insert a record that set UID to Max of MED INT, ie 16777215 3. Insert another record without specifying the value of UID At 3, it failed and said: Duplicate entry '16777215' for key 1 But I have only 1 record in the table. Regards, - Wong - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 23, 2004 4:10 PM Subject: Re: question on auto increment field Scott Haneda [EMAIL PROTECTED] wrote: on 06/23/2004 12:14 AM, Joe Wong at [EMAIL PROTECTED] wrote: If it possible to limit the max value of an auto increment field to say, , and how to do it? I am using MySQL 4.0.18. No. The maximum value for the auto_increment column can be limited by the maximum value of the column type(tinyint, int, mediumint etc.). I am not sure, as a 'hack' you could simply insert a blank record with the value set to ,, once you reach that limit and try to insert a record, a error would be generated, no new records would be able to be inserted until this was resolved. It will not work, because if you insert dummy row and set auto_increment value to , the next generated auto_increment value will be 1. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
Re: INDEX DESC
At 11:36 -0400 6/23/04, Alejandro Heyworth wrote: Does anyone know when INDEX DESC will be implemented? I'm storing time values and want to access the data from the most recent time value without sorting the result set. I don't see any relationship between your two sentences? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
Re: INDEX DESC
On Wed, Jun 23, 2004 at 11:36:52AM -0400, Alejandro Heyworth wrote: Does anyone know when INDEX DESC will be implemented? I'm storing time values and want to access the data from the most recent time value without sorting the result set. Why is sorting required at all? Indexes *are* sorted already. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Form + database / PHP survey + database
On Wednesday 23 June 2004 03:32 pm, tommie ramirez.andujar wrote: Dear colleagues.. I am new to mysql and I am trying to do the following, to create a php file that may contain some kind of form or survey and the data entered may be added to a database. Here's the code of the html form Dearest Colleague, Start writing your project now. No one on this list or the PHP list is going to write this code for you. Either list will be glad to help you with problems you are having with code that you have written. Neither list is going to produce your project for you. I would suggest looking at sourceforge.net for pre-written code. Not Your Code-Monkey, Your Colleagues -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
I suspect he is refering to 3.23's inability to use an index on a ORDER BY xxx DESC Jeremy Zawodny wrote: On Wed, Jun 23, 2004 at 11:36:52AM -0400, Alejandro Heyworth wrote: Does anyone know when INDEX DESC will be implemented? I'm storing time values and want to access the data from the most recent time value without sorting the result set. Why is sorting required at all? Indexes *are* sorted already. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
Alejandro Heyworth wrote: Does anyone know when INDEX DESC will be implemented? I'm storing time values and want to access the data from the most recent time value without sorting the result set. Paul DuBois wrote: I don't see any relationship between your two sentences? Jeremy Zawodny wrote: Why is sorting required at all? Indexes *are* sorted already. I expect he's referring to mysql's poor performance when doing ORDER BY indexed_column DESC relative to ORDER BY indexed_column ASC. I don't think he really means without sorting. Instead, I think he means properly using the index for quick results. The manual http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html says An index_col_name specification can end with ASC or DESC. These keywords are allowed for future extensions for specifying ascending or descending index value storage. Currently they are parsed but ignored; index values are always stored in ascending order. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fetching 12 columns or 1 TEXT field?
Which do you folks think is faster: randomly accessing a table with a primary key and a dozen CHAR columns or a table with a primary key and a single merged TEXT column? The data in the 11 extra columns will always be fetched as a single request. I rolled my own benchmarking program and 10,000 runs each came out just about equal, which surprised me a little. Eamon Daly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fetching 12 columns or 1 TEXT field?
In the last episode (Jun 23), Eamon Daly said: Which do you folks think is faster: randomly accessing a table with a primary key and a dozen CHAR columns or a table with a primary key and a single merged TEXT column? The data in the 11 extra columns will always be fetched as a single request. I rolled my own benchmarking program and 10,000 runs each came out just about equal, which surprised me a little. Probably because each disk seek takes much longer to complete than the difference between processing 1 and 12 fields. Timing a sequential walk through the table may show more of a difference. You probably won't see much difference in table size between the two, either, since a VARCHAR requires length(field)+1 bytes of storage, and you would need to put a delimiter between your fields in the TEXT column. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
I don't see any relationship between your two sentences? Thanks for the responses. I guess I was not clear enough in my last post. You can define an index to sort values in a particular order... ASC is the default. DESC is an option, but it is not implemented yet. I want to know when it will be implemented. I would like the values in my table to be stored in DESC order. For example, I want to read ranges of values from my db in the opposite order of how they are currently being indexed without using an extra ORDER BY in my select. This ORDER BY forces an internal sort to put the result set in DESC order which slows my SELECT. Currently, if I insert integer values into a table with an index ASC (or DESC) on the INT column , a general SELECT will return the values in ascending order. 1 2 3 4 5 I want to naturally store the data as: 5 4 3 2 1 without adding an ORDER BY int_col DESC It would make a world of difference to our app since we are dealing with huge ranges of joint compression information that need to be read out sequentially. I hope this is clearer. So, does anyone know when it will be implemented? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sorting strings as integers
Ole Kasper Olsen wrote:
Hi,
I have a database column (VARCHAR) consisting of the following kind of
data:
1
1.1
1.2.1.2
1.10.1
1.2
1.4.1
I need to sort this colum so that the result will be
1
1.1
1.2
1.2.1.2
1.4.1
1.10.1
I was hoping that just using ORDER BY [column] ASC would work, but
alas, it only works for number less than 10 (current collation
considers 1.10 to be before 1.2 but after 1.1, logically enough).
So I was just wondering if anyone had some nice solutions or SQL
statements containing black magic which would do what I want. Maybe a
special collation?
Version numbers?
CREATE TABLE ss (version VARCHAR(13));
INSERT INTO ss VALUES ('1'),('1.1'),('1.2.1.2'),('1.10.1'),('1.2'),
('1.4.1'),('2.1'),('2.2.1.2'),('2.10.1'),('2.4.1');
If each part is no larger than 255, you can leverage INET_ATON() to do what
you want (up to the 4th part). The trick is making each of these look like
an IP first by using CONCAT to add '0.0.0' to make sure every row has at
least 4 parts, then SUBSTRING_INDEX to pull out just the first 4 parts.
SELECT version FROM ss
ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version,'.0.0.0'),'.',4));
+-+
| version |
+-+
| 1 |
| 1.1 |
| 1.2 |
| 1.2.1.2 |
| 1.4.1 |
| 1.10.1 |
| 2.1 |
| 2.2.1.2 |
| 2.4.1 |
| 2.10.1 |
+-+
10 rows in set (0.00 sec)
Now, I must point out that because we are sorting on a function of the
column, rather than on the column itself, we cannot use an index on the
column to help with the sort. In other words, the sorting will be
relatively slow.
One solution would be to separate the parts into separate columns, each of
which could then be an appropriately sized integer (TINYINT, perhaps). You
could use 0 or NULL for the missing parts, as you see fit (NULLS preserve
the output format you specified, e.g. 1.1, but complicate matching).
CREATE TABLE ss2 (v1 TINYINT UNSIGNED, v2 TINYINT UNSIGNED,
v3 TINYINT UNSIGNED, v4 TINYINT UNSIGNED,
KEY version_idx (v1,v2,v3,v4));
INSERT INTO ss2 VALUES (1,NULL,NULL,NULL),(1,1,NULL,NULL),(1,2,1,2),
(1,10,1,0),(1,2,0,0),(1,4,1,0),(2,1,0,0),(2,2,1,2),
(2,10,1,NULL),(2,4,1,NULL);
Glue the parts together with CONCAT_WS() when you select them, and sort by
all 4 parts:
SELECT CONCAT_WS('.',v1,v2,v3,v4) version FROM ss2 ORDER BY v1,v2,v3,v4;
+--+
| version |
+--+
| 1|
| 1.1 |
| 1.2.0.0 |
| 1.2.1.2 |
| 1.4.1.0 |
| 1.10.1.0 |
| 2.1.0.0 |
| 2.2.1.2 |
| 2.4.1|
| 2.10.1 |
+--+
10 rows in set (0.00 sec)
In this case, the multicolumn index on the 4 parts will be used to sort.
Another option would be to use INET_ATON() when storing the values.
CREATE TABLE ss3 (v INT UNSIGNED, KEY version_idx (v));
In this case, you'd have to use 0 for missing parts.
INSERT INTO ss3 VALUES (INET_ATON('1.0.0.0')), (INET_ATON('1.1.0.0')),
(INET_ATON('1.2.1.2')), (INET_ATON('1.10.1.0')),
(INET_ATON('1.2.0.0')), (INET_ATON('1.4.1.0')),
(INET_ATON('2.1.0.0')), (INET_ATON('2.2.1.2')),
(INET_ATON('2.10.1.0')),(INET_ATON('2.4.1.0'));
Use INET_NTOA() when selecting to display dotted numbers, but sort by the
values already in the column.
SELECT INET_NTOA(v) version FROM ss3 ORDER BY v;
+--+
| version |
+--+
| 1.0.0.0 |
| 1.1.0.0 |
| 1.2.0.0 |
| 1.2.1.2 |
| 1.4.1.0 |
| 1.10.1.0 |
| 2.1.0.0 |
| 2.2.1.2 |
| 2.4.1.0 |
| 2.10.1.0 |
+--+
10 rows in set (0.00 sec)
As with the second version, the index on v will be used for the sort.
Finally, starting with mysql 4.1.2, INET_ATON() will assume 0s for missing
parts, so long as you have at least two parts. That is, 1.1 will
automatically be treated as 1.1.0.0, 1.0 as 1.0.0.0, and 1.2.3 as 1.2.3.0
(but 1 will be treated as 0.0.0.1). With 4.1.2 then, the above could be
simplified slightly.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
On Wed, Jun 23, 2004 at 02:28:15PM -0500, gerald_clark wrote: I suspect he is refering to 3.23's inability to use an index on a ORDER BY xxx DESC In other words ancient history :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
On Jun 23, 2004, at 8:15 AM, Michael Stassen wrote: So, if I understand you correctly, somewhere in the middle of a 20,000 row insert, a row gets inserted with auto_increment id = 87,123,456, say, then the next row tries to insert with the value 87,123,457 but fails. You fix this by skipping the next value with ALTER TABLE yourtable AUTO_INCREMENT=87123458 After that, the auto_increment id column resumes working as expected, until the next time. Is that right? Yes. I've looked at the table status, and it appears to be OK, and the AUTO_INCREMENT values look good (except as below). Have you tried resetting the auto_increment id by manually inserting a larger number? INSERT INTO yourtable (id) values (87123458); No... I'll give that a try next time round. multiple row INSERT statement, LAST_INSERT_ID() returns the auto_increment id of the first row inserted. (See http://dev.mysql.com/doc/mysql/en/Information_functions.html) So, if you've just done a multiple row insert, LAST_INSERT_ID()+1 should already exist as an ID in the table. I'll have to take a look at that--hadn't thought to look at the effect of the multiple row insert. Thanks. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question on auto increment field
I seem to recall old versions of MySQL did re-use auto-increment values but this was changed since it's not really supposed to do that ;-) Cheers Andrew. - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Joe Wong [EMAIL PROTECTED]; Egor Egorov [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, June 23, 2004 7:53 PM Subject: Re: question on auto increment field At 17:33 +0800 6/23/04, Joe Wong wrote: Hi Egor, Thanks for your reply. In addition to this, how I can make MySQL to reuse the number which has been deleted? I tried to do a test as follow AUTO_INCREMENT columns never automatically generate numbers that are less that the maximum value currently in the column. If you want to reuse numbers, you'll have to handle this in your application logic. 1. Create a dummy table with a auto increment field 'UID' set to MED INT 2. Manually insert a record that set UID to Max of MED INT, ie 16777215 3. Insert another record without specifying the value of UID At 3, it failed and said: Duplicate entry '16777215' for key 1 But I have only 1 record in the table. Regards, - Wong - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 23, 2004 4:10 PM Subject: Re: question on auto increment field Scott Haneda [EMAIL PROTECTED] wrote: on 06/23/2004 12:14 AM, Joe Wong at [EMAIL PROTECTED] wrote: If it possible to limit the max value of an auto increment field to say, , and how to do it? I am using MySQL 4.0.18. No. The maximum value for the auto_increment column can be limited by the maximum value of the column type(tinyint, int, mediumint etc.). I am not sure, as a 'hack' you could simply insert a blank record with the value set to ,, once you reach that limit and try to insert a record, a error would be generated, no new records would be able to be inserted until this was resolved. It will not work, because if you insert dummy row and set auto_increment value to , the next generated auto_increment value will be 1. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
Alejandro Heyworth wrote: I don't see any relationship between your two sentences? Thanks for the responses. I guess I was not clear enough in my last post. You can define an index to sort values in a particular order... ASC is the default. DESC is an option, but it is not implemented yet. I want to know when it will be implemented. I would like the values in my table to be stored in DESC order. Values are stored wherever the engine finds room. For example, I want to read ranges of values from my db in the opposite order of how they are currently being indexed without using an extra ORDER BY in my select. This ORDER BY forces an internal sort to put the result set in DESC order which slows my SELECT. Currently, if I insert integer values into a table with an index ASC (or DESC) on the INT column , a general SELECT will return the values in ascending order. 1 2 3 4 5 Not guaranteed without ORDER BY. I want to naturally store the data as: 5 4 3 2 1 without adding an ORDER BY int_col DESC You still must. It would make a world of difference to our app since we are dealing with huge ranges of joint compression information that need to be read out sequentially. I hope this is clearer. So, does anyone know when it will be implemented? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
Jeremy Zawodny wrote: On Wed, Jun 23, 2004 at 02:28:15PM -0500, gerald_clark wrote: I suspect he is refering to 3.23's inability to use an index on a ORDER BY xxx DESC In other words ancient history :-) Not only that, but without ORDER BY, no order is assured. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
Alejandro Heyworth wrote: I don't see any relationship between your two sentences? Thanks for the responses. I guess I was not clear enough in my last post. You can define an index to sort values in a particular order... ASC is the default. DESC is an option, but it is not implemented yet. I want to know when it will be implemented. I would like the values in my table to be stored in DESC order. Indexing a column does not cause rows to be stored in index order. For example, I want to read ranges of values from my db in the opposite order of how they are currently being indexed without using an extra ORDER BY in my select. This ORDER BY forces an internal sort to put the result set in DESC order which slows my SELECT. You cannot count on rows being returned in any order unless you explicitly use ORDER BY. Currently, if I insert integer values into a table with an index ASC (or DESC) on the INT column , a general SELECT will return the values in ascending order. 1 2 3 4 5 You cannot count on that. mysql CREATE TABLE ot (val INT); Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO ot VALUES (1), (2), (3), (4), (5); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql DELETE FROM ot WHERE val=3; Query OK, 1 row affected (0.01 sec) mysql INSERT INTO ot VALUES (6), (3); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql SELECT * FROM ot; +--+ | val | +--+ |1 | |2 | |6 | |4 | |5 | |3 | +--+ 6 rows in set (0.00 sec) I want to naturally store the data as: 5 4 3 2 1 without adding an ORDER BY int_col DESC It would make a world of difference to our app since we are dealing with huge ranges of joint compression information that need to be read out sequentially. I hope this is clearer. So, does anyone know when it will be implemented? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
Hey fellas, I think he is trying to ask for the release date (if there is one) for the clustering index to have the option be be a DESCENDING index. I hope he is using InnoDB as that is the only table type that stores records in a specific order. In the short term - improving the ORDER BY ... DESC performance may be easier to implement. If that is sufficiently fast, a descending storage order may not be needed for acceptable performance. Respecfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Alejandro Heyworth [EMAIL PROTECTED]To: [EMAIL PROTECTED] ciples.com cc: Fax to: 06/23/2004 04:09 PM Subject: Re: INDEX DESC I don't see any relationship between your two sentences? Thanks for the responses. I guess I was not clear enough in my last post. You can define an index to sort values in a particular order... ASC is the default. DESC is an option, but it is not implemented yet. I want to know when it will be implemented. I would like the values in my table to be stored in DESC order. For example, I want to read ranges of values from my db in the opposite order of how they are currently being indexed without using an extra ORDER BY in my select. This ORDER BY forces an internal sort to put the result set in DESC order which slows my SELECT. Currently, if I insert integer values into a table with an index ASC (or DESC) on the INT column , a general SELECT will return the values in ascending order. 1 2 3 4 5 I want to naturally store the data as: 5 4 3 2 1 without adding an ORDER BY int_col DESC It would make a world of difference to our app since we are dealing with huge ranges of joint compression information that need to be read out sequentially. I hope this is clearer. So, does anyone know when it will be implemented? -- 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: INDEX DESC
It sounds like the values you want to index our timestamps. If this is the case you can do something tricky like using an integer column, and storing -(unixtimesamp) values so that what mysql sees as ASC will really be your data in DESC order. Of course there is some overhead involved now in doing a unary - operation on each value and in converting the unixtimestamp to whatever format you need it in. I wouldn't recommend relying on the index to keep records sorted anyway, there is no gurantee they will be you really ought to use the order by even when selecting ASC. John A. McCaskey -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 23, 2004 1:30 PM To: Alejandro Heyworth Cc: [EMAIL PROTECTED] Subject: Re: INDEX DESC Alejandro Heyworth wrote: I don't see any relationship between your two sentences? Thanks for the responses. I guess I was not clear enough in my last post. You can define an index to sort values in a particular order... ASC is the default. DESC is an option, but it is not implemented yet. I want to know when it will be implemented. I would like the values in my table to be stored in DESC order. Values are stored wherever the engine finds room. For example, I want to read ranges of values from my db in the opposite order of how they are currently being indexed without using an extra ORDER BY in my select. This ORDER BY forces an internal sort to put the result set in DESC order which slows my SELECT. Currently, if I insert integer values into a table with an index ASC (or DESC) on the INT column , a general SELECT will return the values in ascending order. 1 2 3 4 5 Not guaranteed without ORDER BY. I want to naturally store the data as: 5 4 3 2 1 without adding an ORDER BY int_col DESC You still must. It would make a world of difference to our app since we are dealing with huge ranges of joint compression information that need to be read out sequentially. I hope this is clearer. So, does anyone know when it will be implemented? -- 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: INDEX DESC
Hi Michael, - Original Message - From: Michael Stassen Sent: Wednesday, June 23, 2004 2:30 PM Subject: Re: INDEX DESC Jeremy Zawodny wrote: Why is sorting required at all? Indexes *are* sorted already. I expect he's referring to mysql's poor performance when doing ORDER BY indexed_column DESC relative to ORDER BY indexed_column ASC. The performance is only poor when using an index for DESC, *if the index is PACKED*. (There's different criteria that determines if a certain index is packed.) Otherwise, it should be pretty much exactly the same as ASC. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
Alejandro Heyworth wrote: Currently, if I insert integer values into a table with an index ASC (or DESC) on the INT column , a general SELECT will return the values in ascending order. 1 2 3 4 5 That may be true, but only because you haven't been adding and deleting records. It's not something you can depend on, and it has nothing to with the index. If you want a specific order (ascending or descending), you have to specify it in an ORDER BY clause. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
That could be. If so, it's a MySQL 3.23 limitation that was fixed in MySQL 4.0. So, what we are agreeing on is that MySQL 4.x does in fact support both DESC and ASC indexes? If this is the case and we're doing something wrong here, cool! I definitely think the docs should reflect this functionality! BUT, ALTER TABLE kneejoint ADD INDEX testindex (compression DESC); 1) SHOW INDEX kneejoint only shows ASC collation. 2) SELECT compression FROM kneejoint still returns values in ASC order. We are currently using the 4.1.2 binaries (MyISAM) and the DESC index functionality isn't working for us yet. I hear all of your comments on the actual storage of the data and understand that. However, the indexes should return values in the proper indexed order if the indexes are being used and they are. Our experience is that ORDER BY ___ ASC on an ASC index is faster than an ORDER BY DESC on the same ASC index when the data sets are large. Thanks for the help by the way! We are rapidly migrating our experiments to MySQL and trying to dump SQL Server forever! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
At 17:06 -0400 6/23/04, Alejandro Heyworth wrote: That could be. If so, it's a MySQL 3.23 limitation that was fixed in MySQL 4.0. So, what we are agreeing on is that MySQL 4.x does in fact support both DESC and ASC indexes? No, what we're saying is that in 3.23, MySQL did not efficiently traverse indexes in reverse order for ORDER BY DESC. In 4.0 and up, indexes are still stored in ascending order, but the server now traverses them efficiently in reverse order for DESC sorts. If this is the case and we're doing something wrong here, cool! I definitely think the docs should reflect this functionality! No need, because it's still true that DESC is ignored for index specifications. BUT, ALTER TABLE kneejoint ADD INDEX testindex (compression DESC); 1) SHOW INDEX kneejoint only shows ASC collation. Correct. 2) SELECT compression FROM kneejoint still returns values in ASC order. No. It returns them in an undefined order if you don't use ORDER BY. It may happen to look like ASC order. We are currently using the 4.1.2 binaries (MyISAM) and the DESC index functionality isn't working for us yet. I hear all of your comments on the actual storage of the data and understand that. However, the indexes should return values in the proper indexed order if the indexes are being used and they are. Our experience is that ORDER BY ___ ASC on an ASC index is faster than an ORDER BY DESC on the same ASC index when the data sets are large. Thanks for the help by the way! We are rapidly migrating our experiments to MySQL and trying to dump SQL Server forever! -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
FW: Subject: How can I speed up my queries
I am new to MySQL but I hope that someone in this group can help out. I am doing a research study on speeding up processing with database The platform is a Pentium 4 2.66GHz Pc with 512MB of memory. Now I tried increasing the memory to 2 Gigabytes but it did not seem to improve the performance in any way. I don't see a lot of swapping to disk and the database itself is easily able to fit into the 512kbytes of memory. The operating system is Linux RH9.0 and I am using Mysql 5.0. Does MySQL have some way of easily handling a sliding window? I could only do it by using a series of recursive queries that repeated The basic query shown below over until the career of each player was Completely covered. There are about 15000 players and 80,000 rows in the Batting table itself. The Query that I wrote takes 11 minutes but an oracle query only takes about 1 minute use baseball; ##THIS IS THE M TABLE WHICH GETS THE AVERAGE IN A 5 YR WINDOW### SELECT round(avg(batting.HR),2) as AVG_HR, batting.HR as HR, batting.yearID as YR, #l.nameLast, l.Lname,l.maxyr, l.minyr, l.maxyr- l.minyr as YRS_PLAYED,l.PlayerID as PlyrID FROM ###THIS ADDS THE L QUERY TO FIND ALL PLAYERS WITH = 5YR CAREERS# ( SELECT * FROM ###THIS COMBINES THE J AND K QUERIES INTO A SINGLE K QUERY TO FIND MAX AND MIN YEARS## (SELECT m.nameFirst as Fname, m.nameLast Lname, max(b.yearID) as maxyr, min(b.yearID) as minyr, b.PlayerID, b.HR , max(b.yearID) -min(b.yearID) as YRSPLAYED from master as m, batting as b where (m.PlayerID =b.PlayerID) and b.HR 0 group by b.PlayerID )as k WHERE maxyr-minyr 3 group by k.PlayerID #limit 10 ) as l,batting where (batting.PlayerID = l.PlayerID) and (batting.yearID = l.maxyr) and (batting.yearID = l.maxyr - 4) and (l.maxyr - l.minyr = 3) group by l.Lname limit 10; 00:11:31THIS IS THE ELAPSED TIME FOR WINDOW QUERY As can be seen the time is 11 minutes and 31 seconds Best regards, Peter Reali
Re: INDEX DESC
Hi Gerald, - Original Message - From: gerald_clark Sent: Wednesday, June 23, 2004 2:28 PM Subject: Re: INDEX DESC I suspect he is refering to 3.23's inability to use an index on a ORDER BY xxx DESC That's not always true. 3.23 WILL use the index for ORDER BY ... DESC in a query like this: SELECT * FROM table ORDER BY key DESC LIMIT 10 but it won't for this: SELECT * FROM table WHERE key_part1=123 ORDER BY key_part2 DESC LIMIT 10 e.g. when the index is already used for the WHERE. Of course this was fixed in 4.0. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
At 16:09 -0400 6/23/04, Alejandro Heyworth wrote: I don't see any relationship between your two sentences? Thanks for the responses. I guess I was not clear enough in my last post. You can define an index to sort values in a particular order... ASC is the default. DESC is an option, but it is not implemented yet. I want to know when it will be implemented. I would like the values in my table to be stored in DESC order. For example, I want to read ranges of values from my db in the opposite order of how they are currently being indexed without using an extra ORDER BY in my select. This ORDER BY forces an internal sort to put the result set in DESC order which slows my SELECT. That's not how relational databases work. If you'd like your results returned in a particular order, ORDER BY is mandatory. Otherwise, the server is free to return results in any order it likes. Currently, if I insert integer values into a table with an index ASC (or DESC) on the INT column , a general SELECT will return the values in ascending order. 1 2 3 4 5 I want to naturally store the data as: 5 4 3 2 1 without adding an ORDER BY int_col DESC There are no options for specifying the order in which rows are stored, with the exception of ALTER TABLE tbl_name ORDER BY ... Even that exception goes out the window as soon as you start adding new rows, because the ALTER TABLE statement won't affect how new rows are stored. It would make a world of difference to our app since we are dealing with huge ranges of joint compression information that need to be read out sequentially. It shouldn't make any difference. If you're using MySQL 3.23, please consider upgrading to 4.0, which fixes the problem that ORDER BY DESC doesn't use indexes. I hope this is clearer. So, does anyone know when it will be implemented? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
Re: INDEX DESC
At 15:30 -0400 6/23/04, Michael Stassen wrote: Alejandro Heyworth wrote: Does anyone know when INDEX DESC will be implemented? I'm storing time values and want to access the data from the most recent time value without sorting the result set. Paul DuBois wrote: I don't see any relationship between your two sentences? Jeremy Zawodny wrote: Why is sorting required at all? Indexes *are* sorted already. I expect he's referring to mysql's poor performance when doing ORDER BY indexed_column DESC relative to ORDER BY indexed_column ASC. I don't think he really means without sorting. Instead, I think he means properly using the index for quick results. The manual http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html says An index_col_name specification can end with ASC or DESC. These keywords are allowed for future extensions for specifying ascending or descending index value storage. Currently they are parsed but ignored; index values are always stored in ascending order. Michael That could be. If so, it's a MySQL 3.23 limitation that was fixed in MySQL 4.0. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
Re: INDEX DESC
Matt W wrote: Hi Michael, - Original Message - From: Michael Stassen I expect he's referring to mysql's poor performance when doing ORDER BY indexed_column DESC relative to ORDER BY indexed_column ASC. The performance is only poor when using an index for DESC, *if the index is PACKED*. (There's different criteria that determines if a certain index is packed.) Otherwise, it should be pretty much exactly the same as ASC. Matt You, Jeremy, and Paul are in complete agreement on this, so it must be true, but I'm a bit surprised. There have been quite a few threads about the slowness of ORDER BY ... DESC, and this is the first time I've seen someone respond to say that it's fixed in 4.0. That's not really evidence, of course, but I'm left wondering what the point of implementing INDEX (colname DESC) is. In any case, perhaps you could explain the following result: SHOW CREATE TABLE inits; CREATE TABLE `inits` ( `id` int(11) NOT NULL auto_increment, `init` char(1) default NULL, PRIMARY KEY (`id`), KEY `init_idx` (`init`) ) TYPE=MyISAM Table inits has 50,000 rows of randomly distributed characters. SHOW INDEX FROM inits; +---++--+--+-+---+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+ | inits | 0 | PRIMARY |1 | id | A | 5 | NULL | NULL | | BTREE | | | inits | 1 | init_idx |1 | init| A | 26 | NULL | NULL | YES | BTREE | | +---++--+--+-+---+ SELECT init FROM inits GROUP BY init ORDER BY init; +--+ | init | +--+ | A| | B| | C| ... | X| | Y| | Z| +--+ 26 rows in set (0.39 sec) SELECT init FROM inits GROUP BY init ORDER BY init DESC; +--+ | init | +--+ | Z| | Y| | X| ... | C| | B| | A| +--+ 26 rows in set (4.09 sec) This is with mysql 4.0.20. As you can see, SHOW INDEX says the index on init is not packed, but DESC ordering takes 10 times as long. Explain, in case you are wondering, indicates the index on init is being used in both cases. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fetching 12 columns or 1 TEXT field?
At 03:29 PM 6/23/04, Eamon Daly wrote: Which do you folks think is faster: randomly accessing a table with a primary key and a dozen CHAR columns or a table with a primary key and a single merged TEXT column? The data in the 11 extra columns will always be fetched as a single request. Both the same - with extremely minor variance. I rolled my own benchmarking program and 10,000 runs each came out just about equal, which surprised me a little. Wow. I'm right - what's the prize? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
