Tag: mysql

MySQL: Composite indexes in 5 minutes

By Ashish Datta | 0 Comments

Despite how important they are, MySQL indexes are a bit of a dark art. Sure everyone knows indexes are important but details on how they’re implemented and when they’ll be used are hard to come by. Beyond regular indexes, MySQL’s composite indexes are especially opaque in regards to how and when they’ll be used. As the name suggests composite indexes are an index constructed across two columns versus a regular index on a single column. So when might a composite index come in handy? Let’s take a look!

We’ll look at a table “client_order” that captures some fictional orders from our fictional clients:

CREATE TABLE client_order (
id int(11) NOT NULL AUTO_INCREMENT,
client_id int(11) DEFAULT NULL,
total decimal(6, 2) NOT NULL,
created_at datetime DEFAULT NULL,
PRIMARY KEY (`id`)
);
view raw client_order.sql hosted with ❤ by GitHub

And we’ll fill it up with 5 million fictional orders with dates spanning the last 10 years. You can grab the data from https://setfive-misc.s3.amazonaws.com/client_order.sql.gz if you want to follow along locally.

To get started, let’s figure out the total amount spent for a couple of clients:
https://gist.github.com/adatta02/f675b2c7b0659ab960d791b44ee02861

~1.5 seconds to calculate the sums and according to the EXPLAIN MySQL had to use a temporary table and a filesort. Will an index help here? Lets add one and find out.

