[SQLize]

Home | About | FAQ | Links | Feedback

Enter MySQL script (temp tables/CRUD allowed, 5 sec / 5MB / 20 rows max):

CREATE TABLE zipcode_distances (
  zipcode_from CHAR(5) NOT NULL,
  zipcode_to CHAR(5) NOT NULL,
  distance FLOAT NOT NULL,
  PRIMARY KEY (zipcode_to, zipcode_from),
  INDEX i_zip_from_dist_to (zipcode_from, distance, zipcode_to)
);
CREATE TABLE venues (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  zipcode CHAR(5) NOT NULL,
  INDEX i_venue_zip (zipcode)
);
CREATE TABLE events (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  venue_id INTEGER NOT NULL,
  INDEX i_event_venue (venue_id)
);

INSERT INTO zipcode_distances VALUES
  ('92108','92108',0.0),
  ('92108','92103',2.4), ('92103','92103',0.0), ('92103','92108',2.4),
  ('92108','92104',2.0), ('92104','92104',0.0), ('92104','92108',2.0),
  ('92108','92105',3.4), ('92105','92105',0.0), ('92105','92108',3.4),
  ('92108','92110',3.7), ('92110','92110',0.0), ('92110','92108',3.7),
  ('92108','92111',2.5), ('92111','92111',0.0), ('92111','92108',2.5),
  ('92108','92115',3.9), ('92115','92115',0.0), ('92115','92108',3.9),
  ('92108','92116',1.2), ('92116','92116',0.0), ('92116','92108',1.2),
  ('92108','92120',3.0), ('92120','92120',0.0), ('92120','92108',3.0),
  ('92108','92123',4.6), ('92123','92123',0.0), ('92123','92108',4.6),
  ('92108','92124',4.0), ('92124','92124',0.0), ('92124','92108',4.0);

INSERT INTO venues (zipcode) VALUES
  ('92103'),('92104'),('92105'),('92108'),('92110'),('92111'),
  ('92115'),('92116'),('92120'),('92123'),('92124'),('92124');

INSERT INTO events (venue_id) VALUES
  (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(1);

EXPLAIN
SELECT
  v.zipcode, z.distance, e.venue_id, e.id
FROM
  zipcode_distances z
  JOIN venues v
    ON z.zipcode_to = v.zipcode
  JOIN events e
    ON v.id = e.venue_id
WHERE
  z.zipcode_from = '92108' AND
  z.distance <= 5
;

| (permalink: 66rTDw4VB7) Sample 1 | Sample 2

No results are displayed if 5 second timeout reached.

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	v	index	PRIMARY,i_venue_zip	i_venue_zip	5		12	100.00	Using index
1	SIMPLE	z	eq_ref	PRIMARY,i_zip_from_dist_to	PRIMARY	10	hshaware_sandbox.v.zipcode,const	1	33.33	Using where
1	SIMPLE	e	ref	i_event_venue	i_event_venue	4	hshaware_sandbox.v.id	1	100.00	Using index

(3 rows returned)