Re: Searching a database .... query optimization (how do I read the explain on output?)

I've seen code like this generated by PeopleSoft - I'm sure it's portable and generic and all, but it runs like a two-legged dog. Index efficiency is severely reduced with leading '%' in LIKE predicates - this is documented, prob in SQL Ref or Perf Tuning Manual (sorry, not near the fine manuals at the moment). If you have the ability to manipulate the SQL to remove the redundant predicates, then do it. As you've already ascertained, it makes a huge difference. Hope that helps, RET On Friday, July 4, 2003, at 01:08 AM, Anthony Presley wrote: > 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 > > ______________________________________________________ "Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so." - Douglas Adams sending to informix-list

I've improved the pre-query optimizer, which was, as you noted, "portable and generic and all", but runs very much like a two-legged dog. [Side note: I like Informix ... and not to start a war, but have noticed that for MY particular needs, the newest Postgres w/ JasperReports [recent advances have solidified this technology in my book] works just as well as commercial Informix]. It now removes the preceding %'s, unless the user wants to wait for it, and only searches for what is requested (and ignoring the rest). My 20+ seconds is now about 3 seconds. However, that's still a LITTLE too long for me. I haven't yet finished up the index fixing's for the SEQUENTIAL SCAN's .... but will post back when I have (long two weeks). In the meantime, am I missing it, but is there a way to have Informix EXPLAIN every query that it processes? IE, I'd like it to spit out the explain on every one of the queries that we have running against it .... so I can take a look in a week or so, and see what I need to index, or not, or split queries, etc.... Thanks. --Anthony Richard Thomas <ret@mac.com> wrote in message news:<benvvo$185$1@terabinaries.xmission.com>... > I've seen code like this generated by PeopleSoft - I'm sure it's > portable and generic and all, but it runs like a two-legged dog. Index > efficiency is severely reduced with leading '%' in LIKE predicates - > this is documented, prob in SQL Ref or Perf Tuning Manual (sorry, not > near the fine manuals at the moment). If you have the ability to > manipulate the SQL to remove the redundant predicates, then do it. As > you've already ascertained, it makes a huge difference. > > Hope that helps, > RET > > On Friday, July 4, 2003, at 01:08 AM, Anthony Presley wrote: > > > 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 > > > > > ______________________________________________________ > "Human beings, who are almost unique in having the ability to learn > from the experience of others, are also remarkable for their apparent > disinclination to do so." - Douglas Adams > > sending to informix-list