Re: "like" and index

Tony Liao wrote:
> I try to explain analyze,but it doesn't work ,it use seq scan.
Generally speaking, LIKE doesn't use indexes. However, there are a
couple of things you could try - definitely use EXPLAIN to see if this
gets you the improvement you're looking for.

- You could try using = on the substring. I'm not sure whether this
would use an index or not, but it'll accomplish the same think as using
LIKE. Using your example,

SELECT id FROM table_a
WHERE substr(prefix, 1, length('johnsmith')) = 'johnsmith';

- You could use the BETWEEN clause instead - I know that BETWEEN uses
indexes when possible.

SELECT id, prefix FROM table_a
WHERE prefix BETWEEN 'johnsmith' AND 'ZZZZZZZZZZZZZZZZZZZZZZZZ';

You'd have to write your application code to actually apply the
"johnsmith" filter, and stop outputting results when the prefix ended -
that's why I've added "prefix" to the select clause. Also, with the
"Z"s, make that however many characters "prefix" is defined.

> ps:I have another table table_B would use
> table_B.prefix=table_A.prefix.so <http://table_A.prefix.so> how can I
> create the index?
If you're joining them, a regular index should get the job done.

CREATE INDEX idx_table_b_prefix ON table_b (prefix);

Then, when you're getting data...

SELECT [something]
FROM table_a a
INNER JOIN table_b b ON a.prefix = b.prefix
WHERE [some other condition]

The inner join will only select records where they match - i.e., there
are rows in both tables with the same prefix. If you change "INNER" to
"LEFT", you'll get the rows from table a, and if a match isn't found,
the table b columns will be null. If you change "INNER" to "RIGHT", it's
the opposite, but I've yet to find a good use for a right join other
than confusing the next person to look at it. :)

--
Daniel J. Summers
*Owner, DJS Consulting* Support <http://support.djs-consulting.com/> •
Tech Blog <http://www.djs-consulting.com/linux/blog>

daniel(at)djs-consulting(dot)com <mailto:daniel(at)djs-consulting(dot)com> •
http://www.djs-consulting.com <http://www.djs-consulting.com/>

GEEKCODE 3.12 GCS/IT d s-:+ a C++ L++ E--- W++ N++ o? K- w !O M--
V PS+ PE++ Y? !PGP t+ 5? X+ R* tv b+ DI++ D+ G- e h---- r+++ y++++