MySQL optimizing by splitting table into two

Question

I currently have a table called map_tiles that will eventually have around a couple hundred thousand rows. Each row in this table represents an individual tile on a world map of my game. Currently, the table structure is like so:

id      int(11)    PRIMARY KEY
city_id int(11)
type    varchar(20)
x       int(11)    INDEX KEY
y       int(11)    INDEX KEY
level   int(11)

I also want to be able to store a stringified JSON object that will contain information regarding that specific tile. Since I could have 100,000+ rows, I want to optimize my queries and table design to get the best performance possible.

So here is my scenario: A player loads a position, say at 50,50 on the worldmap. We will load all tiles within a 25 tile radius of the player's coordinate. So, we will have to run a WHERE query on this table of hundreds of thousands of rows in my map_tiles table.

So, would adding another field of type text called data to the existing table prove better performance? However, this would slow down the main query.

Or, would it be worth it to create a separate table called map_tiles_data, that just has the structure like so:

tile_id int(11) PRIMARY KEY
data    text

And I could run the main query that finds the tiles within the radius of the player from the map_tiles, and then do a UNION ALL possibly that just pulls the JSON stringified data from the second table?

EDIT: Sorry, I should have clarified. The second table if used, would not have a row for each corresponding tile in the map_tiles table. A row will only be added if data is to be stored on a map tile. So by default, there will be 0 rows in the map_tiles_data table, while there could be 100,000 thousand rows in the map_tiles table. When a player does x action, then the game will add a row to map_tiles_data.

Answer 1

No need to store data in separate table. You can use the same table. But you have to use InnoDB plugin and set innodb_file_format=barracuda and as data is going to be text, use ROW_FORMAT=Dynamic (or Compressed)

InnoDB will store the text out side the ROW page, so having data in the same table is efficient than having it in separate table (you can avoid joins and foreign keys). Also add index on x and y as all your queries are based on the location

Useful reading:

Innodb Plugin in “Barracuda” format and ROW_FORMAT=DYNAMIC. In this format Innodb stores either whole blob on the row page or only 20 bytes BLOB pointer giving preference to smaller columns to be stored on the page, which is reasonable as you can store more of them. BLOBs can have prefix index but this no more requires column prefix to be stored on the page – you can build prefix indexes on blobs which are often stored outside the page.

COMPRESSED row format is similar to DYNAMIC when it comes to handling blobs and will use the same strategy storing BLOBs completely off page. It however will always compress blobs which do not fit to the row page, even if KEY_BLOCK_SIZE is not specified and compression for normal data and index pages is not enabled.

Don't think that I am referring only to BLOBs. From storage prospective BLOB, TEXT as well as long VARCHAR are handled same way by Innodb.

Ref: https://www.percona.com/blog/2010/02/09/blob-storage-in-innodb/