Search the Community

I use PHPMyAdmin. I have to convert an InnoDB table to a MyISAM table. I then have to add a FULLTEXT index to two of the table's columns. Is this how I should proceed? 1) Backup table by exporting its contents. 2) Go to "Relation view" and drop foreign key constraints. 3) ALTER TABLE `table_name` ENGINE = MyISAM; 4) ALTER TABLE `table_name` ADD FULLTEXT index_name (col1,col2); Is that it? Is it safe? Note that the table contains approximately 3,500 rows.

I present videos on my site. The videos (their titles, descriptions, etc.) are stored in a MySQL database. I want visitors to be able to search my database for videos. My MySQL version is 5.5.40-36.1. The storage engine of my tables is INNODB. The two columns I want searched are: title (varchar; unique index) description (text; no index) According to my research, the best way to allow users to search my database would be to use FULLTEXT, but my INNODB tables are incompatible. FULLTEXT is available for INNODB in MySQL 5.6.4 and above. I would rather not use LIKE '%...%', and I would rather not use a third party solution such as Sphinx because my database will never grow particularly large (I doubt it'll ever exceed 6,000–8,000 videos). What do you guys recommend? Is it okay to use LIKE with a database containing 6,000–8,000 items?

Hello to all! A friend of mine ask what is the difference between mysql table innodb and MyISAM..... A have made a fiew search but i can't get the real answer ... Also, i check the database of my website and 50% are innodb and 50 % are MyISAM.... I made a lot a select and insert... have a few update.... Do you have an advice to give me? Thanks! Pascal

Hey guys, I am looking into using MySQL's Spatial Extensions. However, I noticed that spatial indexes are not available for InnoDB tables which I am using to implement foreign key constraints. Instead, it is available on MyISAM tables, which do not support foreign keys. I have a table with user profiles and have a foreign key to a "city" table on which I would like to have a spatial index. To have this index, I need to use MyISAM, but then I have to throw my foreign key out the window. I can get the same performance if I add an index on the "foreign" key on the profile table; I can then simply join with the primary key of the "city" table just as before. The only difference is that I do not have a foreign key. This means that I lose the relationship constraint and managing the integrity of my key is then up to me. If I am not careful, I could add a wrong value which would not match anything in the "city" table and therefore a join cannot be done. Updating the "city" table will also be more complicated because then I have to update all rows in my referencing table myself. However, this is not a big concern. So, to sum up, to use the spatial index, I need to use MyISAM and lose my foreign key. Is the optimal solution to index my join columns properly and handle the correctness of my "foreign key" myself? Does anyone have any other ideas (or thumbs up to my approach)? Thanks.

My shared web host does not allow for InnoDB tables. I was planning to use them to take advantage of the foreign key constraint. Now I am going to try to make do with MyISAM, and it seems like the next best thing is a composite key. Would you agree with that? Where can I find information about how to correctly set that up? In my research, I keep finding endless information about foreign keys, and less about the concept of composite keys. Any help you can offer would be greatly appreciated. Thank you in advance for your consideration.