On Wed, 02 Feb 2005 14:48:41 +0000, Richard Huxton <dev@xxxxxxxxxxxx> wrote:
> 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').
Unfortunately, all my cases are LIKE '%john_doe'. So I'm guessing I'll
never get an index.
How about the response time when doing both LIKE user_name and ORDER
BY timestamp? Why does it get blown out of the water like that, from 5
and 5 to 2000 ms? If a LIKE by itself takes 5 ms and an ORDER by
itself takes 5 ms... Doesn't it grab the results matching the LIKE and
the ORDER only those?
While we're on the subject of indexes, is there any way I can speed up
a SELECT DISTINCT user_name FROM log_entries? With 200.000 entries I
get like 46 seconds on this one.
I apologise if these things are already in the manual, I'm only now
getting used to it and I don't find some things right away.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
