MySQL: Query from Unknown Number of Tables

Question

Here's what I'm trying to do. I'm using ejabberd with mod_logdb. All conversations are logged to a table in the database tagged with the date.

ex: logdb_messages_2010-7-17_server_node

What I'm trying to do is write a search that will look in all these tables without hitting the DB too much. I'd like to create a view that takes all the data in the table so I can just hit that.

Problem is there may be days that have no logs and, of course, days before we started logging.

Is there a way I can somehow use a wildcard to snag these from MySQL? My other other though would be writing a daily cron job to recreate the view I'd need.

Answer 1

Wow, that's a Sr. antipattern.

You should query the information schema, unless you can control the schema and avoid the mistake in the first place.

If you cannot, you have to dynamically generate a prepared statement.

This is an example:

http://jayant7k.blogspot.com/2008/02/mysql-stored-procedures-dynamic-tables.html

Answer 2

You could use Merge engine to simplify selection of data from multiple tables.
Views don't improve performance. You have to optimize your queries and add necesarry indexes to improve performance. Sometimes a redesigning of schema is a sollution too.