Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

Optimize slower SQL queries #1394

Closed
ginatrapani opened this Issue · 2 comments

2 participants

@ginatrapani
Owner

A running list of queries that we should speed up.

PostDAO::getPostsToGeoencode
SELECT q.post_id, q.location, q.geo, q.place, q.in_reply_to_post_id,
q.in_retweet_of_post_id, q.is_reply_by_friend, q.is_retweet_by_friend,
q.network FROM (SELECT * FROM tu_posts AS p WHERE (p.geo IS NOT null
OR p.place IS NOT null OR p.location IS NOT null) AND
(p.is_geo_encoded='0' OR p.is_geo_encoded='3') ORDER BY id DESC LIMIT
2000) AS q ORDER BY q.id

@squarepegsys

In this instance I would make an index on tu_posts.is_geo_encoded.

I realize that this query is probably created via ORM, but what may be a better way is to turn this into two queries:

   SELECT id FROM tu_posts AS p WHERE (p.geo IS NOT null
     OR p.place IS NOT null OR p.location IS NOT null) AND
     (p.is_geo_encoded='0' OR p.is_geo_encoded='3') ORDER BY id DESC LIMIT 2000

And then take those ID's and put them in this query:

   SELECT q.post_id, q.location, q.geo, q.place, q.in_reply_to_post_id,
     q.in_retweet_of_post_id, q.is_reply_by_friend, q.is_retweet_by_friend,
     q.network FROM tu_posts q where q.id in (:list of ids:) order by q.id

That will help the query optimizer out a bit. You still should have an index on tu_posts.is_geo_encoded regardless.

@ginatrapani
Owner

Now that the app is aggressively caching dashboard modules and insights in the insights table, this is less of a priority.

@ginatrapani ginatrapani closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.