Re: Indexing questions: Index == key? And index vs substring - how successful?

On Thu, May 03, 2007 at 01:42:44PM -0700, Andrew Edson wrote:
> As the title of this message suggests, I've got a couple of questions about indexing that I'm not sure about. I've tried to take a look at the docs, but I can't remember seeing anything on these; it's quite possible, I admit, that I'm simply not remembering all of what I saw, but I would appreciate it if someone would help me to understand these.
>
> 1. Does an indexed column on a table have to be a potential primary key?

No, that's the difference between unique and non-unique indexes.

> The times were almost identical in the following areas: Before Indexing, after Indexing but before Analyzing, and after Analyzing.

If you want reasons, you're going to need to provide EXPLAIN ANALYSE
output.

> I have in mind something like this:
> select * from [event table] where substring(cntrct_id, 3,1) = 'H';
> which should select any event records associated with 'cntrct_id' values initally set up in August. (Jan = A, Feb = B, etc)
>
> If I established an index on the 'cntrct_id' field in the event
> tables, would it assist in speeding up the substring-based search,
> or would it not be effective at doing so?

Not directly, no. However, you can have indexes on expressions:
CREATE INDEX foo ON bar((substring(cntrct_id, 3,1)));

Which could speed up the above query (could, since it depends on
exactly how much of the table actually needs to be searched...)

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.