-1

I'm trying to get a simple FULLTEXT match to be faster when using order by on another column on a table with over 100 million rows. Is it possible to make a FULLTEXT with an order by on another indexed column fast? SQL Fiddle below with schema and explains of all queries:

http://sqlfiddle.com/#!9/ed646c/1

What I have so far is denormalization and a join but this requires a separate table and I would rather not have another table if not necessary. SQL Fiddle below (denormalized query at the end):

http://sqlfiddle.com/#!9/6aabf/8

I'm open to schema changes or, if I have to, a full search engine on top.

JJJ
  • 422
  • 3
  • 6
  • See this: https://stackoverflow.com/questions/6259647/mysql-match-against-order-by-relevance-and-column – AT-2017 Aug 01 '16 at 02:36
  • Thanks but I don't want to order it by relevance – JJJ Aug 01 '16 at 02:42
  • I see that only 2 rows would be the result in your test case; how many in the real data? – Rick James Aug 01 '16 at 06:23
  • If you say your first query is insanely fast: do you always use `limit 5`? Check your query without the limit (e.g. try a `count(*)`) and without the order. This is the minimum time your query needs if you order by anything else than relevancy - because it has to find all rows in the full text index, not just any random 5 first resutls. The time it requires to order and access the complete data from the table depend on the number of found rows. – Solarflare Aug 01 '16 at 06:59

1 Answers1

0

You don't need two copies of the same data; you can "self join" for the "denormalized" case.

Rick James
  • 106,233
  • 9
  • 103
  • 171