Re: query time

WireSpot wrote:
> I have a table with about 200.000 entries. Among other things, it
> contains an integer field I use as a timestamp, and a variable
> character field I use for user names. Certain queries are taking too
> long IMO. I'm trying this on both 7.4 and 8.0.
>
> If I do a direct comparison (using =) on the user name field and I
> sort by the numeric field, I get about 5 ms. If I do a LIKE on the
> user name and I don't sort at all, I get about 5 ms too. But if I use
> both LIKE on the user name and sorting on the timestamp, the time
> jumps to 2000 ms.

> explain analyze select * from log_entries where user_name like
> '%john_doe%' limit 100 offset 0;

This will not (and never will be able to) use an index on user_name.
Think about it, you'd need an index that ordered use_name so that
(john_doe, AAAAAjohn_doe, Zjohn_doe1234) were all next to each other.

If you anchor the search (LIKE 'john_doe%') and are using the C locale
then an index can be used (because it's the same as >='john_doe' AND
<'john_dof').

If you really need to do indexed searches anywhere in a text-field
you'll need to look at tsearch2 in the contrib/ directory. That lets you
build a full-text index, but it's really meant for documents rather than
user names.
--
Richard Huxton
Archonet Ltd