gregor_m2 wrote:

> I would like to ask You about optimization of select
> statements. To simplify the problem we assume there are three tables
> created as follows: <see below>
>
> Now we are selecting from the persons table as follows:
>
> SELECT p.ID, fn.Name as FirstName, ln.Name as LastName
> FROM Persons P
> JOIN FirstNames fn ON fn.ID = p.FirstName_ID
> JOIN LastNames ln ON ln.ID = p.LastName_ID
> ORDER BY ln.Name, fn.Name
>
> As we see there are indexes on names in both FirstNames and LastName
> tables. How should the optimized PLAN clause look like to cause the
> select statement to use unique indexes of FirstNames and LastNames
> tables ?

Since you have no qualifications on FirstName or LastName, those
indexes can't be used.

Indexes are for locating records using the contents of the WHERE
and ON clauses. Unless you use the indexed fields in a WHERE or
ON clause, the index won't be used. Indexes are not a faster
alternative to sorting. The cost of retrieving records from disk
in random order is generally higher than the cost of retrieving
them in storage order and performing an in-memory sort.

The goal of the optimizer is to minimize the number of records
read from disk. In your case the fastest way is to read all
the Persons records, look up FirstNames and LastNames by primary
key and sort the result.



Regards,


ann

> CREATE Table FirstNames (
> ID Smallint not null,
> Name varchar(16) not null,
> Primary Key (ID),
> Unique (Name));
>
> CREATE Table LastNames (
> ID Integer not null,
> Name varchar(32) not null,
> Primary Key (ID),
> Unique (Name));
>
> CREATE Table Persons (
> ID Integer not null,
> FirstName_ID SmallInt not null,
> LastName_ID Integer not null,
> Primary Key (ID),
> Unique (FirstName_ID, LastName_ID));
>
> Now we are selecting from the persons table as follows:
>
> SELECT p.ID, fn.Name as FirstName, ln.Name as LastName
> FROM Persons P
> JOIN FirstNames fn ON fn.ID = p.FirstName_ID
> JOIN LastNames ln ON ln.ID = p.LastName_ID
> ORDER BY ln.Name, fn.Name
>
> As we see there are indexes on names in both FirstNames and LastName
> tables. How should the optimized PLAN clause look like to cause the
> select statement to use unique indexes of FirstNames and LastNames
> tables ?
>
> Thanks for any tip in advance.
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>