GPS表600000用户[关闭]

Question

I'm using mysql. my programming language is php.

I have 600.000 users that I get lat and lon of these users by cell-phone's gps every 10 seconds once.

some of my queries on this table:

• the nearest of users with a specific lan and lon.
• to get last lan and lon of each user every minute.

some of my queries 7-14 seconds take time like:

SELECT m1.*
            FROM gps m1 LEFT JOIN gps m2
             ON (m1.driver_id = m2.driver_id AND m1.id < m2.id)
            WHERE m2.id IS NULL

I think this table is too big.

Is it good idea to use thiry-party like Cloud Bigtable just for this table? Is there any solution? Let's first discuss MySQL fixes.

my table:

CREATE TABLE `gps` (
  `id` int(11) NOT NULL,
  `driver_id` int(11) NOT NULL,
  `trucks_drivers_id` int(11) NOT NULL,
  `x` varchar(100) COLLATE utf8_persian_ci NOT NULL,
  `y` varchar(100) COLLATE utf8_persian_ci NOT NULL,
  `speed` varchar(100) COLLATE utf8_persian_ci NOT NULL,
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci;

I don't set any indexs to this table.

Answer 1

You can find the id of the latest reading for each driver like this.

           SELECT MAX(id) id
             FROM gps
            GROUP BY driver_id

An index on (driver_id, id) will help that query. Then this query finds the latest position of each driver.

       SELECT a.lat, a.lon, a.driver_id
         FROM gps a
         JOIN (
                  SELECT MAX(id) id
                    FROM gps
                   GROUP BY driver_id
              ) b ON a.id = b.id

Pro tip: Avoid SELECT * on performance critical queries, especially from large tables. Instead give the names of columns you need.

Pro tip: Shorter columns are faster, especially in large tables. Fixed length columns are faster, especially in large tables. Don't use varchar(100) to store lat/lon values. If those values come from GPS use FLOAT. If they come from surveying or photogrammetry use DOUBLE. Or consider using the MySQL Spatial Data Extensions.