Unfortunately, since there are 30 different columns that can be searched on
That's your first problem. I bet the schema isn't normalized and these are all varchar columns.
~~ Normalize your schema ~~ such that the main table is a set of integers. JOIN on PK and index whatever is required. Trust me, MySQL is fast when you do it right. I have tables with 650M records and lookups are just as fast as day 1. Keep table size AS SMALL AS POSSIBLE (less columns, use ints, etc).
Use date_ids & time_ids (from standard DWH techniques) if you are storing datetimes, not datetime fields (default indexing them is 1 second resolution which is stupid).
Comments
That's your first problem. I bet the schema isn't normalized and these are all varchar columns.
~~ Normalize your schema ~~ such that the main table is a set of integers. JOIN on PK and index whatever is required. Trust me, MySQL is fast when you do it right. I have tables with 650M records and lookups are just as fast as day 1. Keep table size AS SMALL AS POSSIBLE (less columns, use ints, etc).
Use date_ids & time_ids (from standard DWH techniques) if you are storing datetimes, not datetime fields (default indexing them is 1 second resolution which is stupid).