mysql> ALTER TABLE client_order ADD INDEX client_idx (client_id);
Query OK, 0 rows affected (15.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT SUM(total), client_id FROM client_order WHERE client_id IN (111929, 372530, 180565) GROUP BY client_id;
+------------+-----------+
| SUM(total) | client_id |
+------------+-----------+
| 23342.81 | 111929 |
| 23182.22 | 180565 |
| 23186.94 | 372530 |
+------------+-----------+
3 rows in set (0.02 sec)
mysql> EXPLAIN SELECT SUM(total), client_id FROM client_order WHERE client_id IN (111929, 372530, 180565) GROUP BY client_id;
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | client_order | NULL | range | client_idx | client_idx | 5 | NULL | 424 | 100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
view raw indexes.sql hosted with ❤ by GitHub

~0.2 seconds and looking at the EXPLAIN we’ve cut down the number of rows MySQL has to look at to 424, much better. OK great, but now what if we’re only interested in looking at data from Christmas Eve in 2016?

mysql> SELECT SUM(total), client_id FROM client_order WHERE client_id IN (111929, 372530, 180565) AND created_at >= "2016-12-24 00:00:00" AND created_at <= "2016-12-24 24:59:59" GROUP BY client_id;
+------------+-----------+
| SUM(total) | client_id |
+------------+-----------+
| 2280.06 | 111929 |
| 1403.08 | 180565 |
| 2841.30 | 372530 |
+------------+-----------+
3 rows in set, 1 warning (1.08 sec)
mysql> EXPLAIN SELECT SUM(total), client_id FROM client_order WHERE client_id IN (111929, 372530, 180565) AND created_at >= "2016-12-24 00:00:00" AND created_at <= "2016-12-24 24:59:59" GROUP BY client_id;
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+--------+----------+------------------------------------+
| 1 | SIMPLE | client_order | NULL | range | client_idx | client_idx | 5 | NULL | 281308 | 11.11 | Using index condition; Using where |
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+--------+----------+------------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT COUNT(*) FROM client_order WHERE client_id IN (111929, 372530, 180565) AND created_at >= "2016-12-24 00:00:00" AND created_at <= "2016-12-31 24:59:59";
+----------+
| COUNT(*) |
+----------+
| 335 |
+----------+
1 row in set, 1 warning (1.03 sec)
view raw date_noindex.sql hosted with ❤ by GitHub

(Note: Details on why we’re querying with full timestamps below)

As you can see, MySQL is still using the client_id index but we’re left still scanning 281,308 rows even though only 335 are actually relevant to us. So how do we fix this? Enter, the composite index! Let’s add one on (client_id, created_at) and see if it helps our query:

mysql> ALTER TABLE client_order ADD INDEX client_id_created_at_idx (client_id, created_at);
Query OK, 0 rows affected (20.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT SUM(total), client_id FROM client_order WHERE client_id IN (111929, 372530, 180565) AND created_at >= "2016-12-24 00:00:00" AND created_at <= "2016-12-24 24:59:59" GROUP BY client_id;
+----+-------------+--------------+------------+-------+-------------------------------------+--------------------------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+-------------------------------------+--------------------------+---------+------+-------+----------+-----------------------+
| 1 | SIMPLE | client_order | NULL | range | client_idx,client_id_created_at_idx | client_id_created_at_idx | 11 | NULL | 11100 | 100.00 | Using index condition |
+----+-------------+--------------+------------+-------+-------------------------------------+--------------------------+---------+------+-------+----------+-----------------------+
1 row in set, 3 warnings (0.00 sec)
view raw clientid_createdat.sql hosted with ❤ by GitHub

It helps but we’re clearly still looking a lot more rows than we need. So what gives? It turns out the order of the composite index is actually critically important since that dictates how MySQL assembles the b-tree for the index. Let’s flip the order of our index and try again:

mysql> ALTER TABLE client_order DROP INDEX client_id_created_at_idx;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE client_order ADD INDEX created_at_client_id_idx (created_at, client_id);
Query OK, 0 rows affected (20.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT SUM(total), client_id FROM client_order WHERE client_id IN (111929, 372530, 180565) AND created_at >= "2016-12-24 00:00:00" AND created_at < "2016-12-25 00:00:00" GROUP BY client_id;
+----+-------------+--------------+------------+-------+----------------------------------+-----------------------+---------+------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+----------------------------------+-----------------------+---------+------+------+----------+--------------------------------------------------------+
| 1 | SIMPLE | client_order | NULL | range | client_idx,created_at_client_idx | created_at_client_idx | 11 | NULL | 1360 | 5.64 | Using index condition; Using temporary; Using filesort |
+----+-------------+--------------+------------+-------+----------------------------------+-----------------------+---------+------+------+----------+--------------------------------------------------------+
view raw created_atclientid.sql hosted with ❤ by GitHub

And there you go! MySQL only has to look at 1360 rows as expected.

So what’s up with having to query with the full timestamps vs. just using DATE(created_at)? It turns out MySQL can’t use datetime indexes when you apply functions to the column you’re querying on. And beyond that, even certain ranges cause MySQL to not select indexes that would work fine:

mysql> EXPLAIN SELECT SUM(total), client_id FROM client_order WHERE client_id IN (111929, 372530, 180565) AND created_at >= "2016-12-24 00:00:00" AND created_at <= "2016-12-25 24:59:59" GROUP BY client_id;
+----+-------------+--------------+------------+-------+----------------------------------+------------+---------+------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+----------------------------------+------------+---------+------+--------+----------+------------------------------------+
| 1 | SIMPLE | client_order | NULL | range | client_idx,created_at_client_idx | client_idx | 5 | NULL | 281308 | 15.22 | Using index condition; Using where |
+----+-------------+--------------+------------+-------+----------------------------------+------------+---------+------+--------+----------+------------------------------------+
mysql> EXPLAIN SELECT SUM(total), client_id FROM client_order WHERE client_id IN (111929, 372530, 180565) AND created_at >= "2016-01-01 00:00:00" AND created_at < "2016-01-10 00:00:00" GROUP BY client_id;
+----+-------------+--------------+------------+-------+----------------------------------+-----------------------+---------+------+-------+----------+-------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+----------------------------------+-----------------------+---------+------+-------+----------+-------------------------------------------------------------------+
| 1 | SIMPLE | client_order | NULL | range | client_idx,created_at_client_idx | created_at_client_idx | 11 | NULL | 22828 | 5.64 | Using index condition; Using MRR; Using temporary; Using filesort |
+----+-------------+--------------+------------+-------+----------------------------------+-----------------------+---------+------+-------+----------+-------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
view raw indexfail.sql hosted with ❤ by GitHub

Which then leads to the unintuitive conclusion that if you actually needed to implement any sort of aggregation by day you’d be better off adding a “date” column calculated from the “created_at” and indexing on that:

mysql> ALTER TABLE client_order ADD COLUMN date date default null;
Query OK, 0 rows affected (46.72 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> UPDATE client_order SET `date` = STR_TO_DATE(DATE(created_at), "%Y-%m-%d");
Query OK, 5000000 rows affected (9 min 52.98 sec)
Rows matched: 5000000 Changed: 5000000 Warnings: 0
mysql> ALTER TABLE client_order ADD INDEX date_client_idx (date, client_id);
Query OK, 0 rows affected (29.43 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT SUM(total), client_id FROM client_order WHERE client_id IN (111929, 372530, 180565) AND `date` = "2016-12-24" GROUP BY client_id;
+----+-------------+--------------+------------+-------+--------------------------------------------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+--------------------------------------------------+-----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | client_order | NULL | range | client_idx,created_at_client_idx,date_client_idx | date_client_idx | 9 | NULL | 44 | 100.00 | Using index condition |
+----+-------------+--------------+------------+-------+--------------------------------------------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
view raw datecol.sql hosted with ❤ by GitHub

Anyway, as always comments and feedback welcome!

SQL: 5 Tips and tricks to impress your DBA friends

By Ashish Datta | 0 Comments

I was poking around the ThoughtBot blog a couple of days ago and ran across a post titled Refactoring Ruby Iteration Patterns to the Database. At a high level, the post was summarizing how you can take an ActiveRecord aggregation (a sum in this case) and run it in directly in your RDMS with SQL. Not really rocket science, but it was a keen reminder of how ORMs often mask over much of the power of “regular” SQL. This isn’t a specific criticism of ActiveRecord, it’s an issue with every ORM from Doctrine to Hybernate.

We’ve actually been writing some straight SQL lately, mostly for analytics work, so I had the team shoot over their favorite “maybe hidden” SQL feature. Since life is better with examples, the sample use cases and queries are written against a schema describing the “items” found on a “receipt” which are optionally related to a “category”. The SQL to create the schema is:

CREATE TABLE IF NOT EXISTS `receipt` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`total` decimal(15,2) DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
INSERT INTO `receipt` (id) VALUES (7006),(3493);
CREATE TABLE IF NOT EXISTS `item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) DEFAULT NULL,
`receipt_id` int(11) DEFAULT NULL,
`total` decimal(15,2) DEFAULT '0.00',
`quadrant` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=40779 ;
--
-- Dumping data for table `item`
--
INSERT INTO `item` (`id`, `category_id`, `receipt_id`, `total`, `quadrant`) VALUES
(1925, 390, 1712, 1.00, 'WE'),
(11834, 263, 3168, 8.40, 'AX'),
(18012, 263, 3493, 7.99, 'AX'),
(25128, 390, 5681, 14.99, 'WE'),
(26898, 267, 4214, 9.99, 'AX'),
(27486, 267, 7345, 1.49, 'AX'),
(33331, 263, 7006, 29.94, 'BT'),
(34947, 390, 9053, 20.01, 'BT'),
(39309, 390, 13544, 14.09, 'BT'),
(40778, 267, 14153, 5.94, 'BT');
view raw gistfile1.sql hosted with ❤ by GitHub

Note: SQLFiddle is unfortunately down right now or I’d add this as a fiddle. Anyway, if you have the schema setup feel free to run the queries as you go down the list.

Order rows by fixed ordering of a column

At some point, you might find yourself needing to sort a list of rows by a column in an arbitrarily enforced order. For example, say on our item table, you needed to sort the rows by the “quadrant” column such that WE was first, followed by AX, and finally BT.

Turns out, it’s possible to specify an arbitrary ordering using the ORDER BY FIELD statement:

SELECT * FROM item WHERE 1 ORDER BY FIELD (quadrant, "WE", "AX", "BT")
view raw gistfile1.sql hosted with ❤ by GitHub

Check that a LEFT JOIN relation exists

If you’re only running JOINs on columns with foreign key relations this isn’t an issue, but what happens if you need to run a JOIN where a FK doesn’t necessarily exist? In our example, lets say you wanted to select only the items which had a corresponding row in the “receipt” table.

The most straightforward way to accomplish this is generally to check that the JOIN’ed column on the related table isn’t NULL:

SELECT * FROM item
LEFT JOIN receipt ON receipt.id = item.receipt_id
WHERE receipt.id IS NOT NULL
view raw gistfile1.sql hosted with ❤ by GitHub

Assign an aggregate value to a variable and re-use

One of the SQL features that’s usually glossed over or ignored in web development is the ability to create variables and then reuse them in subsequent statements. With this schema, an example would be calculating the “% of total spend” for the individual items – most people would run one query to generate the total and then a separate query to calculate the % of spend. For something trivial like this it doesn’t matter but if you were involving complex WHERE predicates it could be a nice performance boost.

The syntax for variables is relatively easy and it’s actually a powerful concept:

SET @total_spend = (SELECT total_spend FROM (SELECT SUM(item.total) AS total_spend FROM item) it);
SELECT receipt_id, (SUM(item.total) / @total_spend) * 100 FROM item GROUP BY receipt_id;
view raw gistfile1.sql hosted with ❤ by GitHub

Add synthetic “pseudo” columns using variables

This one is a Matt Daum favorite and pretty handy. Looking at the example, say that you wanted to assign a “sequence” value to each item depending on their rank order based on “total” within their “category_id”. In plain English, for each “category_id” you want to assign the most expensive item a “1”, the second most a “2”, and so on.

This seems straightforward, but try and construct a result set using only a GROUP BY or some combination of sub queries, I’ll wait. Turns out, the easiest way to accomplish this is to use variables to construct a “pseudo” column that increments and resets when the category changes.

SET @type='';
SET @num = 1;
SELECT @num := if(@type = category_id, @num + 1, 1) as sequence, @type := category_id as extra, bfa.*
FROM (
SELECT id, category_id, total FROM item ORDER BY category_id ASC, total DESC
) bfa
view raw gistfile1.sql hosted with ❤ by GitHub

Select only GROUP’ed rows that fulfill a second clause

Sorry for the terrible description, an example will make it clearer. Given our schema, lets say you wanted to select *only* the most expensive items per category, how could you set about doing it? The obvious approach would be using some combination of GROUP BY and MAX but unfortunately because of the semantics of GROUP BY that wont work as expected.

A better approach, is to leverage an INNER JOIN along with MAX() to only select the rows that match the max total per category:

SELECT item.id,
item.category_id,
item.total
FROM item
INNER JOIN (SELECT Max(it0.total) AS total,
it0.id, it0.category_id
FROM item it0 GROUP BY it0.category_id
) it0
ON item.total = it0.total AND it0.category_id = item.category_id
GROUP BY item.category_id
view raw gistfile1.sql hosted with ❤ by GitHub

The caveat here is that you’re really selecting the highest total, so if two rows have the same total you’re not guaranteed which one you’ll end up with. This approach also scales out, in the sense that you can add additional INNER JOINs to limit the resultset in situations where you’re getting tripped up by GROUP BYs and ORDER BYs.

Anyway, as always, we’d love to hear your favorite tips and tricks in the comments!

MySQL and System Time

By Matt Daum | 0 Comments

Recently for a client we had a very peculiar problem: a nightly script which checked if a person has done a certain action that day was always flagging everyone. We tested the script multiple times on our servers and it always worked fine. The query had something similar to this:

SELECT * FROM action_table WHERE DATE(action_table.time)=DATE(NOW())

Well after a while of trouble shooting we found out that the system clock on the clients server was skewed and in the wrong time zone. We synced the machines clock and update its time zone. Before it had been in UTC time and we switched it over to EST. What is interesting is that MySQL did not respect the new time zone of the clock, it was still reporting as if the system was set to UTC. We then did a soft restart(reload) on the MySQL service, but it still maintained that it was in a UTC timezone and not EST. It took a hard restart of the MySQL service to have it respect the EST timezone.

All in all, as far as we can tell you need to restart the MySQL service to have it respect a new timezone.