issue_comments: 399171239

This data as json

html_url issue_url id node_id user created_at updated_at author_association body reactions issue performed_via_github_app
https://github.com/simonw/datasette/issues/321#issuecomment-399171239 https://api.github.com/repos/simonw/datasette/issues/321 399171239 MDEyOklzc3VlQ29tbWVudDM5OTE3MTIzOQ== 9599 2018-06-21T16:51:08Z 2018-06-21T16:51:29Z OWNER

I may have misunderstood your problem here.

I understood that the problem is that when using the "%" || :text || "%" construct the first hit to that page (with an empty string for :text) results in a where neighborhood like "%%" query which is slow because it matches every row in the database.

My fix was to add this to the where clause:

where :text != '' and ...

Which means that when you first load the page the where fails to match any rows and you get no results (and hopefully instant loading times assuming SQLite is smart enough to optimize this away). That's why you don't see any rows returned on this page: https://latest.datasette.io/fixtures-cafd088?sql=select+neighborhood%2C+facet_cities.name%2C+state%0D%0Afrom+facetable%0D%0A++++join+facet_cities+on+facetable.city_id+%3D+facet_cities.id%0D%0Awhere+%3Atext+%21%3D+%22%22+and+neighborhood+like+%27%25%27+%7C%7C+%3Atext+%7C%7C+%27%25%27%0D%0Aorder+by+neighborhood%3B

 
{
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
 334190959