Xaprb

You probably know that it’s possible to set configuration variables to log queries that don’t use indexes to the slow query log in MySQL. This is a good way to find tables that might need indexes.

But what if the slow query log isn’t enabled and you are using (or consulting on) MySQL 5.0 or earlier, where it can’t be enabled on the fly unless you’re using a patched server such as Percona’s enhanced builds? You can still capture these queries.

The key is knowing what it really means for a query to “not use an index.” There are two conditions that trigger this — not using an index at all, or not using a “good” index. Both of these set a bit. If either bit is set, the query is captured by the filter and logged. Both of these bits also set a corresponding bit in the protocol, so the TCP response to the client actually says “here comes the result of your query, and by the way it didn’t use an index.” This is very useful information.

I’m sure you can see where this is going. Let’s use tcpdump to capture queries, consume the output with mk-query-digest, and filter out all but ones that don’t use an index or use no good index:

$ sudo tcpdump -i lo port 3306 -s 65535  -x -n -q -tttt \
  | mk-query-digest --type tcpdump \
  --filter '($event->{No_index_used} eq "Yes" || $event->{No_good_index_used} eq "Yes")'

If I run a few full table scans now, and then cancel mk-query-digest, I’ll get output like the following (abbreviated for clarity):

#              pct   total     min     max     avg     95%  stddev  median
# Count        100       8
# Exec time    100     5ms   511us   857us   604us   839us   106us   582us
# 100% (8)    No_index_used
select * from t\G

You can see I ran the query 8 times and each time it reported back that it didn’t use an index. This is a dead-easy way to find queries that might not have an index available!

Want to print out tables from those queries? You can do that too. Just add --group-by tables --report-format profile to the command above, and instead of grouping queries together by the query text, it’ll group them by the tables they mention. Then the report will contain one item per table and you’ll just see a summary at the end, like so:

# Rank Query ID           Response time    Calls   R/Call     Item
# ==== ================== ================ ======= ========== ====
#    1 0x                     0.0037 100.0%       8   0.000467 test.t

Aha, looks like test.t is the problem table!

I’m going to present at the EdUIConf conference 2009. This is a conference focused in two directions: Web professionals in higher education, and higher education for web professionals. I believe it’s going to be comparable to, or at least in the same vein as, some of the more popular conferences about user interface design, Web standards, and the like. (It’ll also be much more affordable.)

The speaker lineup boasts a number of heavy hitters. I’m guessing those of you in the Web design profession will know the following name: Molly Holzschlag. If you don’t, crawl out from whatever rock you’ve been hiding under!

My own session at this conference will be on the topic of Web front-end performance. I’ve dubbed it High-Performance Web Interface Design, and I’ll focus on a practical approach to performance. Nothing I’ll show you is revolutionary. The problem is, even though it’s not revolutionary to get good client-side performance, people don’t do it, and users suffer terrible interfaces that don’t download, render, or interact in a snappy fashion.

This will be a relatively fast-paced overview of Web front-end performance, and I’ll show you a demo of a badly performing website (such as the type I see often), make some changes to it, and let you see the performance difference.

If you register, I would appreciate you entering my name in the “how did you hear about this” text box. That will give me a chance to win a laptop. *grin*

Every once in a while, I find myself needing to round a number up to the nearest even power of 10, or round a time interval to the nearest quarter of an hour, or something like that. This is actually quite simple, but for some reason I always find myself confused about how to do it. I have to reason it out all over again, instead of just remembering how to do it. Perhaps writing this blog post will help me remember next time.

The basic idea for rounding to whole multiples is to divide the number, losing precision. Then round, floor, or ceiling the resulting number, and multiply to get back to the original magnitude. For rounding to fractions, reverse the process: multiply, round and divide again.

This actually works for any programming language, not just SQL. But I find myself doing it in SQL most often.

Here’s an example of how to turn a year into a decade:

mysql> SELECT FLOOR(YEAR(NOW()) / 10) * 10 AS decade;
+--------+
| decade |
+--------+
|   2000 | 
+--------+

There are other ways to do this, of course. In this case, since the original year is expressed in decimal notation, and we are rounding down to the nearest power of 10, we could simply take the leftmost three digits and add a zero. But that wouldn’t work if we were trying to “snap” to the nearest five-year interval. The technique I showed above does:

mysql> SELECT FLOOR(YEAR(NOW()) / 5) * 5 AS half_decade;
+-------------+
| half_decade |
+-------------+
|        2005 | 
+-------------+

Let’s suppose we want to take an arbitrary number, and round it to the nearest 1/8th. In this case, we need to divide by 1/8 and then multiply by 1/8 again to get to the nearest fraction, because dividing by eight and multiplying by eight would actually get us to the nearest even power of eight. I’ll just select random numbers between zero and 100 from one of the system tables to illustrate:

mysql> SELECT ROUND((RAND() * 100) / .125) * .125 AS nearest_eighth
     > FROM mysql.help_topic LIMIT 10;
+----------------+
| nearest_eighth |
+----------------+
|         42.875 | 
|         27.875 | 
|         10.875 | 
|         70.375 | 
|         19.625 | 
|         86.875 | 
|         75.750 | 
|         17.750 | 
|         61.500 | 
|         54.500 | 
+----------------+

Of course, 1/8 is an easy number to write out in decimal: .125. It would not be so easy to write out 1/14. So naturally, we can do this by using inverses.

mysql> SELECT ROUND((RAND() * 100) * 14) / 14 AS nearest_14th
     > FROM mysql.help_topic LIMIT 10;
+--------------+
| nearest_14th |
+--------------+
|      88.0714 | 
|      76.7857 | 
|      19.6429 | 
|      67.8571 | 
|      80.2857 | 
|      98.0714 | 
|      49.2857 | 
|      52.2143 | 
|      13.3571 | 
|      10.0000 | 
+--------------+

I hope this was useful to you. I’m betting I’ll be referring back to it myself the next time I need to round a number to the nearest fraction or whole multiple of some other number.