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

If you're going to be searching both by forest and by plant, sounds like you would benefit from a full-on many-to-many relationship. Ditch your plants column, and create a whole new areas_plants table (or whatever you want to call it) to relate the two tables.

If area 1 has plants 1 and 2, and area 2 has plants 2 and 3, your areas_plants table would look like this:

area_id | plant_id | sort_idx-----------------------------      1 |        1 |     0      1 |        2 |     1      2 |        2 |     0      2 |        3 |     1

You can then look up relationships from either side, and use simple JOINs to get the relevant data from either table. No need to muck about in LIKE conditions to figure out if it's in the list, blah, bleh, yuck. I've been there for a legacy database. No fun. Use SQL to its greatest potential.

How about this:

table: plants

plant_ID | name1        | tree2        | shrubbery20       | notashrubbery

table: areas

area_ID | name1       | forest

table: area_plant_map

area_ID | plant_ID | sequence1       | 1        | 01       | 2        | 11       | 20       | 2

That's the standard normalized way to do it (with a mapping table).

To find all areas with a shrubbery (plant 2), do this:

SELECT *FROM areasINNER JOIN area_plant_map ON areas.area_ID = area_plant_map.area_IDWHERE plant_ID = 2

You know this violates normal form?

Typically, one would have an areaplants table: area_ID, plant_ID with a unique constraint on the two and foreign keys to the other two tables. This "link" table is what gives you many-many or many-to-one relationships.

Queries on this are generally very efficient, they utilize indexes and do not require parsing strings.