Re: Sending a fax
What is this “fax” he is speaking of? On Jun 15, 2015, at 9:28 AM, Jørn Dahl-Stamnes sq...@dahl-stamnes.net wrote: On Monday, June 15, 2015, Trianon 33 wrote: All, Maybe a somewhat weird request: I need to verufy my fax can receive foreign faxes. If someone (one is really enough) send me a (preferrably 2 pager) fax on +31848708584 (destination is in The Netherlands) I hope noone is that stupid. I'm sure it cost the sender a lot to send fax/call that number! -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: weird difference in workbench and CLI query
Rik,
Your Crystal Ball was right! How did I miss that? You get the brownie point for
the day. Thanks!
I knew it wasn't version discrepancy because workbench ssh's into the DB, and
the script does as well, so it is running it on the native client in both
cases. My sysadmin was saying it was version discrepancy as well and I was just
sure that wasn't it.
Jim McNeely
On Feb 13, 2012, at 9:11 AM, Rik Wasmus wrote:
When I run this query in workbench:
select c.acc_number 'Accession Number', e.DateExam 'MBI Exam Date',
s.ExamDate 'SJH Exam Date' from chestcon_log c
left join exams e on CONCAT(000,c.acc_number) = e.LastWordAccNum
left join sjhreports_ s on c.acc_number = s.AccessionNumber
WHERE
c.timestamp_exam = CAST(DATE_ADD(CONCAT(CURDATE(), ' 23:59:59'), INTERVAL
'-1' DAY) AS DATETIME) AND c.timestamp_exam =
CAST(DATE_ADD(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL '-14' DAY) AS
DATETIME)
I get this:
7330565 NULL 2012-02-01
6604419 2011-01-25 NULL
but when I run the same query in a shell script, on a Linux box or OS X, I
get this: 7330565NULL2012-02-01
6604419 NULLNULL
I see mixes of single ' and double ... And this is the line that fails, the
only occurance of :
left join exams e on CONCAT(000,c.acc_number) = e.LastWordAccNum
Which my crystal ball tells me is probably a sign of improper escapes in
shell
scripts. What happens if you replace that line with:
left join exams e on CONCAT('000',c.acc_number) = e.LastWordAccNum
--
Rik Wasmus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
Perfect!! This is the answer I was looking for. Thanks! I didn't know about this. Jim McNeely On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: UPDATE triggers with REPLACE statements
In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Does anyone know what engine this is? I can't seem to find any info via google. If I could live with the choice of engine, I could make this work with no extra programming at all. Thanks, Jim McNeely On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: UPDATE triggers with REPLACE statements
With REPLACE, you just set up the query the same as an INSERT statement but otherwise it just works. With ON DUPLICATE UPDATE you have to set up the whole query with the entire text all over again as an update. The query strings for what I'm doing are in some cases pushing enough text in medical report fields that it uses a MediumText data type, and I am watchful of bandwidth and performance, so this seems better - I'm not sending the field names and values twice. It is also something I don't have to program, I can just set the engine. The performance bottleneck is NOT likely going to be MySQL with either engine, but the processes creating these queries have some limitations. Anyway, I just thought I would share. BTW I experimented, and innoDB does updates and fires off update triggers for REPLACE statements, but MyISAM does delete/inserts. Jim McNeely On Dec 19, 2011, at 1:28 PM, Claudio Nanni wrote: Good to know and good that you took time to read the manual, good approach. But why bother with REPLACE if you will go with INSERT.ON DUPLICATE KEY UPDATE? The storage engine is a property of your table and you can set it and/or change it, it is the low-level layer (physical) of the database that takes care on how data is actually stored and retrieved. You can check your table with: SHOW TABLE STATUS LIKE 'your-table-name'; Manual page: http://kae.li/iiiga Cheers Claudio 2011/12/19 Jim McNeely j...@newcenturydata.com In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Does anyone know what engine this is? I can't seem to find any info via google. If I could live with the choice of engine, I could make this work with no extra programming at all. Thanks, Jim McNeely On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
Not if you are using innoDB tables. For these, you use INSERT and UPDATE triggers. Jim McNeely On Dec 19, 2011, at 11:58 AM, Halász Sándor wrote: 2011/12/19 11:30 -0800, Jim McNeely In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Well, try it--but beware of these statements: the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. If accurate, the triggers are those of DELETE INSERT, not UPDATE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
UPDATE triggers with REPLACE statements
Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Backup Policy
You might want to look into replication (http://dev.mysql.com/doc/refman/5.5/en/replication.html). You can set up a replication slave to follow the master DB in real time, or offset by minutes, hours, days, or weeks, or whatever. That way you have a copy already served up waiting in the wings, very accessible. It's best to have at least one slave that is offset by at least a day IMHO because the problem could be human error and this will be faithfully replicated to the slave. MySQL replication works very well. Doing a dump is useful in some situations but we are more and more looking to more convenient ways, as storage and hardware is pretty cheap but time in a critical failure is not cheap. Jim McNeely On Mar 15, 2011, at 1:51 PM, Wm Mussatto wrote: On Tue, March 15, 2011 12:36, Joerg Bruehe wrote: Hi! Adarsh Sharma wrote: Dear all, Taking Backup is must needed task in Database Servers. [[...]] Correct. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. RAID is no backup! A RAID system may give you protection against a single disk drive failing (depending on the RAID level you configure), but it doesn't protect you against misuse, operator error, malware (virus, trojan), wilful deletion, ... RAID is no backup! (This can't be repeated often enough.) Doing a backup means to take the data (including schema, privileges, passwords, triggers, ...) to some independent media where it is safe from all misfunction on the original machine. IMNSHO, a backup must be taken offline or write-protected in some other way, so that even a misfunction of the backup machine does not damage your backup. Old tape drives (or newer tape cartridges) with their physical write protection (ring, slider, ...) did provide such protection, it is a pity that they are too slow and too small for today's data (or too expensive for most people). With disks, my solution is: - Have the backup disks on a separate machine, via the network. - Have external backup disks, which are powered off if not is use. - Have two (or more) and use them alternating, so that even in case of a misfunction or drive failure (affecting the backup disk currently in use) the previous backup (on the other disk) remains safe. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. If you can afford downtime (shutting down the database), dirvish is a good means to take a file system backup (all your data areas). Check it at www.dirvish.org There are plenty of alternatives, but I didn't try most of them. What I did try was rsnapshot, but I found it too inflexible for my purposes. I can't comment on the other approaches. Whatever approach you take: Make sure the backup gets stored os some separate, protected media. HTH, J�rg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com Another advantage of external drives is they can be taken OFF site and stored away from the building. We use three. One on site, one in transit and one that mirrors (off site of course) the images of the other two. We dump nightly and then backup that. We also backup the binary logs which get rotated every two days (restore is nightly back followed by the binary logs). The only only restore we have had to do is the nephew who knows html. The disks are raided, but as was stated, that is to protect against single point failure. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=j...@newcenturydata.com
Re: Help with slow query
Shawn, Thanks for the great help! It still is not working. I did an EXPLAIN on this query with your amended split out join statements and got this: ++-+---+---+---++-+--++-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+---+---+---++-+--++-+ | 1 | SIMPLE | a | range | apptdate | apptdate | 4 | NULL | 296148 | Using where | | 1 | SIMPLE | p | ALL | NULL | NULL | NULL| NULL | 262462 | | | 1 | SIMPLE | t | ALL | NULL | NULL | NULL| NULL | 311152 | | | 1 | SIMPLE | c | ref | IdApptType| IdApptType | 51 | func | 1 | | | 1 | SIMPLE | af| ALL | NULL | NULL | NULL| NULL | 5680 | | ++-+---+---+---++-+--++-+ What I'm not catching is why it says there is no key it can use for the patient table; here is a portion of the show create: PRIMARY KEY (`zzk`), KEY `IdPatient` (`IdPatient`), KEY `SSN` (`SSN`), KEY `IdLastword` (`IdLastword`), KEY `DOB` (`DateOfBirth`), KEY `NameFirst` (`NameFirst`), KEY `NameLast` (`NameLast`) So, the IdPatient is at least a POSSIBLE key, right? On Mar 9, 2011, at 8:34 PM, Shawn Green (MySQL) wrote: Hi Jim, On 3/9/2011 17:57, Jim McNeely wrote: I am trying to set up an export query which is executing very slowly, and I was hoping I could get some help. Here is the query: SELECT a.IdAppt, a.IdPatient, p.NameLast, p.NameFirst, p.NameMI, a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate, , a.ApptTimeOut) AS CHAR) ApptDateTime, a.ApptLenMin Duration, a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type, t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, a.ICD9code ICD9, '??' Diagnosis_free_test from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af) ON (a.IdPatient = p.IdPatient AND a.IdPatientDate = t.IdPatientDate AND CONCAT(a.IdAppt, '0') = c.IdApptType AND a.IdPriCarePhy = af.IdAffil) WHERE a.ApptDate= '2009-03-01'; p.IdPatient, t.IdPatientDate, c.IdApptType, and af.IdAffil are all indexed. Also I selectively took out join parameters until there was nothing but a join on the patient table, and it was still slow, but when I took that out, the query was extremely fast. What might I be doing wrong? Thanks, Jim McNeely The performance problem is with your Cartesian product. I think you meant to write: from Appt_ a LEFT JOIN patient_ p ON a.IdPatient = p.IdPatient LEFT JOIN today_ t ON a.IdPatientDate = t.IdPatientDate LEFT JOIN Copy_ c ON CONCAT(a.IdAppt, '0') = c.IdApptType LEFT JOIN Affil_ af ON a.IdPriCarePhy = af.IdAffil As of 5.0.12, the comma operator for table joins was demoted in the 'order of precedence' for query execution. That means that MySQL became more complaint with the SQL standard but it also means that using a comma-join instead of an explicit ANSI join can result in a Cartesian product more frequently. Try my style and compare how it works. If both styles are similarly slow, collect the EXPLAIN plan for this query and share with the list. Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=j...@newcenturydata.com
Re: Help with slow query
Rhino, Thanks for the help and time! Actually, I thought the same thing, but what's weird is that is the only thing that doesn't slow it down. If I take out all of the join clauses EXCEPT that one the query runs virtually instantaneously. for some reason it will use the index in that case and it works. If I take out everything like this: SELECT a.IdAppt, a.IdPatient, p.NameLast, p.NameFirst, p.NameMI from Appt_ a LEFT JOIN patient_ p ON a.IdPatient = p.IdPatient WHERE a.ApptDate = '2009-03-01'; It is still utterly slow. EXPLAIN looks like this: ++-+---+---+---+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+--+-+--++-+ | 1 | SIMPLE | a | range | apptdate | apptdate | 4 | NULL | 296166 | Using where | | 1 | SIMPLE | p | ALL | NULL | NULL | NULL| NULL | 262465 | | ++-+---+---+---+--+-+--++-+ But, very good try. I thought this might be it as well. Thanks, Jim McNeely On Mar 10, 2011, at 9:05 AM, Rhino wrote: What I'm about to say may be completely out to lunch so don't be afraid to dismiss it. I'm more a DB2 guy than a MySQL guy and I'm getting rusty on both but I've always been struck by how similar the two dtabases are. Therefore, I want to offer an insight on why this query would not perform terribly well in DB2. I simply don't know if it is applicable to MySQL. In DB2, using functions on predicates (conditions in a WHERE clause), prevents DB2 from using an index to satisfy that predicate. (Or at least it used to: I'm not certain if that has been remedied in recent versions of the DB2 optimizer.) Therefore, the CONCAT() function in the line AND CONCAT(a.IdAppt, '0') = c.IdApptType would ensure that no index on the IdAppt column would be used to find the rows of the table that satisfied that condition. My suggestion is that you try rewriting that condition to avoid using CONCAT() - or any other function - and see if that helps the performance of your query. That would require modifying your data to append a zero to the end of the existing date in IdApptType column, which may or may not be a reasonable thing to do. You'll have to decide about that. Again, I could be all wet here so don't have me killed if I'm wrong about this :-) I'm just trying to help ;-) -- Rhino On 2011-03-10 11:38, Jim McNeely wrote: Shawn, Thanks for the great help! It still is not working. I did an EXPLAIN on this query with your amended split out join statements and got this: ++-+---+---+---++-+--++-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+---+---+---++-+--++-+ | 1 | SIMPLE | a | range | apptdate | apptdate | 4 | NULL | 296148 | Using where | | 1 | SIMPLE | p | ALL | NULL | NULL | NULL| NULL | 262462 | | | 1 | SIMPLE | t | ALL | NULL | NULL | NULL| NULL | 311152 | | | 1 | SIMPLE | c | ref | IdApptType| IdApptType | 51 | func | 1 | | | 1 | SIMPLE | af| ALL | NULL | NULL | NULL| NULL | 5680 | | ++-+---+---+---++-+--++-+ What I'm not catching is why it says there is no key it can use for the patient table; here is a portion of the show create: PRIMARY KEY (`zzk`), KEY `IdPatient` (`IdPatient`), KEY `SSN` (`SSN`), KEY `IdLastword` (`IdLastword`), KEY `DOB` (`DateOfBirth`), KEY `NameFirst` (`NameFirst`), KEY `NameLast` (`NameLast`) So, the IdPatient is at least a POSSIBLE key, right? On Mar 9, 2011, at 8:34 PM, Shawn Green (MySQL) wrote: Hi Jim, On 3/9/2011 17:57, Jim McNeely wrote: I am trying to set up an export query which is executing very slowly, and I was hoping I could get some help. Here is the query: SELECT a.IdAppt, a.IdPatient, p.NameLast, p.NameFirst, p.NameMI, a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate, , a.ApptTimeOut) AS CHAR) ApptDateTime, a.ApptLenMin Duration, a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type, t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, a.ICD9code ICD9, '??' Diagnosis_free_test from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af) ON (a.IdPatient = p.IdPatient AND a.IdPatientDate = t.IdPatientDate AND CONCAT(a.IdAppt, '0') = c.IdApptType
Re: Help with slow query
Shawn, This is the first thing that I though as well, but here is a portion from the show create table for patient_: PRIMARY KEY (`zzk`), KEY `IdPatient` (`IdPatient`), KEY `SSN` (`SSN`), KEY `IdLastword` (`IdLastword`), KEY `DOB` (`DateOfBirth`), KEY `NameFirst` (`NameFirst`), KEY `NameLast` (`NameLast`) This extremely simple join is still massively slow. Jim On Mar 10, 2011, at 10:00 AM, Shawn Green (MySQL) wrote: On 3/10/2011 12:32, Jim McNeely wrote: Rhino, Thanks for the help and time! Actually, I thought the same thing, but what's weird is that is the only thing that doesn't slow it down. If I take out all of the join clauses EXCEPT that one the query runs virtually instantaneously. for some reason it will use the index in that case and it works. If I take out everything like this: SELECT a.IdAppt, a.IdPatient, p.NameLast, p.NameFirst, p.NameMI from Appt_ a LEFT JOIN patient_ p ON a.IdPatient = p.IdPatient WHERE a.ApptDate= '2009-03-01'; It is still utterly slow. EXPLAIN looks like this: ++-+---+---+---+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+--+-+--++-+ | 1 | SIMPLE | a | range | apptdate | apptdate | 4 | NULL | 296166 | Using where | | 1 | SIMPLE | p | ALL | NULL | NULL | NULL| NULL | 262465 | | ++-+---+---+---+--+-+--++-+ But, very good try. I thought this might be it as well. ... snip ... According to this report, there are no indexes on the `patient_` table that include the column `IdPatient` as the first column. Fix that and this query should be much faster. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=j...@newcenturydata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Enterprise support now at Oracle?
Wow! We paid for MySQL enterprise plus enterprise support back in the good old days before ORACLE bought MySQL. I just sacrificed the sacred chicken and sprinkled the blood around my computer and went to sign up for support at support.oracle.com. After wading through the crappy Flash interface and telling them my dog's mother's maiden name and all, 3 hours later I got an email saying I was Approved. I feel better. I read a few of the numerous tutorials on HOW TO USE THEIR HELP THING and I still don't understand it. I did a search for MySQL on their site and got NOTHING. Have they dropped all support for MySQL? Is there somewhere else we should go to pay for one on one support for things like my little join query problem? Has anyone else had a good experience with Oracle's MySQL support? Maybe this is a bad dream and I'll wake up soon. Thanks, Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Enterprise support now at Oracle?
Shawn Green works for Oracle and has been very helpful, and I am happy to eat a little bit of shoe leather! Thanks Shawn! Jim On Mar 10, 2011, at 11:11 AM, Jim McNeely wrote: Wow! We paid for MySQL enterprise plus enterprise support back in the good old days before ORACLE bought MySQL. I just sacrificed the sacred chicken and sprinkled the blood around my computer and went to sign up for support at support.oracle.com. After wading through the crappy Flash interface and telling them my dog's mother's maiden name and all, 3 hours later I got an email saying I was Approved. I feel better. I read a few of the numerous tutorials on HOW TO USE THEIR HELP THING and I still don't understand it. I did a search for MySQL on their site and got NOTHING. Have they dropped all support for MySQL? Is there somewhere else we should go to pay for one on one support for things like my little join query problem? Has anyone else had a good experience with Oracle's MySQL support? Maybe this is a bad dream and I'll wake up soon. Thanks, Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=j...@newcenturydata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with slow query
I am trying to set up an export query which is executing very slowly, and I was hoping I could get some help. Here is the query: SELECT a.IdAppt, a.IdPatient, p.NameLast, p.NameFirst, p.NameMI, a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate, , a.ApptTimeOut) AS CHAR) ApptDateTime, a.ApptLenMin Duration, a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type, t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, a.ICD9code ICD9, '??' Diagnosis_free_test from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af) ON (a.IdPatient = p.IdPatient AND a.IdPatientDate = t.IdPatientDate AND CONCAT(a.IdAppt, '0') = c.IdApptType AND a.IdPriCarePhy = af.IdAffil) WHERE a.ApptDate = '2009-03-01'; p.IdPatient, t.IdPatientDate, c.IdApptType, and af.IdAffil are all indexed. Also I selectively took out join parameters until there was nothing but a join on the patient table, and it was still slow, but when I took that out, the query was extremely fast. What might I be doing wrong? Thanks, Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
auto_increment by more than 1
Is there a way to set the auto-increment for a particular table to increase by some number more than one, like maybe 10? Thanks in advance, Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment by more than 1
I have read the manual, and you're right, the auto-increment_increment is a system wide setting. I only want this on one table. I am in this instance creating ID's for a separate system via HL7 for a Filemaker system, and FileMaker is too lame and slow to actually spit out an ID in time for the foreign system to function correctly within its workflow requirements for the end users. So, I am going to offset the ID's so that MySQL issues ID's on the 10's, and FM issues ID's on the 5's. That way, it works similar to the way some people set up replication, but I only need it on this one table, I want the other tables to continue to increment normally. I don't want to do this in another instance of MySQL or another DB because I am otherwise trying to keep it simple. Here is the solution I came up with: CREATE DEFINER=`user`@`%` TRIGGER ``.`p_number_zzk` BEFORE INSERT ON ``.`p_number` FOR EACH ROW BEGIN DECLARE maxy INT; SET maxy = (SELECT ROUND(MAX(zzk),-1) from p_number); IF ! NEW.zzk THEN SET NEW.zzk = (maxy + 10); END IF; SET NEW.IdPatient = CONCAT(P, NEW.zzk); END It's probably ugly, but it works. Any objections to this? The zzk and IdPatient fields have unique validations on them. Thanks, Jim McNeely On Feb 23, 2011, at 12:48 PM, Singer X.J. Wang wrote: Its theoretically possible, but its a hackish solution.. can you explain why you want this? On Wed, Feb 23, 2011 at 15:46, Singer X.J. Wang w...@singerwang.com wrote: Right.. and that's not his question.. On Wed, Feb 23, 2011 at 15:34, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 2/23/2011 12:41, Jim McNeely wrote: Is there a way to set the auto-increment for a particular table to increase by some number more than one, like maybe 10? Thanks in advance, Jim McNeely The manual is your friend. Don't be afraid of it :) http://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html#sysvar_auto_increment_increment -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=w...@singerwang.com -- The best compliment you could give Pythian for our service is a referral.
Re: auto_increment by more than 1
This doesn't work, it just sets the starting number, but it will still increment by one unless you set the auto_increment_increment system variable, but this affects all the tables in the DB and not just the particular table. Thanks, Jim McNeely On Feb 23, 2011, at 10:26 AM, Carsten Pedersen wrote: Den 23-02-2011 18:41, Jim McNeely skrev: Is there a way to set the auto-increment for a particular table to increase by some number more than one, like maybe 10? Thanks in advance, Jim McNeely CREATE TABLE t ( ... ) AUTO_INCREMENT=10; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=j...@newcenturydata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
