Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query giving slow performance.

 
lokesh pattajoshi
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi everybody i am using this(below) SQL query for searching some data but as i have used some joins and there are huge number of records it is giving me performance problem so can any body help me how can i optimize this query..every response is highly appreciated..

if i am searching a then this will be the query

select DISTINCT p.PolicyID "Policy ID", p.CustomerRef "Customer Reference", ct.Cover "Cover", p.Start_Date "Start Date", p.End_Date "End Date",p.No_of_Months "Term", p.DateCancelled "Cancelled Date"
from USER p inner join USER_TYPES ct on p.Cover = ct.CoverID, USER_AGENT sa
where sa.AgentID= p.AgentID and sa.BASE_CODE in ( 'ABC001' ) and ( p.PolicyID RLIKE '[[:<:]]a[[:>:]]' or p.Title RLIKE '[[:<:]]a[[:>:]]' or p.CustomerRef RLIKE '[[:<:]]a[[:>:]]' or p.Start_Date RLIKE '[[:<:]]a[[:>:]]' or p.End_Date RLIKE '[[:<:]]a[[:>:]]' order by 'Policy ID' limit ?,?

Thanks in Advnce
 
Fatih Keles
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess this is a mysql query. A few things at first place:
1) rlike is synonym for regex, if there is a way of replacing it replace it.
2) why do you use regex string search on date columns? (refer to number 1)
3) use explain utility for better understanding and optimization of your query. Yo can refer to mysql manual.

Regards,

Fatih.
 
lokesh pattajoshi
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot Fatih.
 
lokesh pattajoshi
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
2) why do you use regex string search on date columns? (refer to number 1)
thanks fatih for your replay but i did not understood this(above) line ..can i use other things to search instead of string please help.
 
Fatih Keles
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
p.Start_Date RLIKE '[[:<:]]a[[:>:]]'

what is the above regex searching for? what does [[:<:]]a[[:>:]] regex pattern stand for? If you can explain it literally I am sure the equivalent non-regex expressing can be written with either date or string functions.

Regards,

Fatih.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic