Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

What kind of queries are bad for MySQL?

DZone's Guide to

What kind of queries are bad for MySQL?

· Performance Zone ·
Free Resource

Sensu is an open source monitoring event pipeline. Try it today.

This post comes from Vadim Tkachenko at the MySQL Performance Blog.


In writing a recommendation for our Web development team on how to use MySQL, I came up with the following list, which I want to share: What kind of queries are bad for MySQL?

  • Any query is bad. Send a query only if you must. (Hint: use caching like memcache or redis)
  • Queries that examine many rows are bad. Try instead to use…
    SELECT col1 FROM table1 WHERE primary_key_column=SOMETHING
      Or at least
    secondary_key_column=SOMETHING
      If it is still not possible, try to make the query examine the least amount of rows possible (zero is ideal, as we come to the first case here)
  • Queries with JOINS are bad. Try to denormalize the table to avoid JOINS. Example: original query
    SELECT t2.value FROM t2 JOIN t1 ON (t1.id=t2.tid) WHERE t1.orderdate=NOW()
    This can be denormalized by copying the column orderdate from table t1 to table t2, so the query will look like:
    SELECT t2.value FROM t2 WHERE t2.orderdate=NOW()
      I  know that fans of 3NF will hit me hard for this, but this is needed for performance
  • Aggregation queries are bad. If you have SUM, AVG, MIN, MAX over many rows, try to pre-calculate these value in roll-up tables, or at least minimize amount of rows to handle. You can have daily, weekly, monthly, you name it, summary tables. Actually INSERT .. ON DUPLICATE KEY UPDATE ... is very useful here.

Do you have more examples?

Sensu: workflow automation for monitoring. Learn more—download the whitepaper.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}