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