Multiple Databases - Search and retrieve.
Hi
I am very new to MYSQL and need some help and pointers to do certain things in a very optimized way, the result of which should be faster speed to the end user and ease of scalability of the application.
Using PHP 5.2.17 and MySql 5.0, can have multiple DBs but of fixed(limited) size.
In app, there are products, which have categories, which may/maynot have sub categories and categories at the leaf nods of this tree shall have a database assigned to them which will have info about the chapters(name, id, description, summary, reviews,image,linkInfo etc.)
The end result is a lot of data(rows, 1 chapter each), over a lot of databases -
Problems -
1. Web app shall have search capability, so if i have to search for a "string", it has to be searched in all the columns, of all tables, of the the DBs.
I have limited the find to 15 for 1 page display but i need the number of matched results. so that it could be displayed: - Showing 1 to 15 of 4210.
what is the most optimized way to implement it.
2. Also, since i shall have multiple databases, i have to show chapters so their unique ID and database name should be known to locate them, considering the table name in each of the databases is same, so what is the best method to store links in the DB.
I am at the very early stage of development so i think the problems will keep me busy, I hope to get the reply of my dreams.
Any pointers towards a good book regarding this(handling data in multiple databases) or an illustrative article shall be very helpful.
Thanks in advance.
Saurabh.