0

I am using MYSQL Engine InnoDB. I created fulltext index on my exisitng table but when I insert new record it couldn't pickup my new insert data.

Below query is working when I drop my index and regenerate it. But when I creat index and Insert new record then fulltext index couldn't find recently enter data. 

ALTER TABLE data
ADD FULLTEXT INDEX fullTextIndexByName(`given_name`,`family_name`) ;


INSERT INTO `data`
(`id`,
`family_name`,
`given_name`,
`nickname`)

VALUES
(123224322,'TEST_family_name','TEST_given_name','TEST_nickname');


SELECT * FROM data user 
WHERE
MATCH(user.given_name, user.family_name)
AGAINST (LOWER(CONCAT('TEST_given_name','*')) IN BOOLEAN MODE)
ORDER BY user.family_name ;
Humaira Faheem
  • 1
  • 3
  • Are you using a transaction/no autocommit? In this case, it is the [documented behaviour](https://stackoverflow.com/q/37199082) - dropping and adding an index commits any open transaction. Alternatively, you might be missing/not showing the error in your insert syntax (the comma/the quote infront of `TEST_given_name` is incorrect, you can see it in the coloured code in the question), so your insert might not insert anything. – Solarflare Jun 06 '19 at 17:22
  • I am using mysql workbench to run this query. I just want to run this query using mysql query. I am not getting any error as well as not any result display. I use Start transaction/begin and commit but still when I run the fulltext query I am not getting any result? but I get result when I use simple where statement! – Humaira Faheem Jun 07 '19 at 19:34
  • a) you have to commit after the insert and before you do the select b) make sure you are actually using innodb and not myisam. c) if that does not solve your problem, try to reproduce it [here](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=7591d627f8f9620ff957f93f41cf6b22). This is the expected behaviour (add a commit before `select` to find the row). You seem to be either missing an important detail in your question, or did not understand what I meant with the link/the commit, so if you can reproduce your problem on dbfiddle, we have the exact situation of what you are trying to do. – Solarflare Jun 08 '19 at 05:26
  • Sorry, the purpose of providing a dbfiddle is to provide a working, executable code that shows your problem. Your code contains about 10 errors, e.g. your table is sometimes called `user`, other times its called `user_data`, and your table does not contain the column `given_name` which you add in the index, nor the column `generation` that you are using in your insert. Nevertheless, providing the fiddle helped, since the problem is already in your table definition: `utf8_bin` is case sensitive, while you are later making a search using `LOWER`, so the case doesn't match. – Solarflare Jun 10 '19 at 13:24
  • Thank you so much I resolve my problem. It is because of I am using LOWER in my query – Humaira Faheem Jun 10 '19 at 14:23

0 Answers0