Making full table scan 10x faster in InnoDB

http://yoshinorimatsunobu.blogspot.com/2013/10/making-full-table-scan-10x-faster-in.html

by aritraghosh007 • 13 years ago

98 20 13 years ago

yoshinorimatsunobu.blogspot.com

rkalla• 13 years ago

Only because it was the first thing that popped into my head, you can use async file IO in Java 7+ with the AsynchronousFileChannel class[1]

Currently digging through Google to see if I can find how those async calls are implemented on Linux/Windows to see if you can expect similar speedups in performance as Yoshinori saw in this article.

[1] http://openjdk.java.net/projects/nio/javadoc/java/nio/channe...

jey3• 13 years ago

When doing full table scan, InnoDB scans pages and rows by primary key order.

What the fuck?

chrisbolt• 13 years ago

Doesn't that make sense if data is clustered by the primary key, as InnoDB does?

jey3• 13 years ago

Derp, yes. It's not an auxiliary index. However, it still seems weird for a full table scan to be implemented this way instead of whatever's convenient given the page layout. Why does it guarantee that the results will be in PK order?

adwf• 13 years ago

I don't know the ins and outs of InnoDB, but some databases match the physical order to the primary key order. Therefore this would probably make sense in that case.

continuations• 13 years ago

InnoDB uses clustered index. PK order is how rows are ordered "naturally".

chris_wot4• 13 years ago

I'd not be relying on that!

est• 13 years ago

the shell command "ls" also does that. If you have huge quantity of files under a directory, use "ls -f"

mikkelewis• 13 years ago

This is because InnoDB actually stores the data via the primary key index. I'm fairly certain this is the only way they could do it (sanely).

As a side note, because of this, secondary indices just have a pointer to a PK. So when looking up by a secondary index, it has to traverse two trees.

dunham• 13 years ago

Unless, of course, the secondary index has all of the columns you need to execute your query. (Sometimes it can help to add an extra column or two to your secondary index if you want to avoid the extra lookup.)

sqlite3 stores rows in an integer primary key index. If you do not specify an "integer primary key" column, it synthesizes one behind the scenes (essentially a rowid) and your primary key lookups end up going through two indexes.

b0b0b0b• 13 years ago

I would guess that InnoDB is trying to preserve some behavior of MySQL where full table scans are expected to proceed by primary key order.

The solution sounds fragile; I guess you can't query by extent?

jjoe• 13 years ago

Yoshinori is the engineer who developed MHA, MySQL replication auto failover. It's essentially a perl script that ensures a master is always available and functional. This is so DBAs don't have to spend hours manually fixing a broken master/slave replication.

Making full table scan 10x faster in InnoDB

Related Stories