On Mon, 22 Sep 2008 13:48:42 -0700, Jason wrote:
>Hello everyone,
>
>Hoping that I could get some help with a performance problem.
>Using version 3.5.2
>
>Here are the tables:
>CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT)
>CREATE TABLE Keywords4Objects (ObjectId INTEGER, KeywordId INTEGER)
>CREATE TABLE Keywords (KeywordId INTEGER PRIMARY KEY, Keyword TEXT NOT NULL
>COLLATE NOCASE UNIQUE)
>
>The Query:
>SELECT DISTINCT o.ObjectId, o.Name
> FROM Objects o
> LEFT OUTER JOIN Keywords4Objects ok ON ok.ObjectId =
>o.ObjectId
> LEFT OUTER JOIN Keywords k ON k.KeywordId = ok.KeywordId
>WHERE
> k.Keyword LIKE 'abc' OR o.Name LIKE 'abc'
>
>Initially, I was just using a join, but objects without keywords were not
>getting picked up. When I switched to Left Outer Joins. I got the objects
>that don't have keywords, but the query is unbearably slow. It went from
>being instantaneous to over a minute.
>
>Any ideas?
The only reason you need to use LEFT OUTER JOINs here is the
``OR o.Name LIKE 'abc'`` construct. LEFT OUTER JOIN makes
your intermediate result sets much too large.
Also, you use LIKE with an search expression without any
wildcard characters. LIKE doesn't use indexes, = does.
Warning: I have no experience with COLLATE NOCASE, so I'll
ignore that in my 'solution'.
Steps to take (you need all of them, except 1):
1) Use v6.2.3
2) Load Keywords.Keyword with all lower case.
Now you can use k.Keyword = 'abc'.
3) Load Objects.Name with all lower case,
or add an extra column Objects.Keyword as
a lower case version of Objects.Name
if you need to keep the original case
in Objects.Name.
Now you can use o.Name = 'abc'.
4) Add an index on Objects.Keyword
(not unique, of course).
5) Use INNER JOIN to collect matches via
Keywords4Objects
6) Add the matches on Objects.Name
(or Objects.Keyword) with a UNION.
7) Wrap the
SELECT ... JOIN UNION SELECT ...
in a SELECT DISTINCT to get unique results.
8) You probably don't use 'abc' but some
variable in your host language.
Due to 2) and 3) you will have to
lower case your search expression as well.
(untested).
>Thanks in advance.
HTH
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
