Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    8

    Red face Unanswered: Optimizing a Query

    I have searched the forums, but I cannot find an answer to the problem I am facing. If I missed a post that has already been solved, please point me in that direction.

    I have the following query that takes up to 10 seconds to return results (with only 30,000 records):

    Code:
    	$sql = "SELECT DISTINCT 
        mls_listings.c1, 
        mls_listings_images.c1, 
        mls_listings.c2, 
        mls_listings.c3, 
        mls_listings_images.c3 as image,
        mls_listings.c4, 
        mls_listings.c5, 
        mls_listings.c6, 
        mls_listings.c7, 
        mls_listings.c8, 
        mls_listings.c9 
    FROM 
        mls_listings, 
        mls_listings_images 
    WHERE 
        (SELECT DISTINCT mls_listings.c1 = mls_listings_images.c1) AND 
        (mls_listings_images.c1 LIKE '%{$_GET['uniqueid']}%') AND 
        (((mls_listings.c3 IN (
            '{$_GET['redondobeach']}',
            '{$_GET['santamonica']}',
            '{$_GET['specificcity']}',
            '{$_GET['hermosabeach']}',
            '{$_GET['elsegundo']}',
            '{$_GET['manhattanbeach']}',
            '{$_GET['marinadelrey']}',
            '{$_GET['palosverdes']}',
            '{$_GET['playadelrey']}',
            '{$_GET['playavista']}',
            '{$_GET['torrance']}',
            '{$_GET['venice']}',
            '{$_GET['westchester']}',
    		'{$_GET['losangeles']}')
        ) OR 
        (mls_listings.c7 LIKE '{$_GET['zipwestla']}'))
    	OR
    	(mls_listings.c1 LIKE '{$_GET['uniqueid']}')	
    	)
    	AND
        (mls_listings.c5 >= '{$_GET['beds']}') AND 
        (mls_listings.c6 >= '{$_GET['baths']}') AND 
        (
            mls_listings.c4 >= '{$_GET['minprice']}' AND 
            mls_listings.c4 <= '{$_GET['maxprice']}'
    ) 
    GROUP BY mls_listings_images.c1
    ORDER BY mls_listings.c4";
    Does anyone have any tips on how I can better optimize this query?

    Thank you!

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by mmcknight View Post
    Does anyone have any tips on how I can better optimize this query?
    It's very difficult to make sense out of your SQL so I'll just make a few points:
    • using a GROUP BY clause when it's not required
    • putting wilcards around your unique id looks very wrong - c1 LIKE '%{$_GET['uniqueid']}%') - this will result in a table scan each time and ids like 99 will match against ids 99, 199, 299, 991 etc.
    • the code after the IN clause is a mess - what on earth are you trying to do there?
    • the first part of your where clause has (SELECT DISTINCT mls_listings.c1 = mls_listings_images.c1) which seems nonsensical.
    • you could work on your field names (c1 ... c9) to give them a little more meaning.
    • what happens if PHP doesn't have a value for any of these parameters ie ehat happens if beds is left blank in (mls_listings.c5 >= '{$_GET['beds']}')

  3. #3
    Join Date
    Mar 2010
    Posts
    8
    Thanks Mike for your help.

    I'll give you a breakdown of what each field contains:

    mls_listings_images TABLE:
    c1 = unique id
    c2 = Street Address
    c3 = City
    c4 = List Price
    c5 = Bedrooms
    c6 = Baths
    c7 = Zipcode
    c8 = State
    c9 = Description of Property
    c10 = Lease Option (ended up not using it yet though)
    c11 = LotSize Square Feet
    c12 = Garage
    c13 = View
    c14 = Pool
    c15 = Status Change (not used yet)
    c16 = not used
    c17 = Building Size

    mls_listings_images:
    c1 = unique id
    c2 = unused
    c3 = image location

    Here are my answers to you questions / suggestions:

    using a GROUP BY clause when it's not required
    - I couldn't find another way to get the listings to tie into the listings images to be displayed once. Before using GROUP BY the listings were being displayed twice or more. For instance, if one listing had three images, three duplicate listings would be displayed rather than one listing with three images.

    putting wilcards around your unique id looks very wrong - c1 LIKE '%{$_GET['uniqueid']}%') - this will result in a table scan each time and ids like 99 will match against ids 99, 199, 299, 991 etc.
    - When I remove the wildcards, I get no results and nothing seems to match up for the query.

    the code after the IN clause is a mess - what on earth are you trying to do there?
    The IN clause was meant so that is a user checked one of the cities listed (in the form) that query would get activated. One of the cities contains zip codes that get passed (zipwestla) because there are multiple cities that are going to be queried rather than one (like santamonica). Also unique id is a field that the user can type in the unique key rather than going through the search to which it will display that result. Same with bed and baths. How would you do this differently?

    the first part of your where clause has (SELECT DISTINCT mls_listings.c1 = mls_listings_images.c1) which seems nonsensical.

    I used this because DISTINCT was supposed to only return one result and not duplicates as in the case of two images...

    you could work on your field names (c1 ... c9) to give them a little more meaning.

    I will do that, thank you.

    what happens if PHP doesn't have a value for any of these parameters ie ehat happens if beds is left blank in (mls_listings.c5 >= '{$_GET['beds']}')
    - Everything seems to work just fine when it's blank.

    ----

    I answered your questions and don't take it as if I'm not up for help, since that's the whole reason I'm here. I'm just explaining why I did what I did and obviously it's at the top of the learning curve at the moment.

    Thanks and I look forward to hearing your suggestions.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I was trying to rewrite your code but more and more issues kept cropping up:
    • if a property has no image then it will never show up
    • at the moment a malicious user could attack your data through your PHP form as you don't check what's in any field before adding the contents of any variable to your SQL - you need to read up on PHP and SQL injection attacks.
    • there's no way of knowing which image to use when showing a property, a user could be looking for a $10m beach side property but could just be shown an image of an toilet beside the pool rather than the main picture of the house as there's no way for the program to tell the difference.

    I'm going to be honest here and simply point out that it might be better to just hire someone to recreate your table with proper field names, to perhaps have the main image for any property in the listings table which will avoid the join all together, to make your PHP code more robust and then to rewrite your SQL entirely. I don't think this will be a very expensive thing to do (somewhere between 2 and 5 days is my guess) and if you have 30k properties on your portfolio isn't it worth having it all done properly?

    but there may be someone on the forum who can do something with your SQL ...

  5. #5
    Join Date
    Mar 2010
    Posts
    8
    Thanks again for your insight Mike.

    ---

    if a property has no image then it will never show up
    - The properties are showing up and I have an image signifying there isn't an image associated with the listing in the retrieval.

    at the moment a malicious user could attack your data through your PHP form as you don't check what's in any field before adding the contents of any variable to your SQL - you need to read up on PHP and SQL injection attacks.
    - I'll read up on this.

    there's no way of knowing which image to use when showing a property, a user could be looking for a $10m beach side property but could just be shown an image of an toilet beside the pool rather than the main picture of the house as there's no way for the program to tell the difference.
    - You're right and that's is another issue I'm trying to sort out.

    ---

    I would hire someone, but then it would defeat the whole purpose of me learning how to do this myself. Bugs and all, it's something I enjoy.

    Thanks for your feedback on this Mike.

    As for optimizing the speed of the query did you recommend anything you haven't already wrote about?

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by mmcknight View Post
    As for optimizing the speed of the query did you recommend anything you haven't already wrote about?
    I'd do the following:
    • store the main image in the listings table
    • then remove the join to the images table
    • then remove the group by clause
    • have a PHP function to clean the form parameters ie
      Code:
      $id = clean_parameter( "uniqueid" );
    • then alter the SQL comparison on uniqueid to ( '$id' = '' or c1 = '$id' )
    • put a limit on the number of rows returned ie limit 20
    • alter the field names from c1..c9 (actually I'd do this first)

  7. #7
    Join Date
    Mar 2010
    Posts
    8
    Mike thanks again for the help - night and day difference!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •