defining index for table

Question

In my app I am facing a performance problem when loading from DB. I have a list using RecylerView and a cursor for data. Once the number of records goes beyond 1000, the list becomes too slow to load or for any operation. The query uses this WHERE clause: COL1 = something AND (COL2 LIKE something OR col3 LIKE something OR col4 LIKE something) AND col5 is NOT NULL

What would be the ideal way to define an index in this case to get better performance? Also will reducing the number of columns in the query result improve the performance?

Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/150203/discussion-between-preetha-and-pskink). — png, Jul 26 '17 at 14:10

score 0 · Answer 1 · answered Jul 26 '17 at 14:08

Indexes can help with searching or sorting. In your query, col1 should be indexed, any maybe col5 if many rows with a NULL value can be filtered out. (A query can use only a single index per table, so this would require a two-column index.)

An index cannot help with a slow list view, because it does not reduce the amount of data that the list loads from the DB.

To speed up the list, you must reduce the number of entries. See dynamic listview adding “Load more items” at the end of scroll for how to delay loading entries, but the optimal solution would be to add better filters so that you never need to load so many entries. (How is the user supposed to handle them anyway?)

defining index for table

1 Answers1