[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
    FORCE INDEX (i_zip_from_dist_to) 
  JOIN venues v
    FORCE INDEX (i_venue_zip)
    ON z.zipcode_to = v.zipcode
  JOIN events e
    FORCE INDEX (i_event_venue)
    ON v.id = e.venue_id
WHERE
  z.zipcode_from = '92108' AND
  z.distance <= 5
;

| (permalink: FLH73yfRlZ) 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	z	range	i_zip_from_dist_to	i_zip_from_dist_to	9		11	100.00	Using where; Using index
1	SIMPLE	v	ref	i_venue_zip	i_venue_zip	5	hshaware_sandbox.z.zipcode_to	1	100.00	Using index
1	SIMPLE	e	ref	i_event_venue	i_event_venue	4	hshaware_sandbox.v.id	1	100.00	Using index

(3 rows returned)