2

I am aware that any Index in MySQL Innodb contains primary key value in the end of it. If there was a table as follows.

create table BaseTbl ( 
    base_id int not null primary key auto_increment, 
    base_name varchar(30) 
);

create table Tbl (
    record_id int not null primary key auto_increment,
    base_id int not null,
    foreign key (base_id) references BaseTbl(base_id)
);

Table Tbl would have an index on base_id because that is FK. And that would implicitly have record_id as the final key part. Would that be ordered?

Example:

If the data was

BaseTbl
+---------+-----------+
| base_id | base_name |
+---------+-----------+
|       1 | Name1     |
|       2 | Name2     |
|       3 | Name3     |
+---------+-----------+

Tbl
+-----------+---------+
| record_id | base_id |
+-----------+---------+
|         1 |       1 |
|         2 |       2 |
|         3 |       1 |
|         4 |       1 |
|         5 |       1 |
|         6 |       2 |
|         7 |       2 |
|         8 |       1 |
|         9 |       3 |
|        10 |       1 |
|        11 |       2 |
|        12 |       3 |
+-----------+---------+

Would the base_id FK index's record_id part will it be ordered? Like this?

+---------+-----------+
| base_id | record_id |
+---------+-----------+
|       1 |         1 |
|       1 |         2 |
|       1 |         3 |
|       1 |         4 |
|       1 |         5 |
|       2 |         6 |
|       2 |         7 |
|       2 |         8 |
|       3 |         9 |
|       3 |        10 |
|       3 |        11 |
|       3 |        12 |
|       3 |        13 |
|       3 |        14 |
|       3 |        15 |
+---------+-----------+

It seems like it is ordered, as I did not see 'using FileSort' when I explicitly tried mentioning Order By on record_id. But I'd like a confirmation or point in documentation that confirms this. (I failed to find it myself).

2
  • 1
    Any index contains primary key expression. Index entries are sorted. There is no INCLUDE clause for indexes in MySQL. I think that sorting procedure sorts whole entries and do not distinguishes where is index expression part and where is attached primary key expression part. See fiddle - records ordered by index by common field are secondary sorted by primary key.
    – Akina
    Jan 9, 2020 at 11:16
  • 1
    PS. The common (non-primary) index and the index created for foreign key reference support differs in nothing.
    – Akina
    Jan 9, 2020 at 11:23

1 Answer 1

1

FK is a constraint. To facilitate checking that constraint, an INDEX is created. Run SHOW CREATE TABLE for confirmation. An InnoDB INDEX is expanded to include PK column(s). Also, if there is an index with the leftmost column(s) matching the FK's column(s), the FK is happy.

When doing a SELECT, the Optimizer has many possible ways to do the query. If you do not have an ORDER BY, then you must not assume the output is in any particular order. The Optimizer may not have chosen the index you expect, or there could be a GROUP BY that is performed by first sorting, or the GROUP BY is done by hashing, hence randomly ordered. Etc.

"Filesort" is a misnomer in two ways. It may not involve a "file"; that is it might be done in RAM. And it may need to be plural -- For example, GROUP BY a ORDER BY b is likely to involve two sorts. EXPLAIN FORMAT=JSON ... is clearer on this than a plain EXPLAIN.

So, yes, the index for the FK is effectively as you describe (2 columns, ordered). But so what? Don't trust it; tack on an ORDER BY if you need the ordering. As I mentioned above, the Optimizer is free to ignore that index.

A tip: If you want to make use of both columns of the 'index' in question, then explicitly specify INDEX(base_id, record_id). The FK should see that and say "That index is all I need; I won't make another one". Humans reading your code will say "Hmmmm, the schema designer needs these two columns indexed for some reason".

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.