1

I have simplified a much larger table below:

CREATE TABLE `core` (
  `id` int NOT NULL,
  `loc_country` enum('United States','Colombia','United Kingdom',       
       'Australia','India','Germany','Canada','Korea','Netherlands',
       '200 more')  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `loc_city` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
  `job` enum('a','b','c','d') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `loc_country_2` (`loc_country`,`job`,`loc_city`(6))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
          ROW_FORMAT=COMPRESSED




explain format=json
    SELECT id FROM core
        WHERE id!=518601449
          AND loc_country='Mongolia'
          AND id < 518601449
          AND job='a'
        LIMIT 151\G

*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "14002.99"
    },
    "table": {
      "table_name": "core",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY",
        "loc_country_2"
      ],
      "key": "loc_country_2",
      "used_key_parts": [
        "loc_country",
        "job",
        "loc_city",
        "id"
      ],
      "key_length": "34",
      "rows_examined_per_scan": 45657,
      "rows_produced_per_join": 45657,
      "filtered": "100.00",
      "using_index_for_skip_scan": true,
      "cost_info": {
        "read_cost": "9437.29",
        "eval_cost": "4565.70",
        "prefix_cost": "14002.99",
        "data_read_per_join": "1G"
      },
      "used_columns": [
        "id",
        "loc_country",
        "job"
      ],
      "attached_condition": "((`api`.`core`.`job` = 'a') and (`api`.`core`.`loc_country` = 'Mongolia') and (`api`.`core`.`id` <> 518601449) and (`api`.`core`.`id` < 518601449))"
    }
  }
}

The query took 14 seconds to run, I need it to be done in 0.01 seconds

The biggest problem seems to be to use id < XXX and order by id, I thought that this should be "free" to use given that the id is primary key.
I need the id < and sort because I need to get a different portion out of the database with each query, if I'd not use it I would receive the same data with each query for each country+job

I can not parition the table as I have dozens of such queries using different columns, it's just one example.

I believe the compression makes a big impact, it might be responsible for most issues I have though I don't have the storage on my NVME disks to run without compression.

Would it help to add the primary key to the indexes I have? At the end ? I fear it would waste a lot of storage space.
Any ideas ?

3
  • I would like to add: once the data is in memory it works fast. But 14 seconds to get 150 rows from an indexed on two enums seems outrageously slow, as if it would load the entire data into memory
    – John
    Dec 12 '20 at 16:46
  • Why do you have as part of your WHERE 'id!=518601449 AND' ? could this be forcing a table scan? Dec 12 '20 at 20:01
  • @WilsonHauck I've tested it without too. It is kind of obsolete as the < is excluding it anyway, the reason is that a specific part of the table is accessed (that id) and the query is supposed to find similar entries, either above or below the id. I'll remove it
    – John
    Dec 12 '20 at 20:12
0

ASPECT #1: Indexing

There is no need to add the PRIMARY KEY value id to the index. Why ?

According to the MySQL Documentation of Clustered and Secondary Indexes, Paragraphs 1,2 under the Subheading How Secondary Indexes Relate to the Clustered Index say the following:

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

Thus, the primary key is automatically added to a secondary index.

The proof of this is in the XML output

  "used_key_parts": [
    "loc_country",
    "job",
    "loc_city",
    "id"
  ],

and

  "used_columns": [
    "id",
    "loc_country",
    "job"
  ],

ASPECT #2: Table Compression

Compression might be greatly contributing to the slowness. After all, InnoDB must decompress data coming from that table for it to reside in the InnoDB Buffer Pool. In addition, the InnoDB Buffer Pool holds both compressed and uncompressed data and index pages. How so ???

According to the MySQL Documentation on How Compression Works for InnoDB Tables , Paragraphs 1,2 under the Subheading Compression and the InnoDB Buffer Pool say the following:

In a compressed InnoDB table, every compressed page (whether 1K, 2K, 4K or 8K) corresponds to an uncompressed page of 16K bytes (or a smaller size if innodb_page_size is set). To access the data in a page, MySQL reads the compressed page from disk if it is not already in the buffer pool, then uncompresses the page to its original form. This section describes how InnoDB manages the buffer pool with respect to pages of compressed tables.

