Thanks Igor! Good point on the invoice_number index - I didn't have my
brain turned on there!
On 8/2/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Mitchell Vincent <[EMAIL PROTECTED]>
> wrote:
> > Is there any way to determine if a query is using an index or not? In
> > PostgreSQL the "explain" works to tell whether a table is being
> > sequentially scanned or not..
>
> Prepend the query with EXPLAIN QUERY PLAN
>
> > I have a query :
> >
> > SELECT *,(total - balance_due) as total_paid FROM invoice_master WHERE
> > lower(invoice_number) LIKE lower('%%') AND status != 'Void' AND
> > status != 'Recur' AND status != 'Paid' AND status != 'Forwarded'
> > ORDER BY created ASC LIMIT 25
> >
> > The lower('%%') gets used with whatever field the user is searching
> > on.
> >
> > I have indexes on created, status and invoice_number - but apparently
> > I can't make an index on lower(invoice_number) -- can I?
>
> You can't. You can, however, create an index on invoice_number with
> COLLATE NOCASE clause.
>
> Note that condition "lower(invoice_number) LIKE lower('%%')" is always
> true - any string matches this pattern. What precisely is this supposed
> to achieve, and how an index on lower(invoice_number) is expected to
> help here?
>
> A condition on status can be more compactly written as
>
> status NOT IN ('Void', 'Recur', 'Paid', 'Forwarded')
>
> The query as written should use an index on "created" to implement ORDER
> BY clause.
>
> Igor Tandetnik
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
>
--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
