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.

MYSQL version --> 5.0.91 ALTER TABLE products ENGINE=INNODB; The error is that it responds that it changed the engine type, but when I view the database in mysqladmin, the tables are still MYISAM. I really wish I saved the CREATE TABLE commands. Basically, there are three tables. The variable types are VARCHAR, INT, TINYINT, DECIMAL, DATETIME, and YEAR. There are no null fields. One of the tables is a JOIN table, and all three of them possess foreign keys. Please let me know if I left out anything, and thank you in advance for your consideration. I threw together a couple of screen shots. Above the red line shows my command and the response showing that I successfully changed the sessions table. Below the red line shows what I see when I view the database in mysqladmin; the sessions table still has a MYISAM engine type.

hey guys im trying to set to columns as a full text index but im receiving an error: this is where the fault lies but i dunno why im getting the error...im using innodb engine for the table on mariadb 10 FULLTEXT `items_FULLTEXT` (`description`, `title`), this is the sql i'm executing which is failing....i know innodb now support fulltext so where am i going wrong here please? CREATE TABLE `items` ( `item_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `sub_category_id` int(11) NOT NULL, `sub_sub_category_id` int(11) DEFAULT NULL, `sub_sub_sub_category_id` int(11) DEFAULT NULL, `user_address_id` int(11) DEFAULT NULL, `condition_id` int(11) DEFAULT NULL, `title` varchar(90) NOT NULL, `description` text NOT NULL, `auction` int(11) NOT NULL, `buy_now` tinyint(1) NOT NULL DEFAULT '0', `starting_price` decimal(15,2) NOT NULL, `listing_duration` enum('1','3','7','10','30') NOT NULL, `buy_now_price` decimal(15,2) NOT NULL, `quantity` int(11) NOT NULL DEFAULT '1', `offers_accepted` tinyint(1) NOT NULL DEFAULT '0', `start_timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `dispatch_time` enum('Same Day','1 Working Day','2 Working Days','3 Working Days') NOT NULL, `returns` int(1) NOT NULL DEFAULT '0', `return_policy` text, `free_delivery` int(1) DEFAULT '0', `free_delivery_condition` enum('Town/City','County','Country','Continent','MENA Region','Worldwide') DEFAULT NULL, `collection` int(1) DEFAULT '0', `collection_only` int(1) DEFAULT '0', `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `personal_delivery` int(1) DEFAULT '0', `personal_delivery_distance` int(10) DEFAULT '0', `persnal_delivery_price` decimal(12,2) DEFAULT '0.01', `draft` int(1) NOT NULL DEFAULT '0', PRIMARY KEY (`item_id`), FULLTEXT `items_FULLTEXT` (`description`, `title`), UNIQUE KEY `items_item_id_UNIQUE` (`item_id`), KEY `items_user_id_INDEX` (`user_id`), KEY `items_category_id_INDEX` (`category_id`), KEY `items_sub_category_id_INDEX` (`sub_category_id`), KEY `items_user_address_id_INDEX` (`user_address_id`), KEY `items_condition_id_INDEX` (`condition_id`), CONSTRAINT `items_category_id_FOREIGN_KEY` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `items_condition_id_FOREIGN_KEY` FOREIGN KEY (`condition_id`) REFERENCES `conditions` (`condition_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `items_sub_category_id_FOREIGN_KEY` FOREIGN KEY (`sub_category_id`) REFERENCES `sub_categories` (`sub_category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `items_user_address_id_FOREIGN_KEY` FOREIGN KEY (`user_address_id`) REFERENCES `user_addresses` (`user_address_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `items_user_id_FOREIGN_KEY` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1243 DEFAULT CHARSET=utf16; thank you