My inefficient SQL for custom searches..

  • Resolved Zaphod

    (@zaphod)


    15 years, 1 month ago

    Just looking for some advice here. Working on (practically complete) a car comparison site built using WP.

    I’ve written my own search code such that all data stored in meta_data is easily searchable. The thing is, my methods, while working 100% are not at all efficient I think and I’m looking for some pointers / suggestions on how to improve.

    Currently there are over 30,000 records (cars) on system. My approach is to :

    1. Run the full query with all parameters to get a full result count. Use this number to setup paging of results etc.
    2. Run the same query again but with limit x,10 where x is the page no multiplied by ten

    That’s two fairly hefty SQL queries running to basically do one thing.

    Here’s a sample of the SQL for an indication. As I’m querying meta_data there’s a load of joins which obviously doesn’t help.

    SELECT wposts.* FROM cc_posts wposts
LEFT JOIN cc_postmeta cc_make ON (wposts.ID = cc_make.post_id)

LEFT JOIN cc_postmeta cc_model ON (wposts.ID = cc_model.post_id)

LEFT JOIN cc_postmeta cc_year ON (wposts.ID = cc_year.post_id)

LEFT JOIN cc_postmeta cc_price ON (wposts.ID = cc_price.post_id)

WHERE 1=1 AND (cc_make.meta_key = 'make' AND cc_make.meta_value = 'Ford')
AND (cc_model.meta_key = 'model' AND cc_model.meta_value ='Focus')
AND (cc_year.meta_key = 'year' AND cc_year.meta_value >= 2004)
AND (cc_year.meta_key = 'year' AND cc_year.meta_value <= 2008)
AND (cc_price.meta_key = 'price' AND cc_price.meta_value >= 4000)
AND (cc_price.meta_key = 'price' AND cc_price.meta_value <= 8000)
AND wposts.post_status = 'publish' AND wposts.post_type = 'post' AND wposts.post_date < NOW()
order by (cc_price.meta_value+0) asc

    The second query is exactly the same but with the limit of e.g.

    limit 130,10

    (if page is 14: page 1 gets 0 -10, page 2 11 – 20 etc).

Viewing 8 replies - 16 through 23 (of 23 total)

  • I’d personally expect the sub-query to be slower, it is my impression(from reading and little experience) that joins are generally faster for most cases ..

    There’s a huge wealth of examples and comparison queries here.
    artfulsoftware.com/infotree/queries.php?&bw=1280

    You’ll also notice some Subquery vs Join comparisons amongst them..

    vtxyzzy

    (@vtxyzzy)

    I think in general, you are correct. But, as I said, in some cases with Sybase and larger numbers of joins, the optimizer would not choose the best sequence. In particular, with 5 or more joins, it would generally not optimize at all, but just execute in the order coded. I don’t know anything about how the MySQL optimizer compares, but it might be worth a try.

    Thread Starter Zaphod

    (@zaphod)

    Thanks folks.

    I’ve tried a radically different approach as the time for page loads was killing the site.

    Basically I pulled in the normal wp posts type data into the posts table, and pulled in all other data into a custom table I added such that every paramater outside the general post [which just has Make Model Year as post_name] is contained in a table I’ve called cc_post_detail.

    That way I’ve only one join and the searches are now lightning fast.

    SERIOUS ROADBLOCK though: Can’t see how I pull the data from the join onto the actual page.

    Maybe I’m just tired and I’ll get it shortly ??

    Thread Starter Zaphod

    (@zaphod)

    Sample query [just fyi]

    SELECT wposts.* FROM cc_posts wposts
LEFT JOIN cc_posts_detail cc_detail ON (wposts.ID = cc_detail.post_id)

WHERE 1=1 AND (cc_detail.make = 'Ford')
AND (cc_detail.model ='Fiesta')
AND (cc_detail.year >= 2005)
AND (cc_detail.year <= 2008)
AND (cc_detail.price >= 7000)
AND (cc_detail.price <= 10000)
AND (cc_detail.location = 'Dublin')
AND wposts.post_status = 'publish' AND wposts.post_type = 'post' AND wposts.post_date < NOW()
order by (cc_detail.price+0)
asc limit 0,10
    Thread Starter Zaphod

    (@zaphod)

    The problem is that I’d been using WP’s get_post_custom_values to pull the meta data with the previous query.

    How do I get at my new fields from the cc_posts_detail table join above now?!

    ??

    vtxyzzy

    (@vtxyzzy)

    I think if you include the cc_detail fields in the query, that you can retrieve the fields by referencing them as part of the $post object, e.g. $model = $post->model.

    SELECT wposts.*, cc_detail.* FROM cc_posts wposts
LEFT JOIN cc_posts_detail cc_detail ON (wposts.ID = cc_detail.post_id)

WHERE 1=1 AND (cc_detail.make = 'Ford')
AND (cc_detail.model ='Fiesta')
AND (cc_detail.year >= 2005)
AND (cc_detail.year <= 2008)
AND (cc_detail.price >= 7000)
AND (cc_detail.price <= 10000)
AND (cc_detail.location = 'Dublin')
AND wposts.post_status = 'publish' AND wposts.post_type = 'post' AND wposts.post_date < NOW()
order by (cc_detail.price+0)
asc limit 0,10
    Thread Starter Zaphod

    (@zaphod)

    vtxyzzy & t31os_,

    You have both gone well beyond the call of duty here. Thanks so much – I now have exactly what I need. V, that last tip was bang on.

    You guys should get an Amazon wishlist going, people could donate a few quid to it!

    Many many thanks.

    Happy to hear you were able to speed up the query. I nearly suggested considering using a seperate table but figured you had already considered the option and decided against it, i half wish i had said this now (would of saved you some time)..

    vtxyzzy i’ve not worked with other DBs myself and i’ve not much idea about optimisers i was simpy saying what my impressions are, i’m sure it can’t hurt to switch around the queries anyway, it’s only a few seconds testing to see the differences… ??

    Great to see a solution was found. ??

Viewing 8 replies - 16 through 23 (of 23 total)
  • The topic ‘My inefficient SQL for custom searches..’ is closed to new replies.