Re: Question about search performance
Re: Question about search performance
- Subject: Re: Question about search performance
- From: Guido Neitzer <email@hidden>
- Date: Tue, 16 Sep 2008 13:00:42 -0600
- Thread-topic: Question about search performance
On 16.09.08 11:40, "Chuck Hill" <email@hidden> wrote:
>> SELECT <bunch of attributes>, from ORDERS T0 WHERE
>> UPPER(T0.EMAIL_ADDRESS) LIKE UPPER('email@hidden') ESCAPE '\;
>>
>> Here is what I don't understand: If I run the above query directly
>> against our database, it takes about 12 seconds.
>
> I am somewhat surprised it would run that fast. Are you certain you
> are timing this exact query?
Why? If there is a case insensitive index on that column it should be able
to use it as it is just a begins with search. It should come back in
milliseconds ... just tested a similar query here on my MacBook Pro:
didev=# select contact_id from contact where lower(email) like
lower('guido@%');
contact_id
------------
...
(14 rows)
Time: 1,058 ms
I have my indexes on lower, not on upper (PostgreSQL). For more rows it
might be a bit slower ...
select contact_id from contact where lower(email) like lower('gu%');
...
(2974 rows)
Time: 14,406 ms
So, I'd say in either case is something wrong. Maybe there is an index
missing or the index is not set up for being case insensitive? The table I
used has only two millions rows, but I have only a MacBook Pro with the
internal 5k4 harddrive, so any decent deployment system should be WAY
faster. More than a minute sounds like a full table scan on a machine with
slow I/O and 12s sounds like caches kicking in.
>> Can anyone explain why this might be from an EOF perspective?
>
> Not immediately. But I would just avoid this whole issue and make the
> column collate case insensitively:
>
> ALTER COLUMN "ORDERS"."EMAIL_ADDRESS" TO COLLATE
> INFORMATION_SCHEMA.CASE_INSENSITIVE;
>
>
> Both will be much faster.
As it will use the index. How is FrontBase than handling case sensitive
searches (if they are needed)? Btw: can you have different indexes on the
same column in FrontBase, one case sensitive one not? That would help both
cases, if the planner is using the correct one.
cug
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden