select statement limitations?
Posted by:
Ian Ding
Date: January 13, 2005 07:37PM
Greetings.
I am facing some problems in mysqld-nt.exe which we are running it in Windows 2003 currently.
I have been trying to issue a query which would extract records from 6 tables and some conditions have been implemented. The query looks like this:
"SELECT tmpsum.modmem as member_id, member.member_name,member.nric, member.address, member.postcode,member.town,member.state,member.country, tmpdgbv.gbv as dgbv,member.join_date,tmpgbv.bv, tmpgbv.gbv, tmpgbv.bvmonth, tmpgbv.bvyear,tmpmrank.rank_name,tmpmrank.ha_name,rpttable.section_n,rpttable.bonus, rpttable.tbv,rpttable.total,tmpsum.pr,tmpsum.totbon,tmpsum.psb,tmpsum.gdc,tmpsum.sb,tmpsum.lb,tmpsum.bbc,tmpsum.tc,tmpsum.lsf,tmpsum.gbb,tmpsum.yeb, rpttable.memid,rpttable.stotal,rpttable.rep,cumbv.bv as cbv FROM tmpdgbv, tmpsum,cumbv, member, tmpgbv, rpttable, tmpmrank, tmpgdb WHERE tmpdgbv.member_id = member.member_id AND tmpgbv.member_id = member.member_id AND tmpsum.totbon > '0.00' AND cumbv.member_id = member.member_id AND cumbv.bvmonth = tmpgbv.bvmonth AND cumbv.bvyear = tmpgbv.bvyear AND tmpgdb.member_id = member.member_id AND tmpmrank.member_id = member.member_id AND tmpmrank.bvmonth = tmpdgbv.bvmonth AND tmpmrank.bvyear = tmpdgbv.bvyear AND rpttable.member_id = member.member_id AND tmpgbv.bvmonth = tmpdgbv.bvmonth AND tmpdgbv.bvmonth = '12' AND tmpdgbv.bvyear = '2004' AND tmpgbv.bvyear = tmpdgbv.bvyear AND tmpsum.member_id = member.member_id AND tmpgdb.bvmonth = tmpdgbv.bvmonth AND tmpgdb.bvyear = tmpdgbv.bvyear"
The query is working alright. I have verifed it using a simple members table which consist of only 20 records.
If I plunge this query straight into the main table which has around 1000 members, the mysqld-nt.exe CPU utilization would goes up to 100% and I thought some time has to be allocated for this query to execute.
I've waited for 5 hours now and still the mysqld-nt.exe hogging the CPU at 100%. Is it possible that the processing time can be so long?
It's a Pentium 4 Xeon with 1 GB of RAM, and 2 HDD running in RAID-0.
Any help would be appreciated. Thanks in advance.
Cheers.