Relational Databases (MySQL)

I am making a search engine -- I need the engine to query multiple static tables (more than 2) and return a resource with the results.

This is not, I find, as simple as

...
FROM table1, table2, table3, ...
...

(afaik). I tried using JOIN but, unless I am doing it wrong, it is not the tool I'm looking for.

My idea is now to create a master global index (table) of all possible results with an attached id number -- searching this table would be easy. Then I'd JOIN this with every table, find the row with the matching id, and return results for that. (I'd have to JOIN it many times over with every table). This does not seem very elegant. In fact, my whole project is becoming less and less elegant as I discover more of SQL's limitations.

Ideas or pushes in the right direction would be appreciated.

ISO/SQL is inherently flawed for use in a database.

As postgreSQL shows it is possible to implement the highest standard transaction isolation level correctly and still fuck up ACID

section 12.2.2.1. in
http://www.postgresql.org/docs/8.1/static/transaction-iso.html

(Appreciate the ``predicate locking'' sand-in-the-eyes part, actually they'd just need to not execute queries concurrently. That is slower but the alternative is incorrect results, see "Worse is Better")