Searching Large Table Count with PHP & MySQL

[username]

Hi, I have a database which has about 200 tables in it, which need to be searched at once. Is there a best practice to search all these tables at once, other than writing an enormous UNION type query in MySQL?

PS: This is not a poor database design, just a large part of a very large whole, which cannot be separated.

Thanks in advance

[eelixduppy]

>> This is not a poor database design

You sure? ;)

Seems like you are going to need to setup some sort of aggregate read-only view/table that combines all of these tables into a data warehouse. Not sure if MySQL would be the best dbms to use for this much data, though. or if they have tools that would make this simpler.

But once you have the DW set up, you would just query against that.

[brotherhood of LAN]

If the tables are MyISAM the query should be reasonably fast anyway as there is a count in the metadata for each table. Otherwise... it'll probably be slow any other way than eelixduppy suggests.

[Seb7]

This sounds like something I would create a cache table for. Dump all the bits from the other tables that you want to search, then run queries on the cache table.

[eelixduppy]

Perhaps the merge storage engine could be useful? Although your tables have to be identical.

[dev.mysql.com...]

[coopster]

Have a look at sphinx search. You can index your tables in the background with a crontab. You will only have to search the index from there forward. Lightning fast.

[eelixduppy]

>> index

Depends on the type of queries. If a majority of the data will be returned from a query, then using an index may actually slow performance.

[coopster]

Data is always returned with a corresponding query when using sphinx but I was referring to the context of the OP:

about 200 tables in it, which need to be searched at once.
[...]
search all these tables at once

Once the index is found (assuming this is relational data) an exact query will be substantially faster.

Hopefully that made sense now.