0

I want to use a table with a column that should have a million records and it has to retrieve the results very fast.

Im going to use it in a query like below

Database : MySQL

select tags from tags_master where tags like 'A%';

Im trying to use it in typeahead. i tried with sample data, but for a million entries it is taking so much time.

I already tried appying indexes on that field. is there any other trick to optimize it further for typeahead purposes ?

Thanks in advance.

  • I already tried appying indexes on that field. If an index by tags field do not help maybe nothing will help. I think it is taking so much time is a result of too much records are selected (statistically ~40k), so the process of transferring them from server to client is long. – Akina Aug 28 '18 at 6:00
  • This sounds more like a programming question, so Stack Overflow would be better a place. Typeahead / autocomplete features are often done on UI layer. For actual implementations, it depends on what language and framework is being used. – vonPryz Aug 28 '18 at 6:03
  • For example, there are over 1200 words in the English language that start with letter 'a'. It makes little sense to offer all of those as autocomplete alternatives, so consider looking up existing solutions. – vonPryz Aug 28 '18 at 6:10
  • require at least three characters to be typed before it tries to auto-match. – Jonathan Fite Aug 28 '18 at 14:23
0

The possible solution is to limit the resulting set to 10-20 rows as well as sorting not only alphabetical but also by frequency. There are vocabularies exists that contains words along with the probability to be met in the text. May be you have to build your own based on the query statistics. That is called ranking and is used by the search engines. In the simpliest case you have the table like that:

+------+------+
! word ! prob !
+------+------+

and the query is :

SELECT word
  FROM table
 WHERE word LIKE a%
 ORDER BY prob DESC
        , word ASC
 LIMIT 10
;
  • The prob concept is good, but this query still has to read all 1200 a's, plus sort them. – Rick James Nov 27 '18 at 4:16
0

Let's use some brute force.

First add in Kondybas's concept of prob to get some order to the words when there are more than 10. (Let's assume 10 is your limit.)

Next, pre-digest the table every night to recompute this:

a   Apple,arm,atlas,application
ap  Apple,application,aptitude
apt aptitude

etc -- up to 10 words, ordered, for each possible prefix. Sure, this will be a big table, but the speed will be binding. And, so what if the nightly rebuild of the table takes an hour.

And be sure to do this to rebuild:

CREATE TABLE new LIKE real;
rebuild the data in `new`         -- slow, but that is OK
RENAME TABLE real TO old, new TO real;  -- atomic and fast
DROP TABLE old;   -- clean up

OK, what about long prefixes? Let's say that the above technique works fine for up to 7 letters, then fizzles because there are not many words. In that case switch back to the original:

SELECT GROUP_CONCAT(word) AS words
    FROM Words
    WHERE word LIKE '$prefix%';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.