select query taking too long

[Anoop kumar V]

hi All,

I have 2 tables used for reporting and there are no primary keys or
indexes for either. I am trying to run a select query to identify some
rows that need to be removed. But for around 100,000 rows the query is
taking too long. Can somebody please help me in tuning this query?

The 2 tables are RPTACCESS and RECORD_OF_ACCESS. I need to pull out
rows based on these conditions:
1.      The ROA and RPTACCESS tables should have the same value for the
columns LOGINID, APPLICATIONNAME, EID.
2.      The SYSTEM column in the ROA table should have the value
"Remove All"
3.      The ACCOUNTSTATUS column in the ROA table should have the value
"D"

The query works fine - I mean functionally it is correct - when I have
20-30 rows it returns within 5 seconds. But it takes very long on our
QA environment which has around 100,000+ records for both tables.

Here is the query: (I originally had "select count(*)" and later
changed it to "select count(eid)" hoping it would make it faster...but
in vain)


select count(eid) from RPTACCESS
 where upper(applicationname) in
 (
 select upper(r.applicationname) from rptaccess r, record_of_access
roa
 where
  roa.system = 'Remove All'
  and roa.accountstatus = 'D'
  and r.eid = roa.eid
  and r.loginid = roa.loginid
  and upper(r.applicationname) = upper(roa.applicationname)
 )
 and  upper(eid) in
 (
 select upper(r.eid) from rptaccess r, record_of_access roa
 where
  roa.system = 'Remove All'
  and roa.accountstatus = 'D'
  and r.eid = roa.eid
  and r.loginid = roa.loginid
  and upper(r.applicationname) = upper(roa.applicationname)
 )
 and  upper(loginid) in
 (
 select upper(r.loginid) from rptaccess r, record_of_access roa
 where
  roa.system = 'Remove All'
  and roa.accountstatus = 'D'
  and r.eid = roa.eid
  and r.loginid = roa.loginid
  and upper(r.applicationname) = upper(roa.applicationname)
 )
 and  upper(profilecode) in
 (
 select upper(r.profilecode) from rptaccess r, record_of_access roa
 where
  roa.system = 'Remove All'
  and roa.accountstatus = 'D'
  and r.eid = roa.eid
  and r.loginid = roa.loginid
  and upper(r.applicationname) = upper(roa.applicationname)
 );


Please help....

Thanks and best regards,
Anoop

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]