LearnSpeed up your Web App by 1000% with 1 Line of SQL

writes on September 2, 2009

Frameworks have changed the way we develop web applications and many amazing products and services have been delivered as a result.

As developers we take these [frameworks] for granted, they help abstract a lot of the day to day leg work of our applications, and help us develop features more quickly.

This is great in the early days of an application’s lifetime when it’s helpful to be able to rapidly innovate and release new features, but as your user base grows and your application needs to scale, the speed and convenience of these frameworks can hold some hidden pitfalls that we should be aware of.

Recently we came across one such pitfall in the way Tactile CRM is built – we use a custom framework but are slowly migrating our code to the Zend Framework.

We found that one of the search queries used extensively by our application was taking more and more time to execute as new users came on board and our user base grew. The result was a rogue statement/comparison in the way our queries were built with in the application:

$sh->addConstraint(new Constraint($field), 'ILIKE', urldecode($this->_data['q']));

would produce something similar to:

SELECT * FROM table WHERE fieldname ILIKE 'abc%';

(Where the ILIKE comparison does a case insensitive search and the % means do a wildcard search i.e. abcd would match as would ABCD)

Now in general this query isn’t an issue – depending on the database server you use they will handle it in different way. In our case we had a perfect storm. Our development and test environment of our platform are pinned to database versions for testing and deployment reasons and the ILIKE operation on the current live database is very expensive (this has been fixed in a later version of the database server but when testing on our development version the issue wasn’t visible).

After hunting down the issue a change of the line to:

$sh->addConstraint(new Constraint('lower(' . $field . ')', 'LIKE', strtolower(urldecode($this->_data['q']))));

produced SQL such as:

SELECT * FROM table WHERE lower(fieldname) LIKE 'abc%';

and sped up the queries by over a 1000% in worst case scenarios (we lower cased the search string in our code too). Whilst we were at it we updated the framework to only use the LIKE comparison when doing wildcard searches. The new line of code has given us a speed improvement of over 1000% in certain cases for a few hours work.

Certain schools of thought will say that this should have been fixed before the application was released, however I’m a firm believer of the release often methodology of development and learning from one’s mistakes (and those around you). The offending query in itself wasn’t causing major issues, just in certain edge cases and we caught it before it became an issue. It’s given us a reasonable speed boost in the application for not a lot of work.

So the moral of the story is, check the queries your framework is building and how your database plans the query execution once in a while, you might be able to implement some low cost speed boosts for you application.

25 Responses to “Speed up your Web App by 1000% with 1 Line of SQL”

  1. This is fine for english, but i wonder if there are different semantics between ILIKE and lower, in languages that can upper/lower -case letters in various ways, like the turkish language.
    car games

  2. stickgames on September 9, 2009 at 8:20 am said:

    I tried to learn and implement Zend framework one time , but gave it up. To me it’s just too heavy and slow.
    stick games

  3. Link-bait title, but anyways, here’s a more optimized way:

    SELECT TOP 100 fieldname1, fieldname2 FROM table WHERE lower(fieldname) LIKE ‘abc%’;

    1. The TOP 100 in this case limits the result-set. Much faster when dealing with large datasets.

    2. Defining the fieldname’s (i.e.fieldname1, fieldname2 etc.) is better and faster than * (all fields).

  4. If your database doesn’t have functional indexes, this SQL won’t help you. If an index exists on fieldname, then wrapping the conditional column/fieldname in the WHERE clause will invalidate what is stored in the index. So you will have to go the route @Krues8dr suggests or use something else.
    This kind of SQL using LIKE or equivalent doing wildcard searches is always a red flag.
    If you are releasing to production, a good practice is to get all the SQL which is generated and review it like a code review.
    As a DBA I ask myself the same question. If the database is logging slow queries or queries without indexes (MySQL bias here) why can’t it sample the statistics of the predicate/conditional columns in the WHERE clause and add an index on the fly (for the next time the query runs), like Machine Learning layer on top of the optimizer…
    Keeps me in a job until I finish development eh?

  5. Talk about a misleading title. Is your application powered by a single SQL statement?

  6. This is fine for english, but i wonder if there are different semantics between ILIKE and lower, in languages that can upper/lower -case letters in various ways, like the turkish language.

  7. Thanks for your insight. First I though this was bug fixed for Zend framework. I tried to learn and implement Zend framework one time , but gave it up. To me it’s just too heavy and slow.

  8. if you want smiler, you can do it.
    classic games

  9. Great attention grabbing title of the article! Wasn’t exactly what I was expecting but a very interesting read none-the-less. I like scooping up morsels of information like this – adding to the debugging/optimising toolbox.


  10. Gotta love the link bait headline. Awesome. 🙂

  11. I wonder why database server itself doesn’t do this optimization.

  12. This is what I both love and hate about databases 😉

    Love: you can make small changes to queries, indexes, clusters etc, and things improve many orders of magnitude and you’re the performance hero for the day. It’s very satisfying.

    Hate: you can rarely predict completely what those bottlenecks will be until you have lots of real data. This is partly because query engine will make decisions based on its understanding of the statistics of the data (which may not agree with your assumptions), but also because someone might have written a daft query that you didn’t index for.

  13. If you want case insensitivity all the time, it would probably be a lot cheaper to use a case insensitive collation, at least in MySQL

  14. We actually use PostgreSQL and some custom framework code.

    Using EXPLAIN ANALYZE is a really useful tool for optimising queries and checking ended usage.

  15. For what @Steve said above – MySQL doesn’t support functional indexes, so you’ll have to use a different flavor. Along those lines, though, I’d add a column to the table that’s the lowercased-and-plaintext-searchable version of the column in question, properly indexed, if you’re using it that often. You’ll see another big performance boost from not having to do on-the-fly string manipulations.

  16. Hi,

    I was surprised reading your post. I used a tool to compare the performance impact of 2 SQL queries (Explain + SHOW status). There is no difference on the MySQL (5.0.x) query side.

    Did you profile the 2 queries? Where is the difference?

    Maybe it is a MySQL server config or Zend framework issue… 🙁

  17. Just be sure to have a functional index (or something equivalent) on “lower(fieldname)”.

  18. Many of us (including me) started this job by learning things step by step. There is a lot of theory behind these jobs, not only theory of more high-level technologies such as PHP, MySQL but also more low-level skills such as arithmetics and digital systems.
    If you don’t have these study backgrounds, the methodology of development and learning from one’s mistake it’s a very interesting one. And there will always be something new to learn. So thanks for this post!

Leave a Reply

You must be logged in to post a comment.

Learn to code with Treehouse

Start your 7 day free trial today and get access to hundreds of video courses in web development, design and business!

Learn more