1

I am building a classifieds website. Can you suggest me how do I partition geolocation table based on country ???

UPDATE: Can I do based on latitude and longitude ?

Below is my table structure. 

CREATE TABLE IF NOT EXISTS `geo` (
 `id` int(11) NOT NULL,
 `name` varchar(255) NOT NULL,
 `location_type` tinyint(1) NOT NULL COMMENT,
 `parent_id` int(11) NOT NULL COMMENT 'parent location_id',
 `latitude` varchar(255) NOT NULL,
 `longitude` varchar(255) NOT NULL,
  `is_deleted` tinyint(1) NOT NULL COMMENT '0:visible,1:invisible'
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
kulls
  • 845
  • 2
  • 12
  • 37

1 Answers1

1

(This is an un-answer.)

Do not PARTITION for the heck of it. There is rarely any performance gain, nor coding convenience.

If you want to do any arithmetic with latitude and longitude, don't use VARCHAR, use something numeric. FLOAT is precise enough for 1.7m (5.6 ft).

Further discussion of partitioning, including the only 4 uses: http://mysql.rjweb.org/doc.php/partitionmaint .

What is the 'hierarchy' for?

Rick James
  • 135,179
  • 13
  • 127
  • 222