To minimize I/O and to reduce the need to uncompress a page, at times the buffer pool contains both the compressed and uncompressed form of a database page. To make room for other required database pages, MySQL can evict from the buffer pool an uncompressed page, while leaving the compressed page in memory. Or, if a page has not been accessed in a while, the compressed form of the page might be written to disk, to free space for other data. Thus, at any given time, the buffer pool might contain both the compressed and uncompressed forms of the page, or only the compressed form of the page, or neither.

SUGGESTION #1

Since the Buffer Pool holds both compressed and uncompressed pages, maybe increasing the InnoDB Buffer Pool size might slightly improve things. How ?

InnoDB has to evict unused pages from the Buffer Pool. A larger buffer pool reduces the number of times the Buffer Pool hasd to be cleared to make waves for new pages.

SUGGESTION #2

Perhaps changing the size of compressed pages might slightly improve things. This would require reloading the table with the new compression size. Please see my old post innodb_file_format Barracuda on changing the KEY_BLOCK_SIZE would be needed.

UPDATE 2020-12-12 19:52

Here is a crazy trick to try

Instead of your query

SELECT id FROM core
WHERE id!=518601449
AND loc_country='Mongolia'
AND id < 518601449 AND job='a'  LIMIT 151\G

Try refactoring as follows

SELECT id FROM (SELECT id FROM core WHERE loc_country='Mongolia' AND job='a') A 
WHERE id < 518601449 LIMIT 151\G

This will force the loc_country_2 index to gather ids in the subqueries first. Then, all IDs < 518601449 are dismissed. Finally, the LIMIT 151 is imposed.

I do not guarantee the result will be better. It could be worse.

You won't know until you try it out and see or at least look at the EXPLAIN plan !!!

5
  • Hi, thanks for your response. I would like to add, I have seen a similar query do an INTERSECT of INDEX,PRIMARY Which doesn't make sense if PRIMARY is always part of every secondary index. See this one: "key": "intersect(loc_country,PRIMARY)", loc_country is just an index on the country column
    – John
    Dec 12 '20 at 20:14
  • An intersect with the PRIMARY sounds very brutal. Why ? The PRIMARY KEY has all id values. The loc_country_2 index has some of the id values. So, an intersect may be necessary. Dec 12 '20 at 20:39
  • It sounds like a optimizer error to me ? In no way would anything not indexed ever match the query (it's based on the index after all) so ids not in the index couldn't be returned anyway ?
    – John
    Dec 12 '20 at 21:16
  • The problem is that you specified a range scan on id field which has no guaranteed dependence on the index. The Primary Key is the only index available for such a range scan. There is also the fact that the IDs in the loc_country_2 index will have IDs sorted in the index order. My statements can only answer after the fact because the query optimizer must evaluate both indexes to see if one or the other or both are needed to satisfy the query. In your case, it is both. It is not query optimizer error. INTERSECT exists for such scenarios. Dec 12 '20 at 22:20
  • Is there a 'trick' to make it understand the ID is only related to the index ? Without forcing an index
    – John
    Dec 12 '20 at 23:08
0

Get rid of the prefix indexing

KEY `loc_country_2` (`loc_country`,`job`,`loc_city`(6))

-->

KEY `loc_country_2` (`loc_country`,`job`)

It is rarely beneficial. For this particular query it is in the way (and hurts performance). If you simplified the query too much, any advice here may be useless.

I need to get a different portion out of the database with each query,

Do you mean "pagination"? Via OFFSET? That degrades. Or maybe id is used to "remember where you left off"? See http://mysql.rjweb.org/doc.php/pagination

I have dozens of such queries using different columns,

Please give us a couple more examples -- else our advice will be useless.

Please provide SHOW TABLE STATUS LIKE 'core'; -- I would like to see the table size, row size, and some other things.

In every case that I have seen so far of Using index merge intersect, a suitable composite index will work better.

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.