Re: upgrade 3.23 to 4.12 slowness
--- Jeff Lacki [EMAIL PROTECTED] wrote: +-+--+--+-++---+ | Field | Type | Null | Key | Default| Extra | +-+--+--+-++---+ | id | bigint(20) | | | 0 | | | is_active | varchar(10) | | | | | | last_login | date | | | -00-00 | | | reason | varchar(100) | YES | | NULL | | | term_reason | varchar(100) | YES | | NULL | | | last_login_time | time | | | 00:00:00 | | +-+--+--+-++---+ 6 rows in set (0.00 sec) When I removed the is_active and user_account1 from the following select statement it is immediate: SELECT userid, nickname, city, state, country, email, sex, birthday, marital_stat, num_children, height, weight, smoke, drink, born_again, image1, is_active FROM users1, user_stats1, user_wants1, user_account1 WHERE sex='m' AND userid=user_stats1.id AND userid=user_wants1.id AND userid=user_account1.id; Id still like to know why, but at least its a huge start. Jeff Hi Jeff, Since user_account1.id is not an index of user_accound1, the server has to go and browse the whole dataset to search for matches, try creating an index on user_account1 using id and run an explain statement against your query. hope it helps, esv. Enrique Sanchez Vela email: [EMAIL PROTECTED] - It's often easier to fight for one's||We live in the outer space principles than to live up to them||Rev. Kay Greenleaf Adlai Stevenson|| Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Child-Parent Relationships with 2 Parents
David Blomstrom wrote: I just thought of a possible solution. Instead of listing all the phyla, orders, classes, families, genera and species in one big table, create separate tables for each taxonomic level. Each taxon would then have two parents. the genus Sus' (pigs) parents would be both the subfamily Suinae and family Suidae. But the genus Panthera doesn't belong to a subfamily, so its family (Felidae) would be listed as both Parent and Parent2. NAME | PARENT | PARENT2 Sus | Suinae | Suidae Panthera | Felidae | Felidae Then I could write PHP scripts focusing on either Parent or Parent2, depending on what I want to do. Does this sound sensible? Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs I'll be honest, I forgot most of that stuff from Biology. However, in general if an item can have two or more parents, then you can use a separate table to represent that relationship. For example: ParentTable --- ParentId Attribute1 Attribute2 ... RelationshipTable ParentId ChildId ChildTable ChildId Attribute1 Attribute2 ... Now, the RelationshipTable can have zero to many relationships between a child and a parent. You can even add a Label column in there if you want to label the relationship type somehow. And it may be better to create separate tables for each taxonomic level, like you said. Eventually one day you may want to have attributes (columns) of a species, that may be different from attributes (columns) of a genus. -- http://www.douglassdavis.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
convert varchar to char
Hi, I try to convert a varchar to a char, but it doesn't seems to work. show create table sessions; CREATE TABLE `sessions` ( `id` varchar(32) NOT NULL default '', `user_id` int(6) NOT NULL default '0', `ip` varchar(8) NOT NULL default '0', `lastseen` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `expire` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`), KEY `user_id2` (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ALTER TABLE sessions MODIFY ip char(8) NOT NULL DEFAULT '0'; ALTER TABLE sessions MODIFY id char(32) NOT NULL DEFAULT ''; show create table sessions; CREATE TABLE `sessions` ( `id` varchar(32) NOT NULL default '', `user_id` int(6) NOT NULL default '0', `ip` varchar(8) NOT NULL default '0', `lastseen` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `expire` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`), KEY `user_id2` (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 What am I doing wrong ? -- Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: convert varchar to char
Pooly wrote: Hi, I try to convert a varchar to a char, but it doesn't seems to work. From the manual: ...all CHAR columns longer than three characters are changed to VARCHAR columns. URL: http://dev.mysql.com/doc/mysql/en/silent-column-changes.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: convert varchar to char
Damnit ! Thanks for pointing it, I forgot these things. But it's a bit more subtle : If any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. I get it working with : ALTER TABLE sessions MODIFY id char(32) NOT NULL DEFAULT '', MODIFY ip char(8) NOT NULL DEFAULT '0'; show create table sessions; CREATE TABLE `sessions` ( `id` char(32) NOT NULL default '', `user_id` int(6) NOT NULL default '0', `ip` char(8) NOT NULL default '0', `lastseen` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `expire` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`), KEY `user_id2` (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 and now I've got fixed-length rows ! Thanks 2005/8/13, Roger Baklund [EMAIL PROTECTED]: Pooly wrote: Hi, I try to convert a varchar to a char, but it doesn't seems to work. From the manual: ...all CHAR columns longer than three characters are changed to VARCHAR columns. URL: http://dev.mysql.com/doc/mysql/en/silent-column-changes.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: convert varchar to char
On Sat, Aug 13, 2005 at 04:01:38PM +0100, Pooly wrote: ALTER TABLE sessions MODIFY ip char(8) NOT NULL DEFAULT '0'; ALTER TABLE sessions MODIFY id char(32) NOT NULL DEFAULT ''; Hello, Since you have two varchar columns, I don't think there's any way to convert them both to char without dropping one first. MySQL will always silently convert a char into a varchar if the table is already a dynamic-row-length type (which it is, because the other varchar makes it that way). So neither statement actually does anything, they're both null operations. The only way you can do this would be to move all data from, say, ip, into another table temporarily, then drop that column, then change id into a char, then create ip as a char, and import it all back. This reveals a bit of a shortcoming in alter table that you can't atomically modify two columns at once, which might get around this problem. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: convert varchar to char
On Sat, Aug 13, 2005 at 05:37:56PM +0100, Pooly wrote: Damnit ! Thanks for pointing it, I forgot these things. But it's a bit more subtle : [snip] Bah, should have waited another 5 minutes before I bothered posting my last long-winded ramble ;) ALTER TABLE sessions MODIFY id char(32) NOT NULL DEFAULT '', MODIFY ip char(8) NOT NULL DEFAULT '0'; Cool, I didn't know you could do this though. Ta :) -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spatial extensions - SRID
At 16:27 -0400 8/12/05, [EMAIL PROTECTED] wrote: To the powers that be: Can we get a MySql GIS/spatial list?? This is going to be a very popular area (actually it is already). I am trying to use the Spatial extensions to MySql. To be honest PostGIS has many more features, but MySql is my favorite, and on top of that, the applications I am using only work with MySql. My problem is this. From the manual, all Geometry contains: Its SRID, or Spatial Reference Identifier. This value identifies the geometry's associated Spatial Reference System that describes the coordinate space in which the geometry object is defined. In MySQL, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry. This is a big issue. Will this be the same in version 5? Or will the SRIDs actually be used? I would like to just represent everything in lat/lon, not on a planar surface. Now, if the SRIDs are used in MySql 5, where is the list of acceptable values? As far as I know, it's the same in MySQL 5. Also, to developers on the list: perhaps it would be better if these were text values like 'NAD83'?? I think that is unlikely to happen. The OpenGIS spec defines the SRID to be an integer, and the SRID() function to be an integer-valued function. -- 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: convert varchar to char
Yeah, for example the last statement ALTER, ..., ... is the only way sometimes to make things working. anyway, it's worth knowing it. 2005/8/13, Chris Elsworth [EMAIL PROTECTED]: On Sat, Aug 13, 2005 at 05:37:56PM +0100, Pooly wrote: Damnit ! Thanks for pointing it, I forgot these things. But it's a bit more subtle : [snip] Bah, should have waited another 5 minutes before I bothered posting my last long-winded ramble ;) ALTER TABLE sessions MODIFY id char(32) NOT NULL DEFAULT '', MODIFY ip char(8) NOT NULL DEFAULT '0'; Cool, I didn't know you could do this though. Ta :) -- Chris -- Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index - max key length is 1024 bytes
What are the pros and cons in resizing the MAX index size, from 1024. - javabuddy People are conversing... without posting their email or filling up their mail box. ~~1123957730975~~ roomity.com http://roomity.com/launch.jsp No sign up to read or search this Rich Internet App -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spatial extensions - SRID
Paul DuBois wrote: At 16:27 -0400 8/12/05, [EMAIL PROTECTED] wrote: This is a big issue. Will this be the same in version 5? Or will the SRIDs actually be used? I would like to just represent everything in lat/lon, not on a planar surface. Now, if the SRIDs are used in MySql 5, where is the list of acceptable values? As far as I know, it's the same in MySQL 5. Thanks Paul. Is there any one out there using MySql for any serious GIS applications? If you are, I would like to know what types of things have you done with it? Also, could you please respond and tell how you address the issue of calculations in a planar space (such as Distance(point, point) ) for example? -- http://www.douglassdavis.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: spatial extensions - SRID
Hello, I have a html page with 70+ form fields some like 40 fields are only used for entering quantity numbers… Don’t want to do Mysql table with 70 fields… Is it a good idea to put this 50 fields of the form fields into a single text mysql field? Somehow process it with php before, put inside of some kind of xml structure? Thanks, Andras Kende http://www.kende.com -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 0.0.338 / Virus Database: 267.10.8/71 - Release Date: 8/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character Set Question
Need more information... what exactly is Our older version, and what exactly is the newest version of MySql, without this it's hard to know what issues you may be facing... I imagine you are on 4.0.n for the old and 4.1.n for the new... but we can't really tell from the information you gave us. When you run mysqldump you get an output file with everything in it... I suggest running mysqldump --tab=/var/tmp/somedirectory which will create a series of files in the location you specify, with a .sql file for each table with the create table command, and a .txt file for each table with the data in tab delimited format. This gives you an easy way to edit the create table statements to make sure each table has the character set information you really want in it before you import the data. Then you can do the import using cat *sql | mysql database to create the tables, and run mysqlimport against the .txt files to insert the data. Using this process you can more precisely manage your tables so they have the right character set for each column... you can do it by editing your regular mysqldump output file, but it's a big file and this way is just easier... it's also quicker to do the import this way. Be sure to dump the old database using the old mysqldump, that way if there was no character set information it won't put something in there by mistake (the new mysqldump could insert something of it's choosing if there is nothing defined)... be sure to use the new mysql client and mysqlimport to insert the data into the new version, making sure to use an appropriate --default-character-set setting each time you call it. Best Regards, Bruce On Aug 12, 2005, at 4:24 AM, James Sherwood wrote: Hello, We have installed the newest version of MySql and cannot get it to play nice with French characters. Our older version worked fine. The problem may (or may not) be that when we put the dump into the new database(yes its default charset is Utf8) the default character set for the table is Utf8 but some fields are like this: 'Story' longtext character set latin1 NOT NULL We tried linking our tomcat to the old database on the other server through this tomcat and everything works fine but when we link the tomcat back to the new database, it will not play nice with french characters. (they come out as outlined squares etc) Any ideas would be greatly appreciated James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spatial extensions - SRID
Andras, Is it a good idea to put this 50 fields of the form fields into a single text mysql field? Somehow process it with php before, put inside of some kind of xml structure? Nothing wrong with 70 columns unless there are interdependencies /or groupings amongst the 70 values that require normalisation. Definitely, 70 numeric columns would be a much better idea than one text column with 70 numeric values embedded in it! Sounds as if you need to read a bit about normalisation. PB - Andras Kende wrote: Hello, I have a html page with 70+ form fields some like 40 fields are only used for entering quantity numbers… Don’t want to do Mysql table with 70 fields… Is it a good idea to put this 50 fields of the form fields into a single text mysql field? Somehow process it with php before, put inside of some kind of xml structure? Thanks, Andras Kende http://www.kende.com No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.8/71 - Release Date: 8/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
