The first query is probably running faster because you have a few constants in your search like room=0. I'm sure one of these narrows the search set considerably. Your second query only has one constant, and assuming there is an index on C there must be lots of records matching 87903.
On the first query you may be able to change it a bit to speed it up. Since you have a full text index on it, you may be able to do something like:
match(billing_desc) against('+search -amplifi* -PCR* -western*')
It's not the same as you sample, it doesn't wild card the beginning of the word, only the end. But perhaps this would work for you.??
On Thursday, June 26, 2003, at 05:45 PM, Charles Vos wrote:
Hello All,
Could somebody please enlighten me as to why it takes nearly 2 hours to put 8 rows of data into my table?
The insert commands I use are:
insert into mytab2
select y03m02_Pt.person, y03m02_Acdt.place, y03m02_Acdt.charge_id, y03m02_Acdt.disch,
y03m02_Pt.age, y03m02_Pt.sex, y03m02_Pt.di, y03m02_Pt.dr_id, y03m02_Pt.drty,
y03m02_Acdt.U, y03m02_Acdt.C, y03m02_Acdt.billing_desc
from y03m02_Acdt, y03m02_Acmt, y03m02_Pt
where
match (billing_desc) against ('search')
AND (U not like "2__")
AND room=0
AND (billing_desc not like "%amplifi%")
AND (billing_desc not like "%PCR%")
AND (billing_desc not like "%western%")
AND C="?????"
AND y03m02_Pt.person=y03m02_Acmt.person
AND y03m02_Acmt.place=y03m02_Acdt.place
AND y03m02_Acmt.charge_id=y03m02_Acdt.charge_id;
and
#87903
insert into mytab2
select y03m02_Pt.person, y03m02_Acdt.place, y03m02_Acdt.charge_id, y03m02_Acdt.disch,
y03m02_Pt.age, y03m02_Pt.sex, y03m02_Pt.di, y03m02_Pt.dr_id, y03m02_Pt.drty,
y03m02_Acdt.U, y03m02_Acdt.C, y03m02_Acdt.billing_desc
from y03m02_Acdt, y03m02_Acmt, y03m02_Pt
where
C="87903"
AND y03m02_Pt.person=y03m02_Acmt.person
AND y03m02_Acmt.place=y03m02_Acdt.place
AND y03m02_Acmt.charge_id=y03m02_Acdt.charge_id;
The first one runs in about 12 mins, and inserts close to 50 rows, which is reasonable (especially given that it's searching some 60,000,000 rows to find the data to insert. However, the second command takes between 2 and 3 hours each time.
I'm running MySQL 4.0.13 on a Windows 2000 server with plenty of disk space.
So, is there anything I can do, or should I just go get a pot or two of coffee and start inserting all the data by hand?
-Charlie
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
