SEO

#1 (**permalink**) 02-28-2008, 10:30 AM

hi- i have two tables for a web index:

keywords:
-keywordid(int)
-keyword(text)

and

keywordurls
- keywordurlid
- keywordid
- urlid

I want to perform a simple query, where I find all the urls containing
a keyword:

select urlid from keywordurls where keywordid in
(select keywordid from keywords where keyword like '%searchterm%')

The problem is, I cannot create an index on keywordurls that makes the
search be the cardinality of the subquery results, it's always the
cardinality of the keywordurls table.

I've tried an index on keywordid, I've tried an index on urlid, and
two joint indexes - keywordid-urlid and urlid-keywordid

So, to me, it would seem that the subquery would execute first,
returning, say, X values, and which are keywordid's, and then if I
have a keywordid index, it would only have to find X values. But,
like I said, the explain command always says the search will be order
of the cardinality of the entire table!

Thanks for any help,
Dino

#2 (**permalink**) 02-28-2008, 10:30 AM

On Dec 9, 7:21 pm, "dino d." <dinodorr...@yahoo.com> wrote:
> hi- i have two tables for a web index:
>
> keywords:
> -keywordid(int)
> -keyword(text)
>
> and
>
> keywordurls
> - keywordurlid
> - keywordid
> - urlid
>
> I want to perform a simple query, where I find all the urls containing
> a keyword:
>
> select urlid from keywordurls where keywordid in
> (select keywordid from keywords where keyword like '%searchterm%')
>
> The problem is, I cannot create an index on keywordurls that makes the
> search be the cardinality of the subquery results, it's always the
> cardinality of the keywordurls table.
>
> I've tried an index on keywordid, I've tried an index on urlid, and
> two joint indexes - keywordid-urlid and urlid-keywordid
>
> So, to me, it would seem that the subquery would execute first,
> returning, say, X values, and which are keywordid's, and then if I
> have a keywordid index, it would only have to find X values. But,
> like I said, the explain command always says the search will be order
> of the cardinality of the entire table!
>
> Thanks for any help,
> Dino

Don't use a subquery when a join is much better. Try this instead:

select u.urlid
from keywordurls u
join keywords k
on u.keywordid = k.keywordid
where k.keyword like '%searchterm%'

Joins will typically perform better (and often much, much better) than
the equivalent SQL written with a subquery.

In this case you can drop the keywordurlid column. The primary key of
the keywordurl table should be (keywordid, urlid). The primary key of
the keywords table should have a primary key of (keywordid). Set the
primary key correctly for those tables (which should create the
indexes) and I'm sure the above query (with the join) will run pretty
quickly.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

optimal index for subquery