What is the difference in perfomance of using composite primary key vs simple index?

Question

I'm using innodb mysql tables for storing data. In my project i have to store maxmind geoip database in mysql (to have native foreign keys with different entities).

Most tutorials how-to import maxmind geoip database in mysql advice following table definition for table with ip ranges:

CREATE TABLE IF NOT EXISTS `GeoLiteCity_Blocks` (
  `startIpNum` int(10) unsigned NOT NULL,
  `endIpNum` int(10) unsigned NOT NULL,
  `locId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`startIpNum`,`endIpNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

But i am using ORM, that requires one primary key (it implements automatically many methods for CRUD entities), so i thought to use different table structure:

CREATE TABLE `ipblocks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `startIpNum` int(10) unsigned NOT NULL,
  `endIpNum` int(10) unsigned NOT NULL,
  `locId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IP_RANGE` (`startIpNum`,`endIpNum`),
  KEY (`locId`) // FOREIGN KEY
) ENGINE=InnoDB;

Most common query to this table will be:

SELECT locId FROM ipblocks
WHERE %IP_ADDRESS% BETWEEN startIpNum AND endIpNum
LIMIT 1

In theory, this allows to use as ORM natively, as to use table data. I wanted to ask - will this degrade performance severely (i don't care about space on hard disk, perfomance is much more important for me)?

Table with ip blocks have about ~2 millions of rows.

The best index for your most common query is a spatial index (http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html) — newtover, Feb 04 '13 at 15:47
A spatial index can not be used for the query mentioned above. The `IP_RANGE` index would work. — G-Nugget, Feb 04 '13 at 16:23
@G-Nugget, you are right, will it descrese perfomance very much, or it will be ok? — avasin, Feb 04 '13 at 17:12

score 1 · Accepted Answer · answered Feb 04 '13 at 17:32

There wouldn't be a significant decrease in performance. The only time it would really make a difference is if the data in the table would change, but it looks like the data is basically static. The index that would be used is basically the same, but InnoDB uses clustered indexes, so it basically needs to do 2 index lookups instead of one, but the time that takes would not be noticeable.

What is the difference in perfomance of using composite primary key vs simple index?

1 Answers1