MySQL Forums
Forum List  »  General

Re: Optimised way to search over 2 milllion poi data in mysql
Posted by: Rick James
Date: September 04, 2014 08:58PM

> ADD KEY `lat` (`lat`,`long`), ADD KEY `lat_2` (`lat`), ADD KEY `long` (`long`);

The last two are of no use.

> ENGINE=MyISAM
> Ram are limited to 2Gb
> key_buffer_size-8384512

key_buffer_size-8384512 --> should be 200M
MyISAM will not work as well with my algorithm.
If you switch to InnoDB (which has a clustered PRIMARY KEY), then
innodb_buffer_pool_size-8388608 --> should be 600M

Based on your CREATE TABLE, I would estimate 200MB to hold 2M rows in a MyISAM table, plus 150MB to hold your indexes. So, with a key_buffer_size = 200M, you are likely to cache all the indexes (even the useless ones) in RAM. Plus the rest of RAM has room to cache the data. Therefore, your queries (once things have been drawn into cache) will be CPU-bound, not I/O bound. This is without PARTITIONing. PARTITIONing would buy you nothing.

My technique, which depends on InnoDB and PARTITIONing, would also fit in RAM. It would be less CPU-bound. Since it can all fit in RAM, it is less dependent on needing to split into two tables.

As for 2 tables, ... OK, it is not very clear:
If you do need filtering, the fields you are going to test must be included in this table to avoid lots of JOINing to another table.
...
    -- Thing to tailor
    --   *id* = column that JOINs *Locations* and *Info*
    --   *Locations*, *Info* -- the two tables
    --   Scaling of lat, lon; here using *10000 in MEDIUMINT
    --   Table name

The idea behind having two tables is to keep the first table (the one that is searched) as small as possible, thereby minimizing I/O. The second table has the rest of the desired info; it does not matter (much) how bulky it is, since it will be probed by its primary key for only the desired number of "nearest" items; no need to slog through other rows.

Options: ReplyQuote


Subject
Written By
Posted
Re: Optimised way to search over 2 milllion poi data in mysql
September 04, 2014 08:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.