> I have two tables that should be related by a customer > id (established in the customer table). The second > table is going to have documents in a "text" column. > I want the ability (at least I think) to have full > text search for this second table. Hence I chose a > myIsam table type. > So a few things: > 1-Can I realte the customer ID somehow to the customer > table. In my application I'm passing the ID value > into the record insertion. By the way , the customer > table is set currently as Innodb. > 2- I'm wondering just how bad it would be to not have > "full text search". I'm a bit unclear, but I suspect > that I'd have to format the docs in a certain way to > highlight the important keywords that would need to be > searched for. > 3-If I set both tables to myIsam or leave it as it is, > is there another way to relate the id's together ?
Yes, the ids are still related in the same way as they would be in an innodb table--the relationship just isn't _enforced_ by the DBMS. You have to enforce the relationship in the application logic. This is a very normal situation for everyone who uses myisam tables. If necessary you can simulate transactions with LOCK TABLES. So to insert a row into the child table which references a row in the parent table you could do something approximately like this: 1. use lock tables to lock both the parent and child tables--this is so that no one can delete the valid parent row before you are able to insert the child row 2. select the desired parent row from the parent table to make sure it exists 3. insert the child row which references the parent row 4. unlock tables Do something similar for updates and deletes Locking the tables like this can slow the application down if you have many concurrent updates/inserts/deletes, but not necessarily as much as one might think. The queries executed between the lock and unlock are usually very quick so the tables are not locked for very long and remember that with myisam tables the whole table is locked anyway for all update/deletes and sometimes for inserts so all you are doing is locking two tables at the same time instead of just one. It all depends on your application and the ratio of updates/inserts/deletes to selects as to whether this will work for you. best, Jeremy March > Thank you > Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
