Hi Stefan!

First, although it isn't the most complicated SQL I've ever seen (although it may be amongst the most unnecessarily complicated SQL I've seen), I do think that you should insist on fixing one bit that Sean points out a few places:

> FROM User_ User__1
> JOIN BannedObjects_User BannedObje_1 ON (BannedObje_1.bannedUsers =
> User__1.BOLD_ID), Document Document_1, BusinessClassesRoot BusinessCl_1

This is mixing the way JOINS are done in SQL-89 (implicit joins) with the way they're done in SQL-92 (explicit use of JOIN). You should either change this to all SQL-92 (like below) or stick to SQL-89 by avoid using the JOIN word altogether (admittedly, I don't quite remember why, but I think mixing the way of joining, at least was a no-no, although it is not related to your current problem). If done by an automated engine, then why doesn't the engine produce sensible code? Here's the preferred SQL-92 version:

NOT EXISTS(SELECT User__1.BOLD_ID FROM User_ User__1
JOIN BannedObjects_User BannedObje_1 ON (BannedObje_1.bannedUsers = User__1.BOLD_ID)
JOIN Document Document_1 ON BannedObje_1.bannedObjects = Document_1.BOLD_ID
JOIN BusinessClassesRoot BusinessCl_1 ON User__1.BOLD_ID = BusinessCl_1.BOLD_ID
WHERE IwadisObje_1.BOLD_ID = Document_1.BOLD_ID
AND BusinessCl_1.iwadisID = '50C3B73C-095B-439C-BFAF-1BE6EC0BBD2E')

And here is the somewhat less ideal, but still OK SQL-89 equivalent:

NOT EXISTS(SELECT User__1.BOLD_ID
FROM User_ User__1, BannedObjects_User BannedObje_1, Document Document_1, BusinessClassesRoot BusinessCl_1
WHERE IwadisObje_1.BOLD_ID = Document_1.BOLD_ID
AND BusinessCl_1.iwadisID = '50C3B73C-095B-439C-BFAF-1BE6EC0BBD2E'
AND BannedObje_1.bannedUsers = User__1.BOLD_ID
AND BannedObje_1.bannedObjects = Document_1.BOLD_ID
AND User__1.BOLD_ID = BusinessCl_1.BOLD_ID)

I also reacted to something Sean didn't mention, your 'NOT(((SELECT COUNT...) > 0))' as opposed to a simple NOT EXISTS with less parenthesis, but that's just something unnecessarily complicated, time consuming (counting a lot of records can take time) and making it more difficult for a new person to understand the query, and isn't something that must be changed.

As for the current problem, my GUESS (I would understand more if I'd known the index definition, but haven't got time to put your DB on this computer at the moment) is that the IDXWORD1 index is an index on the selective DATA field and that BOLD_ID is quite selective for IwadisObject, whereas the IDXWORD3 index is not very selective, and possibly only referring to the ATTRIBUTE field?

You can prevent the bad plan (hopefully more or less 'forcing' the preferred plan, although the optimizer can of course choose another bad plan) by adding zero:

IwadisObje_1.BOLD_TYPE+0 IN (122)

The alternative is of course to remove the BOLD_TYPE index altogether, but I suppose that could affect other queries and lead to more trouble. So, if you cannot change the SQL at all and removing the index isn't appropriate, then I don't think there is any quick solution for you.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of swestner
Sent: 28. april 2008 05:41
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Indexed used with search name='TEST' but not with name like 'TEST%'

Hello Farias and Sean,

thanks for your work!

I tested the select you posted and it has exactly the described
problem at execution time. Your right that the problem could reduced
to this select.

Plan:
PLAN JOIN (IWADISOBJE_1 INDEX (IDXIWADISOBJECT1), WORD_1 INDEX
(IDXWORD3))

Prepare time: 1 ms
Execution time: 490640 ms
Fetch time: 2319 ms


- how many entries are in the entire IwadisObject table
77225
- how many entries have IwadisObject BOLD_TYPE IN (122)
52281
- how many entries are in the whole Word table
9666470
- how many Word table entries have Word_1.attribute = 'NAME'
332000
- how many Word table entries have Word_1.data LIKE 'ANFRAGE%'
1715
- how many Word table entries have Word_1.attribute = 'NAME' *AND*
Word_1.data LIKE 'ANFRAGE%'
295


To get the above record-count I did a select count and all select
runs in milliseconds even the select count(data) from word where data
like 'ANFRAGE%'. Firebird uses the index for that select to so index
for LIKE ist used but not in the 'problem'-select from the
beginning....

Im out at customer today and will be back in 12 hours and could test
more if you need more informations.

Thanks

Stefan


--- In firebird-support@yahoogroups.com, "Leyne, Sean" <Sean@...>
wrote:

>
>
> > >This is the single most complicated SQL statement that I have

seen

> > > through my 14 years of using/supporting IB/FB!!!
> >
> > Layne, I *think* that for the problem being, we can reduce the

SQL to

> >
> > SELECT IwadisObje_1.BOLD_ID, IwadisObje_1.BOLD_TYPE
> > FROM IwadisObject IwadisObje_1
> > JOIN Word Word_1 ON (IwadisObje_1.BOLD_ID = Word_1.indexedObject)
> > WHERE ((Word_1.attribute = 'NAME') and (Word_1.data

LIKE 'ANFRAGE%'))

> > AND (IwadisObje_1.BOLD_TYPE IN (122))
>
> I agree this SQL is a good test for the problem at hand.
>
>
> > Stefan, could you test this against your data and confirm if it
> reproduces
> > the same bad behavior?
>
> I'd also like to know:
>
> - how many entries are in the entire IwadisObject table
>
> - how many entries have IwadisObject BOLD_TYPE IN (122)
>
> - how many entries are in the whole Word table
>
> - how many Word table entries have Word_1.attribute = 'NAME'
>
> - how many Word table entries have Word_1.data LIKE 'ANFRAGE%'
>
> - how many Word table entries have Word_1.attribute = 'NAME' *AND*
> Word_1.data LIKE 'ANFRAGE%'
>
>
> Sean
>

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links