Whats the most efficient way to store an array of integers in a MySQL column?

user_81785 0
Viewed 24367

I've got two tables

A: 

plant_ID | name.
1        | tree
2        | shrubbery
20       | notashrubbery

B: 

area_ID | name    | plants
1       | forrest | *needhelphere*

now I want the area to store any number of plants, in a specific order and some plants might show up a number of times: e.g 2,20,1,2,2,20,1

Whats the most efficient way to store this array of plants?
Keeping in mind I need to make it so that if I perform a search to find areas with plant 2, i don't get areas which are e.g 1,20,232,12,20 (pad with leading 0s?) What would be the query for that?

if it helps, let's assume I have a database of no more than 99999999 different plants. And yes, this question doesn't have anything to do with plants....

Bonus Question Is it time to step away from MySQL? Is there a better DB to manage this?

mysql
6 Answers

8 years after this question was asked, here's 2 ideas:

1. Use json type (link)

As of MySQL 5.7.8, MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents.

2. Use your own codification

Turn area_id into a string field (varchar or text, your choice, think about performance), then you can represent values as for example -21-30-2-4-20- then you can filter using %-2-%.

If you somehow try one of these, I'd love it if you shared your performance results, with 100M rows as you suggested.

--

Remember than using any of these breaks first rule of normalization, which says every column should hold a single value

user_95338660
Related