I am having trouble with the on-growing data size in my MySQL database.

I am using Ejabberd and MAM function which will make use of an archive table to store messages sent between users and the table keeps growing. It now takes over 10 seconds to query something like

    WHERE username = '<some_id>'
      and bare_peer = '<some_string>'
      and timestamp >= '<some_timestamp_in_microseconds'


SELECT timestamp, XML, peer, kind, nick FROM archive
    WHERE username = '<some_id>'
      and bare_peer = '<some_string>'
      and timestamp >= '<some_timestamp_in_microseconds>'
      and timestamp <= '<some_timestamp_in_microseconds>'

These are very common SQL that would execute thousands of times each day, and since the SQL are executed from within Ejabberd, I cannot change the syntax.

Current Situation:

  • Instance Specification: 8 core CPU, 64 GB RAM innodb_buffer_pool_size: 49392123904 bytes (roughly around 49GB)
  • With references to this post, I got the result of 1005383M (roughly 1TB) estimated requirement of memory size.
  • The archive table size: 700GB of data, and ~200GB of index, around 0.9b of rows (yes, a lot of rows)
  • Here is the table creation SQL:
CREATE TABLE `archive` (
  `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `timestamp` bigint(20) unsigned NOT NULL,
  `peer` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bare_peer` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `xml` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `txt` text COLLATE utf8mb4_unicode_ci,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `kind` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nick` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `id` (`id`),
  KEY `i_username` (`username`) USING BTREE,
  KEY `i_timestamp` (`timestamp`) USING BTREE,
  KEY `i_peer` (`peer`) USING BTREE,
  KEY `i_bare_peer` (`bare_peer`) USING BTREE,
  FULLTEXT KEY `i_text` (`txt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


With the below information, one thing I could think of is to:

  • Partition the archive table with Primary Key (RANGE / Per 5m of rows), but from my understanding, since MySQL doesn't support fulltext index in Partitions, I would be required to drop the fulltext index in the txt column, which I think is ok.

  • Unfortunately, since MySQL could only partition on Primary Keys, and I cannot change the SQL. I therefore cannot utilize the partition directly on the SQL. What I could do is to drop the entire partition regularly and keep the remaining index size to fit into memory as much as possible.

I am posting to seek for a second opinion on whether:

  1. Is this the best way I could do with the above limitations?
  2. If so, How can I partition such a big table without downtime, by using possibility pt-online-schema-change ?

Thank you all for your time.

  • What are the ranges of those timestamps in the queries? Usually recent? Always recent? Covering a small or a large amount of time? Also check which of indexes the engine is actually using for those queries via explain. Commented Jun 7, 2020 at 8:37
  • For 80% of the time, timestamps are within 1 week, covering not more than 1 day or maybe 2. For the second query explain shows Using intersect(i_bare_peer,i_username); Using where; Using filesort and for the first query Using intersect(i_bare_peer,i_username); Using where. Commented Jun 7, 2020 at 9:27
  • SHOW TABLE STATUS will provide the table size.
    – Rick James
    Commented Jun 9, 2020 at 18:19
  • The result shows around 770GB of data and 250GB of index. Commented Jun 10, 2020 at 3:13

2 Answers 2


Fitting the index into RAM is not a useful goal. Decreasing the number of blocks of the index to use is a useful goal.

"Using intersection" is not as fast as the following composite index. Both of your queries would benefit from

INDEX(username, bare_peer, timestamp)

When adding it, you can drop i_username since it is a prefix of this.

PARTITIONing, even if possible, is unlikely to improve performance.

Also, normalizing out the 4 names (in the same table?) would shrink this table significantly, thereby helping performance a little.

What version of MySQL? If it a new one, adding the above index should be relatively non-invasive.

More on designing indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

  • Thank you for the response. I am using 5.7. I will definitely try the new index, but I am trying to find a way to minimize the effect when adding the index, would there be anything other then algorithm=inplace, lock=none could facilitate the process? Commented Jun 10, 2020 at 3:13
  • Probably. If that won't work, you will get an error message.
    – Rick James
    Commented Jun 10, 2020 at 4:52

An application can have many queries. And you have indexes to help these queries run fast. Indexes can be of multiple columns (a.k.a. composite index) or single column. An index can help multiple queries - this, requires careful study and planning of index creation. And, this can help reduce the number of indexes and the size of the indexes.

Here are some points to consider:

(A) Composite Index:

An aspect of the composite indexes is that these can also serve where only a single column or multiple columns are used in the query - but with some rules. If an index is defined as (col1 + col2 + col3), the index is usable where the query has where clause using col1 only, or col1 + col2, or col1 + col2 + col 3. So, if you have indexes on col1, or col1 + col2 - these will are redundant, in this case. This is explained in the manual Multiple-Column Indexes.

Another aspect, is the order in which the columns are ordered in a composite index. This can play an important part in the query performance. That is, an index on col1 + col2 is not the same as that of the col2 + col1. High selectivity of the data is an important factor in determining the order of columns in a composite index.

What is selectivity? From the manual:

A property of data distribution, the number of distinct values in a column (its cardinality) divided by the number of records in the table. High selectivity means that the column values are relatively unique, and can retrieved efficiently through an index. If you (or the query optimizer) can predict that a test in a WHERE clause only matches a small number (or proportion) of rows in a table, the overall query tends to be efficient if it evaluates that test first, using an index.

(B) Index Prefixes:

In case, of the table and the indexes in the question post - you can see that all the indexes are single column indexes. One way to reduce the size of a single column index is by using Index Prefixes. This can be used on string type columns (e.g., VARCHAR), for example user_name. You can create an index that uses only the first N characters of the column. This will help reduce the index size. See Index Prefixes for details.

(C) Covering Index:

Consider the query from the question post:

    WHERE username = '<some_id>'
      and bare_peer = '<some_string>'
      and timestamp >= '<some_timestamp_in_microseconds'

A composite index on the user_name + bare_peer + timestamp produces a Covering Index for this query.

(D) A Hashed Column:

The following is the note from the manual topic Multiple-Column Indexes


As an alternative to a composite index, you can introduce a column that is "hashed" based on information from other columns. If this column is short, reasonably unique, and indexed, it might be faster than a "wide" index on many columns. In MySQL, it is very easy to use this extra column:

SELECT * FROM tbl_name 
WHERE hash_col = MD5(CONCAT(val1, val2))
AND col1 = val1 AND col2 = val2;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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