CODEWITHSUNDEEP
mysqloracle-databasedatabase-performance
Muhammad Jawad
Muhammad Jawad

Reputation: 101

Performance issues of mysql query as compared to oracle query

I have created a complex view which gives me output within a second on Oracle 10g DBMS.. but the same view takes 2,3 minutes on MYSQL DBMS.. I have created indexes on all the fields which are included in the view definition and also increased the query_cache_size but still failed to get answer in less time. My query is given below

select * from results where beltno<1000;

And my view is: 

create view results as select person_biodata.*,current_rank.*,current_posting.* from person_biodata,current_rank,current_posting where person_biodata.belt_no=current_rank.belt_no and person_biodata.belt_no=current_posting.belt_no ;

The current_posting view is defined as follows: 

select p.belt_no belt_no,ps.ps_name police_station,pl.pl_name posting_as,p.st_date   from p_posting p,post_list pl,police_station ps   where  p.ps_id=ps.ps_id and   p.pl_id=pl.pl_id  and  (p.belt_no,p.st_date) IN(select belt_no,max(st_date) from p_posting group by belt_no);

The current_rank view is defined as follows: 

select p.belt_no belt_no,r.r_name from p_rank p,rank r       where      p.r_id=r.r_id      and      (p.belt_no,p.st_date) IN (select belt_no,max(st_date) from p_rank group by belt_no)

Upvotes: 2

Views: 281

Answers (2)

Raees Khan
Raees Khan

Reputation: 53

Muhammad Jawad it's so simple. you have already created indexes on table that allow database application to find data fast, but if you change/update the (indexes tables) table (e.g: inserst,update,delete) then it take more time that of which have no indexes applied on table because the indexes also need updation so each index will be updated that take too much time. So you should apply indexes on columns or tables that we use it only for search purposes only. hope this will help u. thank u.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270201

Some versions of MySQL have a particular problem with in and subqueries, which you have in this view:

select p.belt_no belt_no,ps.ps_name police_station,pl.pl_name posting_as,p.st_date
from p_posting p,post_list pl,police_station ps
where p.ps_id=ps.ps_id and p.pl_id=pl.pl_id and
      (p.belt_no,p.st_date) IN(select belt_no,max(st_date) from p_posting group by belt_no)

Try changing that to:

where exists (select 1
              from posting
              group by belt_no
              having belt_no = p.belt_no and p.st_date = max(st_date)
             )

There may be other issues, of course. At the very least, you could format your queries so they are readable and use ANSI standard join syntax. Being able to read the queries would be the first step to improving their performance. Then you should use explain in MySQL to see what the query plans are like.

Upvotes: 1

Related Questions