Re: Create Schema and repeatable import of XML
You can have a look at XMLPipeDB. http://sourceforge.net/projects/xmlpipedb/ Iñigo On 03/09, Sayth Renshaw wrote: Hi Hoping someone can help me work some information or direction to a good resource for using XML with Mysql. Specifically I have a complex XML file, I would like to create a schema in MYSQL based on it (I have XSD as well) and be able to upload new xml data into the database as it's released to me. I cannot find a good way to achieve this, currently I am trying to use mysql for excel and map via excel into mysql. The data format is complex and a bit beyond scope of excel. Are there good resources that how to work with XML? Google searches don't show much but confusion and referral to expensive software such as XMLspy. If xml was supposed to be a universal format why is it so hard to work with. Sayth. pgpzWxCo3cswL.pgp Description: PGP signature
RE: query tuning
Good advices indeed. Thanks for sharing. iñ On Wed, 14 Nov 2012, Rick James wrote: A PRIMARY KEY is a KEY, so the second of these is redundant: PRIMARY KEY (`zzk`), KEY `zzk` (`zzk`), Compound indexes are your friend. Learn from http://mysql.rjweb.org/doc.php/index1 `zzr_StatusTime` mediumtext, `zzr_StatusDate` mediumtext, Don't need 16MB for a date or time. Combining the fields is usually a better idea. VARCHAR(255), and especially TEXT/MEDIUMTEXT, can hurt performance when temp tables needed in a query -- use something sensible. `NameLastFirstMI` varchar(255) DEFAULT NULL, 1 letter? Occupying up to 767 bytes (255 utf8 chars)! InnoDB is generally faster than MyISAM. KEY `isdigitized` (`IsDigitized`), Rarely is a 'flag' (yes/no) value worth indexing. `is_deleted` int(11) DEFAULT NULL, If it is just a 0/1 flag, then consider TINYINT UNSIGNED NOT NULL -- it will shrink the space for that field significantly. `Q01_answer` text, `Q01_title` text, `Q02_answer` text, `Q02_title` text, Generally better to have another table for arrays; in this case it might have 4 columns: Appt_zzk, question_number, answer, title. and multiple rows for each Appt. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 4068352 kB' Sounds confused about the engine?? Most fields are NULLable. Not a serious issue, but not good practice. As you add INDEX(ProcModeCode, dateexam) you can DROP this as being redundant: KEY `ProcModeCode` (`ProcModeCode`) The quick fix is to try the index suggested. It should change from 1 SIMPLE e index dateexam,IdAppt,ProcModeCodePRIMARY 4 NULL 1121043 Using where Note the huge estimated number of rows. That should decline significantly. What's the value of key_buffer_size? It should generally be 20% of _available_ RAM (for MyISAM usage). -Original Message- From: James W. McNeely [mailto:jmcne...@nwrads.com] Sent: Wednesday, November 14, 2012 3:48 PM To: mysql@lists.mysql.com Subject: Re: query tuning Rick, Thanks! Here is the create table info. Sorry for the size, but you can skip down from all the fields to see the indexes. CREATE TABLE `exams` ( `zAddDate` date DEFAULT NULL, `zModDate` date DEFAULT NULL, `IdPatient` varchar(32) DEFAULT NULL, `zModBy` varchar(255) DEFAULT NULL, `Notes` mediumtext, `TechComments` mediumtext, `NameLastFirstMI` varchar(255) DEFAULT NULL, `IdExam` varchar(32) DEFAULT NULL, `IdAppt` varchar(255) DEFAULT NULL, `IdProcedure` varchar(255) DEFAULT NULL, `ProcName` mediumtext, `zzr_StatusBy` mediumtext, `zzr_StatusTime` mediumtext, `zzr_StatusDate` mediumtext, `zAddBy` varchar(255) DEFAULT NULL, `zMasterUserTest` varchar(255) DEFAULT NULL, `ProcAreaCode` mediumtext, `ClinicalICD9_1` mediumtext, `ICD9Text` mediumtext, `FilmsStandard` mediumtext, `FilmsAlternate` mediumtext, `OutsideSource` mediumtext, `ProcCode` mediumtext, `ClinicalText` mediumtext, `OutsideStatus` mediumtext, `OutsideDate` varchar(255) DEFAULT NULL, `ProcModeCode` varchar(16) DEFAULT NULL, `DateOfBirth` varchar(255) DEFAULT NULL, `PathDiagnosis` mediumtext, `PathSize` mediumtext, `PathBiopsy` mediumtext, `PathBiopsyDate` varchar(255) DEFAULT NULL, `ClinicalICD9_2` mediumtext, `ClinicalICD9_3` mediumtext, `RefPractInstructions` mediumtext, `_External` mediumtext, `FindCode` mediumtext, `NWRaccession` varchar(255) DEFAULT NULL, `Gender` varchar(255) DEFAULT NULL, `IdOverReadFacility` varchar(255) DEFAULT NULL, `ProcDorS` mediumtext, `CompareToDate` varchar(255) DEFAULT NULL, `IdRefSite` varchar(255) DEFAULT NULL, `IsBillable` varchar(255) DEFAULT NULL, `LastWordProcRt` mediumtext, `IdPerformedBy` varchar(255) DEFAULT NULL, `ContrastRiskFactors` mediumtext, `TimeToCompletion` varchar(255) DEFAULT NULL, `ContrastDose` mediumtext, `OutsideSourceOther` mediumtext, `SiteSide` mediumtext, `SiteLocation` mediumtext, `OverReadExamNumber` varchar(255) DEFAULT NULL, `IsOverRead` varchar(255) DEFAULT NULL, `OverReadCharge` varchar(255) DEFAULT NULL, `IsBillIns` varchar(255) DEFAULT NULL, `LastWordProcLt` mediumtext, `IsRLB` varchar(255) DEFAULT NULL, `IsBCHP` varchar(255) DEFAULT NULL, `IsNWRCoordUSOverride` varchar(255) DEFAULT NULL, `IsTechOnly` varchar(255) DEFAULT NULL, `IsProcNotComplete` varchar(255) DEFAULT NULL, `IsWaiverSigned` varchar(255) DEFAULT NULL, `IdInsur1` varchar(255) DEFAULT NULL, `IdInsur2` varchar(255) DEFAULT NULL, `IsNoInsurance` varchar(255) DEFAULT NULL, `IsPrintBillInfo` varchar(255) DEFAULT NULL, `OverReadChargePrinted` varchar(255) DEFAULT NULL, `RefSiteOther` varchar(255) DEFAULT NULL, `IsOverReadBillIns Copy` varchar(255) DEFAULT NULL, `ExamRoom` varchar(255) DEFAULT NULL, `IdProtocol` varchar(255) DEFAULT NULL, `IsTransDelayed` varchar(255) DEFAULT NULL, `Patient Shielded` varchar(255) DEFAULT NULL, `Patient Permitted` varchar(255)
Re: How to create new mysql instance
Can any body help me how to create new instance at the same mysql databas server in 5.0.85 community version ? You might find useful: http://code.openark.org/blog/mysql/manually-installing-multiple-mysql-instances-on-linux-howto At least i used that last time i had to set 2 instances on my machine. iñigo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Could somebody tell me how to get the time of a database?
Hi Moon's Father, :-) what about through filesystem? $ ls -la /path-mysql-databases or something like that. Iñigo I want to know the create time of a database,but I can not find a way till now. Any reply is appreciated. -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Could somebody tell me how to get the time of a database?
Thanks for your reply very much. I know this way. :-) Could you know how to get this just by sql? Mmmhh, no idea right now. I think unless you logged the CREATE DATABASE I don't think the creation date is stored anywhere. Anybody could check that? Iñigo On Mon, May 12, 2008 at 2:57 PM, Iñigo Medina García [EMAIL PROTECTED] wrote: Hi Moon's Father, :-) what about through filesystem? $ ls -la /path-mysql-databases or something like that. Iñigo I want to know the create time of a database,but I can not find a way till now. Any reply is appreciated. -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Block size of filesystem
Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo -- Iñigo Medina García Librería Díaz de Santos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Block size of filesystem
Thanks, Aaron. Will you be using the MyISAM or InnoDB table engines? Both, but InnoDB more. I had heard that InnoDB uses 16k blocks internally, so that might be a good starting point, though I'd love to have someone confirm or deny that this is actually true. Ok, that's interesting. :-) I'll check it. Iñigo Aaron Blew wrote: Will you be using the MyISAM or InnoDB table engines? I had heard that InnoDB uses 16k blocks internally, so that might be a good starting point, though I'd love to have someone confirm or deny that this is actually true. -Aaron On Fri, May 9, 2008 at 12:01 AM, Iñigo Medina García [EMAIL PROTECTED] wrote: Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo -- Iñigo Medina García Librería Díaz de Santos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Block size of filesystem
Thanks Ben. I would use as large a block size as you dare, especially with InnoDB. Makes reading and writing faster as custs down seek time as cuts down disk fragmenation and avoids block table reads. With MyIsam you have lots of files, but if you only have a few again might work well with a large block size. Perfect. That sounds the way Aaron explain about 16k blocks in InnoDb. And actually we have more InnoDB tables tan MyISAM. Also have a look at the stripe size of your raid system, might work well aligning them if you can. This URL also gives some tips for an ext3 file system on RAID, look for 'stride': http://insights.oetiker.ch/linux/raidoptimization.html Perfect. I'll check it. Iñigo Ben Clewett wrote: I would use as large a block size as you dare, especially with InnoDB. Makes reading and writing faster as custs down seek time as cuts down disk fragmenation and avoids block table reads. With MyIsam you have lots of files, but if you only have a few again might work well with a large block size. Also have a look at the stripe size of your raid system, might work well aligning them if you can. This URL also gives some tips for an ext3 file system on RAID, look for 'stride': http://insights.oetiker.ch/linux/raidoptimization.html Ben Iñigo Medina García wrote: Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Block size of filesystem
I'm seeing that architecture has almost allways the limit in 4 kb (block size - page). Theoretically architecture of 64 bits would offer up to 8 kb, but it seems to be that it usually has 4 kb too because of compatibiliry issues with its i386 ancestors. Any idea about that? We run Intel Core 2 Duo on kernel 2.6.21-1 and i386 by now. Do you think it could be better to change the architecture in order to get the advantages of 8 kb blocks size? Iñigo Iñigo Medina García wrote: Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Block size of filesystem
Will you be using the MyISAM or InnoDB table engines? I had heard that InnoDB uses 16k blocks internally, so that might be a good starting point, though I'd love to have someone confirm or deny that this is actually true. Yep, Aaron. Look at: http://www.innodb.com/innodb/features/ and expand the *wspifmd* link read more of InnoDB Features: database page sizes configurable, from 4 to 64 kB, default 16 kB. Iñigo -Aaron On Fri, May 9, 2008 at 12:01 AM, Iñigo Medina García [EMAIL PROTECTED] wrote: Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo -- Iñigo Medina García Librería Díaz de Santos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Log Optimisation
Hi Ben, Dear MySql, I am trying to optimise InnoDB, and trying to find out how much of the innodb log file contains row data which has not been written to storage. Therefore I can optimize the size of the log, keeping it low to reduce crash recovery time yet high enough to be useful. :-) I can see the varaible 'Innodb_data_pending_writes' which may be what I need. This is zero, I am not sure whether this means I have no uncommitted data in the log, or something else. Can't find anything much on Google. If any member can help me, would be very usesful :) What spits SHOW INNODB STATUS\G ? Iñigo Regards, Ben. * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
