Sunday, 15 July 2012

Performance issues of mysql query as compared to oracle query -


I have created a complex scene that gives me output on Oracle 10G dBMS in one second .. but only one view 2 Takes 3 minutes on MYSQL DBMS. I have made indexes on all fields that are included in the visual definition and have extended the query_cache_size but still failed to get the answer in a short time. I have given my query below < Pre> select * result from where beltno & lt; 1000;

And I think:

  create visual results in the form of 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;   

current_posting is defined as:

  select p.belt_no belt_no, ps.ps_name, police_station, pl Pl_name Posting_as, p.st_date 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) ) P_posting group by belt_no); View   

current_rank is defined as:

  p_bank p to p.belt_no select belt_no, r.r_name , Rank R where p.r_id = r.r_id and (p.belt_no, p.st_date) IN (select belt_no, max (st_date) from the p_rank group by belt_no)    

In some versions of MySQL there is a special problem with these and subqueries that you have in this view :

  select p.belt_no belt_no, ps.ps_name police_station, pl.pl_name posting_as, p.st_date 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, ps T_date) IN (select belt_no, max (st_date) from the p_posting group by belt_no)   

Try changing it:

  where it exists (   

Definitely there may be other problems by belt_no by posting a group having belt_no = p.belt_no and p.st_date = max (st_date). At the very least, you can format your questions so that they can be readable and use syntax included in the ANSI standard. Being able to read the questions will be the first step to improving their performance. Then you should use interpret to see if there are query plans.

No comments:

Post a Comment