FULLTEXT & InnoDB, and Backing up database

PHPSardine

Sorry for all the newbie questions, but you all are so helpful.

Anyway, I have 3 questions:

1) I put together a database with 17 tables all of the InnoDB type. This is my first time doing this, so I've been learning as I go. Currently, I wanted to make a couple of fields searchable. I tried to make them FULLTEXT indices, but found that it's not allowed with InnoDB tables. I couldn't easily fidn a way to do searches with InnoDB. Is this something that can be done. If so, how?

2) If the answer to number 1 is no, can I change my tables to MyISAM and not mess anything up? In other words, I have a lot of related tables called into queries with joins...by switching the DB type, will these relationships be compromised?

3) I'm finally getting to the point where I have a lot of info in my databases and I would like to start backing things up regularly. I am using phpMyAdmin offered by my hosting company. Is there some easy code that will automatically make an SQL backup of my database?

Thank you all so so much!!!

Sxooter

The full text search engine is dependent on the myisam table type.

You can search using like, but it's not as efficient as a real full text engine will be.

If you switch to myisam tables you will no longer have functionaing pk/fk relationships.

If you want real time online backup of innodb tables you can buy the commercial backup tool that the author of innodb sells. There are some other ways that can work.

Contrast and compared:

In PostgreSQL the full text search engine runs with the default (and only) table handler. There are no such things as non-transactional tables, so you don't have to make these particular choices with it. Oh, and online, real time, coherent backups are free. As is the database no matter what you use it for. Just a thought. Same is true of the Firebird dbms.

Your other option is to store all your normal data in the innodb tables and then run a job that copies them into full text indexed myisam tables every so often.

PHPSardine

So...as far as the search function is concerned, I must have my tables as MyISAM to do a full text search? If I switch the tables that I want to be searched, can I preserve the relationships between pk/fk in anyway? How would you deal with such a switch?

That's for the info on backing up the database. I thnk I'll look into buying some software to handle it. It'll problably be the best solution for me.

Thanks!

Sxooter

If you switch to myisam tables, then the definitions of the foreign keys simply disappear. Note that you'll still have your primary keys.

Maintaining a relationship between you FK/PK is now up to your application, as the database simply can't do that. Note that a lot of applications don't need transactional integrity to work well, so this may be an ok way of doing things for you.

My solution would be to use a different database. But if you're married to MySQL, then that's the compromise you have to make.

Good luck on your project.