Dan Kennedy wrote:
On Thu, 2007-03-01 at 15:13 -0800, jp wrote:
Hi, I have a custom collation sequence (e.g.
mycollate). Are there any advantages in terms of
performance of declaring this collation at the table
level, instead of just at the index level?
For example, if I have:
CREATE TABLE people AS (
country_id char(02),
lastname varchar(100),
phone varchar(50)
);
CREATE UNIQUE INDEX people_mycollate on people (
country_id,
lastname COLLATE mycollate
);
- - - - - -
...will the following use the index (about 500k recs)?
- - - - - -
SELECT lastname FROM people
WHERE country_id='US' AND lastname>'A'
ORDER BY country_id,lastname COLLATE mycollate
LIMIT 100;
- - - - - -
SQLite will use the index to implement the country_id='US' clause,
and the ORDER BY, but not the lastname>'A' clause. This is because
the lastname>'A' doesn't use the "mycollate" collation.
If the COLLATE clause was specified as part of the table
definition, then lastname>'A' would be a "mycollate" comparison
and the index would be used for this too.
Dan.
I'm not sure if it is all implemented yet, but Richard has checked in
some changes to the collation handling.
The query should use the index as created if the comparison is changed
to use the specified collation like this.
SELECT lastname FROM people
WHERE country_id='US' AND lastname COLLATE mycollate >'A'
ORDER BY country_id,lastname COLLATE mycollate
LIMIT 100;
In this case you are using explicit collations everywhere the lastname
is used.
If you change the table definition then mycollate will be the default or
implicit collation for that field. The OP said that he wanted to use
different collations for this field in different queries, but that
doesn't mean that he couldn't use mycollate as the default collation and
apply any others that he needed using explicit collation clauses like
the query above.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
