Searching a database .... query optimization (how do I read the explain on output?)
I've got a more-or-less open search of the database in question, which
works just fine, if you don't mind the upwards of 20+ seconds it takes
to return the data. I'm finding that amount of time to be
unacceptable.
So, I've run the query through dbaccess using set explain on; and have
the following output:
QUERY:
------
select first 20 unique (user.id) as id,user.lastname, user.firstname
from
user, address, invention, ascpdef as state, phone
where
user.lastname like "%" and
user.firstname like "%" and
user.username like "1297%" and
invention.user_id = user.id and
address.type = 'User'
and address.type_id = user.id
and
add1 LIKE "%"
and
city like '%'
and zip like '%'
and
state.abbreviation like '%' and address.state_id = state.id
and
phone.type = 'User' and phone.type_id = user.id and
phone.phone like '%'
order by user.lastname asc, user.firstname asc, user.id desc
Estimated Cost: 26229
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By
1) root.address: SEQUENTIAL SCAN
Filters: (root.address.type = 'User' AND (root.address.add1 LIKE
'%' AND (root.address.city LIKE '%' AND root.address.zip LIKE '%' ) )
)
2) root.state: INDEX PATH
Filters: root.state.abbreviation LIKE '%'
(1) Index Keys: id
Lower Index Filter: root.state.id = root.address.state_id
NESTED LOOP JOIN
3) root.invention: INDEX PATH
(1) Index Keys: user_id (Key-Only)
Lower Index Filter: root.invention.user_id =
root.address.type_id
NESTED LOOP JOIN
4) root.phone: SEQUENTIAL SCAN
Filters: (root.phone.type = 'User' AND root.phone.phone LIKE '%' )
DYNAMIC HASH JOIN (Build Outer)
Dynamic Hash Filters: root.invention.user_id = root.phone.type_id
5) root.user: INDEX PATH
Filters: (root.user.lastname LIKE '%' AND (root.user.firstname
LIKE '%' AND root.user.username LIKE '1297%' ) )
(1) Index Keys: id
Lower Index Filter: root.user.id = root.invention.user_id
NESTED LOOP JOIN
What I'm hoping to do, is to allow my users to search by any of these
fields (last name, first name, client number, address, city, state,
etc....). I suppose that I could rewrite the function that creates
the query to omit the fields that have nothing in them. However, what
I'm wondering is if this could be sped up by using:
a. More / better indexes (how would I know from the output above?)
b. Multiple sub-queries in a temporary table?
c. Something else I'm not seeing.
Thanks for your help.
--Anthony
On Thu, 03 Jul 2003 11:08:49 -0400, Anthony Presley wrote:
Have you run the recommended suite of UPDATE STATISTICS commands?
Art S. Kagel
> I've got a more-or-less open search of the database in question, which
> works just fine, if you don't mind the upwards of 20+ seconds it takes
> to return the data. I'm finding that amount of time to be unacceptable.
>
> So, I've run the query through dbaccess using set explain on; and have
> the following output:
>
> QUERY:
> ------
> select first 20 unique (user.id) as id, user.lastname, user.firstname
> from
> user, address, invention, ascpdef as state, phone where user.lastname
> like "%" and> user.firstname like "%" and
> user.username like "1297%" and
> invention.user_id = user.id and
> address.type = 'User'
> and address.type_id = user.id
> and
> add1 LIKE "%"
> and
> city like '%'
> and zip like '%'
> and
> state.abbreviation like '%' and address.state_id = state.id and
> phone.type = 'User' and phone.type_id = user.id and phone.phone like '%'
> order by user.lastname asc, user.firstname asc, user.id desc
>
> Estimated Cost: 26229
> Estimated # of Rows Returned: 1
> Temporary Files Required For: Order By
>
> 1) root.address: SEQUENTIAL SCAN
>
> Filters: (root.address.type = 'User' AND (root.address.add1 LIKE
> '%' AND (root.address.city LIKE '%' AND root.address.zip LIKE '%' ) ) )
>
> 2) root.state: INDEX PATH
>
> Filters: root.state.abbreviation LIKE '%'
>
> (1) Index Keys: id
> Lower Index Filter: root.state.id = root.address.state_id
> NESTED LOOP JOIN
>
> 3) root.invention: INDEX PATH
>
> (1) Index Keys: user_id (Key-Only)
> Lower Index Filter: root.invention.user_id =
> root.address.type_id
> NESTED LOOP JOIN
>
> 4) root.phone: SEQUENTIAL SCAN
>
> Filters: (root.phone.type = 'User' AND root.phone.phone LIKE '%' )
>
>
> DYNAMIC HASH JOIN (Build Outer)
> Dynamic Hash Filters: root.invention.user_id = root.phone.type_id
>
> 5) root.user: INDEX PATH
>
> Filters: (root.user.lastname LIKE '%' AND (root.user.firstname
> LIKE '%' AND root.user.username LIKE '1297%' ) )
>
> (1) Index Keys: id
> Lower Index Filter: root.user.id = root.invention.user_id
> NESTED LOOP JOIN
>
>
> What I'm hoping to do, is to allow my users to search by any of these
> fields (last name, first name, client number, address, city, state,
> etc....). I suppose that I could rewrite the function that creates the
> query to omit the fields that have nothing in them. However, what I'm
> wondering is if this could be sped up by using:
> a. More / better indexes (how would I know from the output above?) b.
> Multiple sub-queries in a temporary table? c. Something else I'm not
> seeing.
>
> Thanks for your help.
>
> --Anthony
Mr. Kagel and everyone else,
I have, in fact, run the various update statistics on the database &&
table.
My query optimizations rules from onconfig are:
OPTCOMPIND 2
I can get GREAT speed-ups by simply removing the fields that the user
is not searching by (ie, if lastname = null, then don't specify
lastname as lastname LIKE '%', just search for any last name). And
when I mean speed-up, it's a serious speed-up. In milliseconds, I go
from 762987 [for the query I posted] to 9276, [if I omit all of the
WHERE clauses that are composed of only '%'].
However, where possible, I'd like it to be even faster .... about half
(or roughly, 4500 milliseconds) would be ideal. Most of what this
database does is searching [at least, on these tables]. However,
approximately 300 rows are inserted into the user table a day (roughly
less than .05% of the total size of the table, but each phone,
address, and invention have as many as 3X the amount of information in
user).
Therefore, other than update statistics .... what am I missing?
Thanks for the pointers, as always.
--Anthony