CODEWITHSUNDEEP
mysqlindexingmyisam
cyberbeat
cyberbeat

Reputation: 55

Is the primary key stored implicitly in other keys in mysql myisam engine?

My problem: imagine a table with millions of rows, like

CREATE TABLE a {
id INT PRIMARY KEY,
column2..,
column3..,
many other columns..
..
INDEX (column2);

and a query like this:

SELECT id FROM a WHERE column2 > 10000 LIMIT 1000 OFFSET 5000;

My question: does mysql only use the index "column2" (so the primary key id is implicitly stored as a reference in other indexes), or does it have to fetch all rows to get also the id, which is selected for output? In that case the query should be much faster with a key declared as:

INDEX column2(column2, id)

Upvotes: 2

Views: 927

Answers (2)

Rick James
Rick James

Reputation: 142208

Short answer: No.

Long answer:

MyISAM, unlike InnoDB, has a "pointer" to the data in the leaf node of each index, including that PRIMARY KEY.

So, INDEX(col2) is essentially INDEX(col2, ptr). Ditto for INDEX(id) being INDEX(id, ptr).

The "pointer" is either a byte offset into the .MYD file (for DYNAMIC) or record number (for FIXED). In either case, the pointer leads to a "seek" into the .MYD file.

The pointer defaults to a 6-byte number, allowing for a huge number of rows. It can be changed by a setting, either for saving space or allowing an even bigger number of rows.

For your particular query, INDEX(col2, id) is optimal and "covering". It is better than INDEX(col2) for MyISAM, but they are equivalent for InnoDB, since InnoDB implicitly has the PK in each secondary index.

The query will have to scan at least 5000+1000 rows, at least in the index's BTree.

Note that InnoDB's PRIMARY KEY is clustered with the data, but MyISAM's PRIMARY KEY is a separate BTree, just like other secondary indexes.

You really should consider moving to InnoDB; there is virtually no reason to use MyISAM today.

Upvotes: 4

Norbert
Norbert

Reputation: 6084

An index on column2 is required. Your suggestion with id in the index will prevent table scans and should be very efficient.

Further more it is faster to do this assuming that column2 is a continuous sequence:

SELECT id FROM a WHERE column2 > 15000 LIMIT 1000;

This is because to work with the offset it would just have to scan the next 5000 records (MySQL does not realize that you are actually offsetting column2).

Upvotes: 1

Related Questions