CODEWITHSUNDEEP
mysqlindexing
Evg
Evg

Reputation: 3080

which table is faster in MYSQL?

Two tables:

CREATE TABLE `htmlcode_1` (
  `global_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  PRIMARY KEY (`global_id`),
  KEY `k_site` (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `htmlcode_2` (
  `global_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  PRIMARY KEY (`site_id`,`global_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

which one should be faster for selects and why?

'select * from table where site_id=%s'

Upvotes: 1

Views: 53

Answers (3)

Rick James
Rick James

Reputation: 142298

The first table represents many "globals" in each "site". That is, a "many-to-one" relationship. But it is the "wrong" way to do it. Instead the Globals table should have a column site_id to represent such a relationship to the Sites table. Meanwhile, the existence of htmlcode_1 is an inefficient waste.

The second table may be representing a "many-to-many" relationship between "sites" and "globals". If this is what you really want, then see my tips . Since you are likely to map from globals to sites, another index is needed.

Upvotes: 0

Jacques Amar
Jacques Amar

Reputation: 1833

The reason to use a PRIMARY KEY is to allow for either quick access OR REFERENTIAL INTEGRITY (CONSTRAINT ... FOREIGN KEY ...)

In your second example, you do not have the proper key for referential integrity if any other table refers to your table. In that case, other operations will be very very slow.

The differences in speed in either case for your particular case should be too small and trivial, but the proper design will dictate the first approach.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562320

The latter table is probably slightly faster for that SELECT query, assuming the table has a nontrivial number of rows.

When querying InnoDB by primary key, the lookup is against the clustered index for the table.

Secondary key lookups require a lookup in the index, then that reveals the primary key value, which is then used to do a lookup by primary key. So this uses two lookups.

Upvotes: 3

Related Questions