Re: Optimised way to search over 2 milllion poi data in mysql

[newbie Shai]

Dear Rick,
Sorry miss your reply just saw. Actually I am still not clear when you say you have split into 2 tables i only see one table there? Not so clear on this. Below are the information you required from my end.

1. currently the Ram are limited to 2Gb we can increase to 4Gb.

2. Below is the show table status.

poi - Name
MyISAM - Engine
10 - Version
Dynamic - Row_format
0 - Rows
0 - Avg_row_length
0 - Data_length
281474976710655-Max_data_length
1024 - Index_length
0 - Data_free
2001143 - Auto_increment
2014-05-24 02:52:31 - Create_time
NULL- Checksum


2. Show create table

CREATE TABLE IF NOT EXISTS `poi` (
`poiID` int(11) NOT NULL,
`type` varchar(50) NOT NULL,
`locationName` varchar(200) NOT NULL,
`state` varchar(50) NOT NULL,
`city` varchar(50) NOT NULL,
`lat` float NOT NULL,
`long` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=201143 ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `poi`
--
ALTER TABLE `poi`
ADD PRIMARY KEY (`poiID`), ADD KEY `lat` (`lat`,`long`), ADD KEY `lat_2` (`lat`), ADD KEY `long` (`long`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `poi`
--
ALTER TABLE `poi`
MODIFY `poiID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2001143;

4. Buffers.

bulk_insert_buffer_size-8388608
innodb_buffer_pool_size-8388608
innodb_log_buffer_size-1048576
join_buffer_size-131072
key_buffer_size-8384512
myisam_sort_buffer_size-8388608
net_buffer_length-16384
preload_buffer_size-32768
read_buffer_size-131072
read_rnd_buffer_size-262144
sort_buffer_size-2097144
sql_buffer_result-OFF

Unfortunately my colleague have fully cleared the table and manually split the data into few table as a temporary solution.