Joining tables/multiple matches

[Anon]

Hey guys,

I'm developing a MySQL database of files for a few fileservers we have on this network. A program scours the 10 or so computers and archives the files into a huge database that's split into 4 tables: etc, video, audio, and images; I did this to increase search speeds. What I want to do is know how to search the four tables at once. Say the tables are all this:

create table index_etc (
id INT DEFAULT 0 NOT NULL AUTO_INCREMENT,
host VARCHAR(64), // computer name
dir VARCHAR(128), // directory
filename VARCHAR(255), // filename
primary key(id)); // index

Each table is like that, but say I want to search for any folders with "bacon" in them. I tried doing this:

SELECT host,dir FROM index_etc e, index_video v (etc.) WHERE e.dir LIKE "%bacon%" OR v.dir LIKE "%bacon%" OR ... etc. When it doesn't give me an error, the search takes forever as I think it's doing some infinite search. Does anyone know how I can do a search that will go through the first table, find results, then hit the second, etc., and return say 50 results at a time? Basically a normal search but scaled over multiple tables?

Thanks a lot!!

Mike

[[deleted]]

Joining tables is meant to let you select data from several tables at once, when there is a relation between the tables

If you don't specify a relation, every record from the first table will be selected once for every record in the second table, which will be selected once for every entry in the thrid table which will...
you get the idea.

If you have 2 records in 4 tables, you get 2x2x2x2=16 results. If you have 100 records... a lot.

So you'd better do the search on each table seperately, or slap together a search engine (see the article in the articles section)