• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

SQL Query taking too long

windows 2003, Mysql 5, railo 3.1

I have a database of property listings with the following tables: residential,seo,photolinks. The photolinks table is fairly large (approx 90,000 records) and contains the MLSNum, photolink (hyperlink) and photoOrder ( 0 thru whatever)
I am building a  featured properties list on my homepage and the query runs fast ( 0.031 sec )until I add in the query for the photo, then it takes nearly 10 sec. to return the results. Looking at my query can anyone see the problem or offer advise on how this can be rewritten? any help appreciated!


SELECT seo.MLSNum,seo.StrNumber,seo.StrName,seo.Cty,residential.MLSNum,residential.publiccomments,residential.City,residential.State,residential.Price,residential.StreetNumber,residential.Bedrooms,residential.StreetName,photolinks.photolink,photolinks.photoOrder from navica.residential,navica.seo, navica.photolinks WHERE residential.mlsNum=seo.MLSNum AND residential.MLSNum = photolinks.MLSNum AND photoOrder=0 AND ListingOffice = 115
ORDER BY RAND()limit 5

  • 2
1 Solution
Try this

SELECT seo.MLSNum,seo.StrNumber,seo.StrName,seo.Cty,residential.MLSNum,residential.publiccomments,residential.City,residential.State,residential.Price,residential.StreetNumber,residential.Bedrooms,residential.StreetName,Q1.photolink,Q1.photoOrder
from residential Inner Join seo ON residential.mlsNum=seo.MLSNum
Inner Join
(Select photolinks.MLSNum,photolinks.photolink,photolinks.photoOrder FROM
photolinks INNER JOIN residential ON residential.MLSNum = photolinks.MLSNum) As Q1
ON Q1.MLSNum=residential.mlsNum
WHERE  photoOrder=0 AND ListingOffice = 115
ORDER BY RAND()limit 5
Bang-O-MaticAuthor Commented:
Amesome! took just under 1 second for the query now... thanks so much for your help!
Glad to be of assistance. Happy coding

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now