MySQL geospatial query optimisation suggestions

mongodbmysql-5.5optimization

I am new to DB Administration. Trying to sort out this query which is the heart of our application. I am searching google on similar questions, but would like an experts opinion on my particular problem.

I have 3 tables namely,

CREATE TABLE IF NOT EXISTS `Movie` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(150) NOT NULL,
  `synopsis` TEXT NULL,
  `releaseDate` DATE NULL,
  `runningTime` INT NULL,
  `officialWebsite` VARCHAR(120) NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `Showtime` (
      `movieId` INT NOT NULL,
      `cinemaId` INT NOT NULL,
      `showtime` DATETIME NOT NULL,
      `comments` VARCHAR(200) NULL,
      `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`movieId`, `cinemaId`, `showtime`),
      INDEX `fk_Showtime_Movie1_idx` (`movieId` ASC),
      INDEX `fk_Showtime_Cinema1_idx` (`cinemaId` ASC),
      INDEX `index_showtime` USING BTREE (`showtime` ASC, `cinemaId` ASC, `movieId` ASC),
      CONSTRAINT `fk_Showtime_Movie1`
        FOREIGN KEY (`movieId`)
        REFERENCES `MoviesNow`.`Movie` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_Showtime_Cinema1`
        FOREIGN KEY (`cinemaId`)
        REFERENCES `MoviesNow`.`Cinema` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `Cinema` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `addressL1` VARCHAR(45) NULL,
  `addressL2` VARCHAR(45) NULL,
  `addressL3` VARCHAR(45) NULL,
  `city` VARCHAR(45) NULL,
  `postcode` VARCHAR(45) NULL,
  `countryCode` VARCHAR(45) NULL,
  `phoneNumber` VARCHAR(45) NULL,
  `lat` VARCHAR(45) NOT NULL,
  `lng` VARCHAR(45) NOT NULL,
  `attributes` INT NULL,
  `screens` INT NULL,
  `sound` INT NULL,
  `seating` VARCHAR(45) NULL,
  `adult` DECIMAL(8,2) NULL,
  `child` DECIMAL(8,2) NULL,
  `senior` DECIMAL(8,2) NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idCinema_UNIQUE` (`id` ASC),
  INDEX `index_Cinema_lat_lng` USING BTREE (`lat` ASC, `lng` ASC))
ENGINE = InnoDB

Movies table got thousands of record, cinema table contains 10s of thousands and showtime got 1 million and growing.

Joining these 3 tables my query for selecting 10 records out of 1.3million takes about

2-5 seconds

Query

SELECT M.id AS movieId, M.title AS movieTitle, M.synopsis AS movieSynopsis, M.releaseDate, M.runningTime, M.officialWebsite,
C.id AS cinemaId, C.name AS cinemaName, C.addressL1, C.addressL2, C.addressL3, C.city, C.postcode, C.countryCode, C.phoneNumber, C.lat, C.lng, C.attributes, C.screens, C.sound, C.seating, C.adult, C.child, C.senior,
S.showtime,
DEGREES(ACOS(SIN(RADIANS("+lat+")) * SIN(RADIANS(lat)) + COS(RADIANS("+lat+")) * COS(RADIANS(lat)) * COS(RADIANS("+lng+" - lng)))) * 69.09 AS distance
 FROM Movie M
INNER JOIN Showtime S
ON S.movieId = M.id
INNER JOIN Cinema C
ON C.id = S.cinemaId

WHERE C.countryCode = 'GB'
AND S.showtime > NOW()

HAVING (distance * 3) <= (TIME_TO_SEC(TIMEDIFF(showtime, NOW())) / 60) // Just checking time to drive(3mins/ 1mile) to a cinema is less then the show start elapsed time.

ORDER BY S.showtime ASC, distance ASC
LIMIT 0, 10

Currently I have two options

  1. Creating another table with all the above select fields and
    repopulating that table every hour. In this way I can ignore JOINS and expired showtimes will not get to the new table, so I will end up with comparatively less records and my one table select statement with location query will be bit faster than now.
  2. Creating a mongoDB and just doing the same as 1st point.

or if there is a better way to optimise my tables and query to make it as fast as possible would be much appreciated.

As this is my main query for my entire application, this query has to be faster. If possible as fast as 10milliseconds would be much appreciated.

I am sure MySQL gurus like you would give a much better solutions than my options, and thats the reason I posted my question here.

Cheers,

Best Answer

Your problem is this:

DEGREES(ACOS(SIN(RADIANS("+lat+")) * SIN(RADIANS(lat)) + COS(RADIANS("+lat+")) * COS(RADIANS(lat)) * COS(RADIANS("+lng+" - lng)))) * 69.09 AS distance

Basically as you also filter by that:

HAVING (distance * 3) <= (TIME_TO_SEC(TIMEDIFF(showtime, NOW())) / 60)

this means a table scan to calculate all the distances.

Suggestion:

Box it. Put in a query where lat and lng are between extremes (lat > latmax and lat < latmin), in addition.

This allows indices on Lat and lng to be used to filter out most of the entries - while then the distance calculation works only on the rest.

There really is no other way to that - any real distance calculation is math heavy and index unfriendly - but a "bounding box" pretest can filter out all the elements too far away anyway.

Related Question