COUNT and GROUP BY on text fields seems slow
Why're all your string based columns defined as TEXT? If you read the performance comparison, you'll see that TEXT was ~3x slower than a VARCHAR column using identical indexing: http://forums.mysql.com/read.php?24,105964,105964
If your fields are only ever going to have 2 values, you're much better off making them booleans. You should also make everything NOT NULL
unless there's a real reason you'll need it to be NULL
.
Also take a look at the ENUM
type for a better way to use a finite number of human-readable values for a column.
As for slowness, the first thing to try is to create indices on your columns. For the particular query you're showing here, an index on species, region
should make a huge difference:
create index on mytablename (species, region);
should do it.