-1

For the sake of discussion, please refer to the simplified structure of our ecommerce database below (running on MySQL 5.6 using InnoDB engine). At this point in time, the transactions and the transaction_items table stand at approx. 11.5MM and 15MM rows/each. These 2 are also the tables used the most (daily) for recording new transactions/transaction_items and producing summary/aggregate reports for analytics.

Problem: As of right now, we feel that (aside from the size of data in the tables) we have a lot of indexes that are taking up unnecessary space and could possibly be deleted or altered to be more efficient with disk usage. Below are a few examples:

table index_name size_mb
transactions idx_txs_on_staff_id_location_id_created_at 298
transactions idx_txs_on_staff_id_topic_id_created_at 298
transactions idx_txs_on_location_id_topic_id_created_at 254
transactions idx_txs_on_customer_id_created_at 225
transactions idx_txs_on_session_id 184

Question: Focusing on the transactions table solely, how would you index the table optimally to perform date range based queries involving one or more of the foreign key columns along with the is_void column (which is the generally NULL)?

Schema Structure

Here are some examples of our existing queries:

SELECT 
 t.* 
FROM 
 transactions t 
WHERE 
 t.location_id IN (1,2,3) 
 AND t.created_at BETWEEN '2020-12-01 04:00:00' AND '2020-12-01 03:59:59' 
 AND t.is_void IS NULL;
SELECT
 t.* 
FROM
 transactions t 
WHERE 
 t.staff_id IN (1,2,3)
 AND t.created_at BETWEEN '2020-12-01 04:00:00' AND '2020-12-01 03:59:59' 
 AND t.is_void IS NULL;
SELECT
 t.location_id,
 t.topic_id,
 COUNT(t.id) AS topic_count
FROM
 transactions t 
WHERE 
 t.created_at BETWEEN '2020-12-01 04:00:00' AND '2020-12-01 03:59:59' 
 AND t.is_void IS NULL
GROUP BY
 t.location_id, 
 t.topic_id;
SELECT
 t.location_id,
 t.staff_id,
 COUNT(t.id) AS staff_txs
FROM
 transactions t 
WHERE 
 t.created_at BETWEEN '2020-12-01 04:00:00' AND '2020-12-01 03:59:59' 
 AND t.is_void IS NULL
GROUP BY
 t.location_id, 
 t.staff_id;
SELECT 
 t.* 
FROM 
 transactions t 
WHERE 
 t.customer_id = 23
 AND t.created_at BETWEEN '2020-07-01 04:00:00' AND NOW()
 AND t.is_void IS NULL
ORDER BY
 t.created_at DESC;

Edit - 12/24/2020 @ 3:05PM EST Database: MySQL 5.6 using InnoDB Engine

2
  • Does your DBMS support partial indexes? Dec 24 '20 at 19:24
  • Hi Colin, I tried checking and I don't believe MySQL 5.6 supports partial indexes
    – Paul D.
    Dec 24 '20 at 20:09
0

So tackling this in the simplest of ways, the common predicates I see across your example queries are created_at and is_void. So you should have an index composed of at least those two columns (and optimally in the order of most unique to least unique), so likely the first column in the index would be created_at and then the is_void column after.

From there you can decide if you want to make your indexes fully covering for each scenario you provided above, e.g. one index for created_at, customer_id, is_void; another index for created_at, staff_id, is_void, etc (notice again the fields are in order of uniqueness). Or if you want to just let the generic created_at, is_void index partially cover each scenario and then your queries will do an additional level of filtering.

By looking at the table schema for transactions it looks like you have 5 foreign keys, so it wouldn't be the end of the world to have an index catered to each foreign key in conjunction with the created_at and is_void fields (if applicable to your scenarios). Five indexes is reasonable. And you don't need to fully cover every use case, but it's generally a good idea to identify your top use cases and try to cover them as best as possible by using the predicates of those scenarios in your indexes.

It also looks like you frequently SELECT location_id so it would be a candidate as an INCLUDE to your indexes (where it's not the predicate of the index themselves). E.g. CREATE INDEX idx_txs_on_created_at_is_void_includes_location_id ON transactions (created_at, is_void) INCLUDE (location_id)

Depending on the database system you're using (which you should tag in your question) there might be other features you can leverage to improve your indexing as well, such as Partial (Filtered) Indexes as Colin mentioned. Since it seems like your normal uses cases (based on the examples you gave) always filter on is_void IS NULL then that would make for a good candidate in a partial index.

1
  • 1
    Thank you for your response. I will update my question to reflect the database as being MySQL 5.6 using InnoDB engine.
    – Paul D.
    Dec 24 '20 at 20:03
0
INDEX(customer_id, is_void, location_id)
INDEX(customer_id, is_void, staff_id)
INDEX(customer_id, is_void, created_at)
INDEX(is_void, created_at, location_id, topic_id)
INDEX(is_void, created_at, location_id, staff_id)

The order of the columns in each INDEX is important. The inclusion of is_void is important to performance; leaving it out is why your indexes did not help much.

More discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Please provide SHOW CREATE TABLE in text format, not images, and not index names.

Other suggestions:

  • Use COUNT(*) instead of COUNT(id).
  • Use DECIMAL for money, not FLOAT.
  • Deleting unnecessary INDEXes saves some disk space, speeds up INSERTs only a little, and is generally a low priority.
  • Similarly, a "partial index" would not be much better than a full index. (Anyway, no version of MySQL has bothered to implement it.)
  • Other queries may or may not use the indexes you have or the indexes I suggest.
0

There are a few principles that should be guiding you here:

The Golden Rule of Indexing:

When you have multiple columns in your index, you can access using the column in the index so long as you use equality predicates on all prior columns in the index

This means that you probably want your date columns to appear last in the index. You only use range predicates against them so if you had an index on (created_at , location_id) and you had a filter of location_id = 1 and created_at between '2020-12-01 04:00:00' AND NOW() then you would be reading all of the index that covers created_at between '2020-12-01 04:00:00' AND NOW(). If the index was location_id, created_at then you would also be able to use the location_id filter to reduce the amount of index you read. You can read more about this rule here (it's written using Oracle but the principle is the same for any BTree index).

The second principle is that if a filter doesn't reduce the amount of data in the table you visit sufficiently enough then it's probably not worth including in the index. E.g. if is_void is generally null then having an index that will filter on that isn't going to benefit you much.

So your general pattern should be:

(foreign_key, created_at)

If you upgrade your MySQL to at least version 8.0.13 then you will be able to benefit from Index Skip Scans - this will allow an index that leads on a column with only a few distinct values to be used to filter on the secondary column. If you only had a few location_id values then an index on (location_id, created_at) could be used for your search on just created_at. If you don't upgrade then you will probably want an index that just covers created_at.

Looking at your index names, it looks like you've combined several columns for your current indexes, e.g idx_txs_on_staff_id_location_id_created_at. In order to benefit from this index you would have to have equality filters on staff_id and location_id, I don't see that anywhere in your examples. Even if there were queries that used both, it's likely that one column provides better selectivity than the other - in fact looking at your data model, it seems that location_id is a property of staff_id anyway so it shouldn't reduce the amount of data visited anyway.


Ordering columns in a BTree index by uniqueness is an old concept that hasn't ever really had any standing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.