Re: Each morning some SQLs are very slow

[lisheng1 zhang1]

Thanks a lot for helps!

We tried InnoDB fulltext search in 5.7 earlier but found it also has the similar issue: initial SQL is much slower (compared with later identical SQL, even with SQL_NO_CACHE).

We are thinking about customizing mySQL fulltext search for our special need, for example, earlier when i read myISAM fulltext index code, i found it just used B-tree index (therefore from term to ROWKEY, then get to row), i thought we may index our ID instead of ROWKEY, this may save a lot of time.

Recently i started to read InnoDB fulltext code and found it is much closer to normal inverted index like lucene (but i have not read through yet, a lot more to learn ..).

Could you give some high-level guidance if such customization is possible in InnoDB fulltext search, thanks a lot (for now we just need some high-level guidance, i am familiar with basic mySQL source code and build/deployment issues, also i read mySQL innoDB fulltext papers).

MySQL fulltext search worked well as features but its performance seems not as good as that of popular search engine like lucene, so we are thinking about customization.

Thanks again for helps