Subject Re: [ib-support] Firebird - slow queries
Author Helen Borrie
At 03:57 PM 13-05-02 +0200, you wrote:
>First of all: I don't want to start yet another "My fav RBDMS is the best"
>and a "xxx RBSMS is better" debate.
>I yust want to hear your opinions in my case.
>The server: a linux box, 512 ram, p-III-600, lots of disk space.
>Database servers: Postgres 7.2, mysql. 3.22, Firebird 1.0
>All freshly installed, only with the test database
>I tested the performance of the above three RBDMS to see
>which one is best suited to our needs.
>We will NOT use mysql, since it lacks some important functions,
>but I kept it as a reference.
>There were two tables in the database:
>table t1
> t1_id: autoincrementing field, PK w. index
> last int : timestamp
> desctxt varchar (254): description
>table t2
> t2_id autoincrementing field, PK, w. index
> t1_id int: a reference
> data1 float
> data2 float

[ snip weird results ]

>So I want to ask: where to look for this low performance?
>The interbase interface in PHP is broken?
>Or there is another issue I am not aware of?

Possibly an issue you are not aware of is that you should NOT define an
index for your primary key - nor for a foreign key. When you apply the PK
constraint to it, the index gets created automatically. If you then go and
create another identical index, you confuse the optimizer, and it will
(usually) use no index for your sorts and joins.

On the other hand, you didn't mention creating a foreign key for your
join. If not, then an index on the reference column should speed up that
joined query.

The speed you reported sounds like a lame index problem. Get hold of a
tool such as IB_SQL and see what the plans look like for these queries.


All for Open and Open for All
Firebird Open SQL Database · ·