NYCPHP Meetup

[Michael B Allen]

I need a table that will be used basically as a map between records in
another table and I'm not sure how to create the indexes.

At first I thought I would just do something like:

  CREATE TABLE map (
      id int(5) unsigned NOT NULL AUTO_INCREMENT,
      a int(5) unsigned NOT NULL,
      b int(5) unsigned NOT NULL,

      PRIMARY KEY (id),
      UNIQUE (a,b)
  );

But I think I could work around dumping the id if that would help
which would give me:

  CREATE TABLE map (
      a int(5) unsigned NOT NULL,
      b int(5) unsigned NOT NULL,

      PRIMARY KEY (a,b)
  );

However ultimately I want to do queries with WHERE clauses like:

  FROM map m3, map m2, map m1
  WHERE  (m3.a = m2.a AND m2.b = m1.a AND m1.b = 72)
      OR (m3.a = m2.a AND m2.b = m1.b AND m1.a = 72)
      OR (m3.a = m2.b AND m2.a = m1.a AND m1.b = 72)
      OR (m3.a = m2.b AND m2.a = m1.b AND m1.a = 72)
      OR (m3.b = m2.a AND m2.b = m1.a AND m1.b = 72)
      OR (m3.b = m2.a AND m2.b = m1.b AND m1.a = 72)
      OR (m3.b = m2.b AND m2.a = m1.a AND m1.b = 72)
      OR (m3.b = m2.b AND m2.a = m1.b AND m1.a = 72)

so I'm wondering if I want to optimize lookups by a OR b so perhaps I
should do something like the following (not sure if this is even
valid):

  CREATE TABLE map (
      a int(5) unsigned NOT NULL,
      b int(5) unsigned NOT NULL,

      INDEX (a),
      INDEX (b)
  );

Or maybe I should have no indexes at all?

  CREATE TABLE map (
      a int(5) unsigned NOT NULL,
      b int(5) unsigned NOT NULL
  );

Can anyone recommend which method I should try first?

Mike