How to speed SELECT SQL query?-CodePudding

I am trying to execute a SQL query as fast as I can.

There is only single table with ~10 million records, the table has 3 indexes for faster read, but unfortunately the column I want to select from hasn't.

let me explain : I want to select the title from table bugs(id, token, title, category, device, reported_at, created_at, updated_at) :

What I am doing : SELECT title FROM (SELECT id FROM bugs WHERE reported_at = '2020-08-30' and token = 'token660')

it works but it's slow, although I used the 2 indexes reported_at and token, How can I speed it up ?

here are the indexes :

{
    "records": [
        {
            "Table": "bugs",
            "Non_unique": 0,
            "Key_name": "PRIMARY",
            "Seq_in_index": 1,
            "Column_name": "id",
            "Collation": "A",
            "Cardinality": 9791826,
            "Sub_part": null,
            "Packed": null,
            "Null": "",
            "Index_type": "BTREE",
            "Comment": "",
            "Index_comment": ""
        },
        {
            "Table": "bugs",
            "Non_unique": 1,
            "Key_name": "index_bugs_on_category_and_token_and_reported_at",
            "Seq_in_index": 1,
            "Column_name": "category",
            "Collation": "A",
            "Cardinality": 1,
            "Sub_part": null,
            "Packed": null,
            "Null": "YES",
            "Index_type": "BTREE",
            "Comment": "",
            "Index_comment": ""
        },
        {
            "Table": "bugs",
            "Non_unique": 1,
            "Key_name": "index_bugs_on_category_and_token_and_reported_at",
            "Seq_in_index": 2,
            "Column_name": "token",
            "Collation": "A",
            "Cardinality": 29946,
            "Sub_part": null,
            "Packed": null,
            "Null": "YES",
            "Index_type": "BTREE",
            "Comment": "",
            "Index_comment": ""
        },
        {
            "Table": "bugs",
            "Non_unique": 1,
            "Key_name": "index_bugs_on_category_and_token_and_reported_at",
            "Seq_in_index": 3,
            "Column_name": "reported_at",
            "Collation": "A",
            "Cardinality": 6085027,
            "Sub_part": null,
            "Packed": null,
            "Null": "YES",
            "Index_type": "BTREE",
            "Comment": "",
            "Index_comment": ""
        }
    ]
}

CodePudding user response：

You need to create an index with the columns of the search condition. In this case:

CREATE INDEX search_index 
ON bugs (reported_at, token);

Since you are searching with equal sign this should be fast. The query must be:

SELECT title FROM bugs WHERE reported_at = '2020-08-30' and token = 'token660'

If you cannot change the database, you can select rows separately, intersect them, and then get the title:

SELECT r.title 
FROM (
SELECT * FROM bugs WHERE reported_at = '2020-08-30'
) r
JOIN (
SELECT id FROM bugs WHERE token = 'token660'
) t
ON r.id = t.id

CodePudding user response：

In MySQL, multi-column indexes can only use the left-most columns. Other databases do not have this restriction.

This is because MySQL indexes are (by default) B-Trees. A multi-column index is a tree of trees. To use the index (category, token, reported_at) MySQL must first find a category in the category tree, then there will be a sub-tree of token's within that category, and finally a sub-tree of reported_at's within that category and token.

In your case, you have a multi-column index on (category, token, reported_at). If you were searching by just category, MySQL could use the index. Or by category and token, MySQL could use the index. Or by category, token, and reported_at.

You're searching by token and reported_at, but since you are not searching by category MySQL will not use the index. It would have to scan every entry in the category index for a matching token. Other databases are more flexible with how they use indexes and might try this anyway, but MySQL will not.

Use The Index, Luke has a good explanation of multi-column indexes in MySQL

In general, indexes with more than two columns are of questionable value.

So, just like before, the answer is to make a new index on the fields you are searching on. Or use a better database.