Sqlite+80K行+LIKE=键盘滞后-Sqlite+80Krows+LIKE=keyboardlag

It sounds to me that you are searching and ready the keyboard in the same thread. This way you will search as many times as you have characters and the typing speed is limited to the search speed.

听起来你正在搜索并在同一个线程中准备好键盘。这样,您将搜索与字符一样多的次数,并且打字速度仅限于搜索速度。

A proper solution is to separate this into two threads, one to read and display the keyboard, the second to search and display the search results. This way, if you are typing fast er than you can search, only the search will lag, but not the typing. For example the Firefox address bar works that way.

一个合适的解决方案是将其分为两个线程,一个用于读取和显示键盘,第二个用于搜索和显示搜索结果。这样,如果您输入的速度比搜索速度快,那么只有搜索会滞后,而不是输入。例如,Firefox地址栏以这种方式工作。

Your code will be more complex due to the two threads and the communication/coordination between the two, but I think this is the only good solution.

由于两个线程以及两者之间的通信/协调,您的代码将更加复杂,但我认为这是唯一的好解决方案。

Here's how I'm guessing SQLite performs that query:

以下是我猜测SQLite执行该查询的方式:

1. Find all rows that match one of your LIKE statements.

   查找与您的LIKE语句之一匹配的所有行。

2. Sort them by DEFIN.

   按DEFIN排序。

3. Truncate the results after the first 50 rows.

   在前50行之后截断结果。

I suspect the truly painful part is the sort; without that, it could simply collect the first 50 matching rows it found. Can you get away with not sorting the definitions?

我怀疑真正痛苦的部分是那种;没有它,它可以简单地收集它找到的前50个匹配行。你可以逃脱不对定义进行排序吗?

Because you are using LIKE sqlite won't be using an index and it will sequentially scan from the start till is hits a limit of 50 records in the result set. Depending on the order of your data it could hit the first 50 results in the beginning or at the end worst case.

因为您正在使用LIKE sqlite将不会使用索引,它将从开始顺序扫描,直到达到结果集中的50条记录的限制。根据您的数据顺序,它可能会在开始或结束时遇到前50个结果。

How about you create new column that stores the first letter of each of the base words in defin ie d for dog etc and index this column.

如何创建新列,用于存储defin中每个基本单词的第一个字母,即d等等,并为此列编制索引。

Modify your query to select * from words where fisrtletter = :firstletter order by defin limit 50

修改您的查询以从fisrtletter =的词语中选择*:首先按照定义限制50排序

This will cut your search space significantly

这将显着减少您的搜索空间

The use the like operator for the complete match

使用like运算符进行完全匹配

you could also pre-order the data so that the order by won't be needed, I suspect the order by will cause the full table to be scanned rather than terminating when the first 50 records are hit

你也可以预先订购数据,这样就不需要订单了,我怀疑订单会导致扫描整个表而不是在前50个记录被点击时终止

Try making the search query in a different thread. As UI operations are performed only on the main thread, any time consuming operations done on the main thread will delay the UI operations.

尝试在不同的线程中进行搜索查询。由于UI操作仅在主线程上执行,因此在主线程上执行的任何耗时操作都将延迟UI操作。

I generally use

我一般用

[self performSelectorInBackground:@selector(threadedFetch) withObject:nil];

in combination with Core Data's NSFetchedResultsController to start a fetch upon user search input. But be careful when treading. Use separate NSManagedObjectContext for each thread or have a single context which you lock with -[NSManagedObjectContext lock].

与Core Data的NSFetchedResultsController结合使用,可以在用户搜索输入时启动提取。但是在踩踏时要小心。为每个线程使用单独的NSManagedObjectContext,或者使用单个上下文锁定 - [NSManagedObjectContext lock]。