SQL Query taking too long

Posted on 2011-05-06
Last Modified: 2012-05-11
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

Question by:Bang-O-Matic
    LVL 16

    Accepted 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

    Author Closing Comment

    Amesome! took just under 1 second for the query now... thanks so much for your help!
    LVL 16

    Expert Comment

    Glad to be of assistance. Happy coding

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now