I"m a little confused by the query you posted.. it looks like it would
work, although with many redundant subqueries to get there. From your
requirement, I don't understand why you needs to wrap it in a self-
referencing subquery..
Why does this not give you the same logical value?
select count(r2.eid)
from rptaccess r2, record_of_access roa
where
roa.system = 'Remove All'
and roa.accountstatus = 'D'
and r2.eid = roa.eid
and r2.loginid = roa.loginid
and upper(r2.applicationname) = upper(roa.applicationname)
Having said that: if your original query takes 5 seconds in your 30
record QA environment, adding a few indexes as recommended will take it
down to the order of maybe a few hundred milliseconds.
Adding indexes to production, while not to be taken lightly, is not
something to be shy away from. for records on the order of a few
hundred K, it would be a matter of a minute or so and the odds of the
action breaking anything are very nearly nil.
On Wed, 2005-19-10 at 19:39 -0400, Anoop kumar V wrote:
> Unfortunately, I cannot create indexes for these tables. These are on
> production and I cannot modify the tables in anyway.
> Also, none of the columns are unique in nature - they just serve as a
> reporting store.
>
> Is there anyway that I can tune the select query itself and hope some
> performance enhancement?? (Maybe I am scanning the tables once too
> many.. or........)
>
> Need help please.
>
> Thanks,
> Anoop
>
> On 10/19/05, Jasper Bryant-Greene <[EMAIL PROTECTED]> wrote:
> > On Wed, 2005-10-19 at 17:39 -0400, Anoop kumar V wrote:
> > > 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?
> >
> > You have answered your own question! The problem is that there are no
> > indexes on your tables. Indexes are designed to speed SELECT queries up,
> > so not having indexes will cause your SELECT queries to slow down.
> >
> > Define indexes on the columns you are querying against; I'd start with
> > accountstatus, eid, loginid, applicationname, profilecode...
> >
> > >From the names I'd suggest some of those might be UNIQUE indexes or
> > PRIMARY KEYs.
> >
> > --
> > Jasper Bryant-Greene
> > General Manager
> > Album Limited
> >
> > e: [EMAIL PROTECTED]
> > w: http://www.album.co.nz/
> > p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303
> > a: PO Box 579, Christchurch 8015, New Zealand
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
>
> --
> 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]
