MySQL Forums
Forum List  »  Full-Text Search

Re: Multiple Databases - Search and retrieve.
Posted by: Rick James
Date: July 09, 2011 04:25PM

You have boxed yourself in by saying there shall be many databases, many tables, many columns.

Think outside the box. Consider adding another database, which will have one table, and that table will have two columns -- a TEXT field with a FULLTEXT index, and an id.

When you insert stuff into one of your many tables in one of your many databases, combine the indexable columns and INSERT one row into the extra table I am proposing.

OK, let's say that is viable. Now for figuring out how to get from the "id" to a database and table and row. Well, that gets messy. You could pick apart the id (or it could be 3 columns instead of 1). This would give you a db name, a table name and a row number. The you would construct (using PHP string functions)
$sql = "SELECT ... FROM $dbname.$tblname WHERE id = $rownum";
and execute it.

But, then you will wonder why you don't have one table in one database -- with a compound PRIMARY KEY composed of
dbname, tblname, rownum
Why are things split across databases? Across tables? Maybe splitting things makes _logical_ sense, but maybe the _physical_ implementation can be a single table.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Multiple Databases - Search and retrieve.
2165
July 09, 2011 04:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.