Board index » delphi » IB index optimizer

IB index optimizer

Hi!

First, sorry my english.

Problem:

i have table with ~10million records (a log table)

table log: (logTime timestamp, codeA integer, codeB integer, data:
varchar(100))
I have an DESC index log_code_time_desc: (codeA, codeB, logTime)

I use this select:
  select * from log where codeA=44 and codeB=66 order by logTime DESC

this select will not use log_code_time_desc index!

If I use plan:
  select * from log where codeA=44 and codeB=66 plan log
index(log_code_time_desc) order by logTime DESC

then IB Plan is the following:
  PLAN SORT ((LOG INDEX (LOG_CODE_TIME_DESC)))

** My problem is that why use SORT in plan, where index is DESC otherwise?
  This is wery large performace problem, where the table have 10million
record...

Szakly Balzs
szaka...@microraab.hu

 

Re:IB index optimizer


Just alter you order by clause to:

    order by codeA, codeB, logTime

--
Sergio Samayoa
Lgica Software
http://www.geocities.com/logicasw/

Re:IB index optimizer


Szakly,

Well, that is because logTime is a part of a composite index and is not the
initial part of the index. It is the 3rd part in the composite index and hence
can have variying values for the combination of codeA+codeB. Hence, logTime is
(may be) not in DESC order in the index if you take all the rows into
consideration.

Create a DESC index on logTime *only* to get the records in DESC order via an
index, and thereby avoid sorting.

When you force your query to use the index by specifying a PLAN, it is using
it for the codeA=44 WHERE condition.

Best wishes,
Sriram

Quote
"Szakly Balzs" wrote:
> Hi!

> First, sorry my english.

> Problem:

> i have table with ~10million records (a log table)

> table log: (logTime timestamp, codeA integer, codeB integer, data:
> varchar(100))
> I have an DESC index log_code_time_desc: (codeA, codeB, logTime)

> I use this select:
>   select * from log where codeA=44 and codeB=66 order by logTime DESC

> this select will not use log_code_time_desc index!

> If I use plan:
>   select * from log where codeA=44 and codeB=66 plan log
> index(log_code_time_desc) order by logTime DESC

> then IB Plan is the following:
>   PLAN SORT ((LOG INDEX (LOG_CODE_TIME_DESC)))

> ** My problem is that why use SORT in plan, where index is DESC otherwise?
>   This is wery large performace problem, where the table have 10million
> record...

> Szakly Balzs
> szaka...@microraab.hu

--
Sriram Balasubramanian               EMail: bsri...@borland.com
InterBase R&D, Borland
http://www.borland.com
-----------------------------------------------------------------------
Register now for the 12th Annual Borland Conference- July 21-25 in
Long Beach, California. BorCon is the best place to learn about
award winning technologies for implementing e-business. Register today!
http://www.borland.com/conf2001/

This e-mail, and any attachments thereto, is intended only for use by
the addressee(s) named herein and may contain legally privileged and/or
confidential information.  If you are not the intended recipient of
this e-mail, you are hereby notified that any dissemination,
distribution or copying of this e-mail, and any attachments thereto, is
strictly prohibited.  If you have received this e-mail in error, please
immediately and permanently delete the original and any copy of any
e-mail and any printout thereof.

Re:IB index optimizer


Quote
"Sriram Balasubramanian (Borland)" wrote:
> ...
> Create a DESC index on logTime *only* to get the records in DESC order via an
> index, and thereby avoid sorting.

I would try to order _without_ an index on LogTime.  IB sorts quickly,
and using an index may (or may not!) slow the process.

Regards,
Aage J.

Other Threads