Multiple indexes ... are they necessary

[Lester Caine]

I seem to remember that once I have an index on the primary key that removes the need to add further indexes on the same fields, so for example I have 'm_file' and 'm_id' as a primary key on 'media' so there is no need to add a unique index on 'm_id' and 'm_file' and it does actually fail as a duplicate. 
The webtrees schema has a lot of what I consider to be duplicate indexes and I think that they would not provide any speed improvements if they existed in firebird. For example one copy of $table->unique(['parent_id', 'place']); and $table->unique(['place', 'parent_id']); indexes will be used to find both 'parent_id' and 'places'?

[Dimitry Sibiryakov]

Lester Caine wrote 05.02.2026 11:41:
> The webtrees schema has a lot of what I consider to be duplicate indexes and I
> think that they would not provide any speed improvements if they existed in
> firebird. For example one copy of $table->unique(['parent_id', 'place']); and
> $table->unique(['place', 'parent_id']); indexes will be used to find both
> 'parent_id' and 'places'?

Indexes are not to be created "for fun" or "just in case". Indexes are
created for exact purposes: speed up exact queries of enforce exact integrity
constraints. You must very carefully study nature and usage of data in a
database to make decision about creation of indexes.

In your example both indexes enforces the same constraint and speed up a
query looking for exact match, but they are different if a query search for
ranges of values and use only part of index.

--
WBR, SD.

[Lester Caine]

The key problem I'm trying to solve here is that a number of the extra indexes that webtrees is trying to add fail because an index already exists for those fields. In the case of the second 'parent_id' and 'places' index, I've commented it out in the schema as I have with various other 'duplicates'. I KNOW that some searches benefit from additional indexes, and also perhaps indexes in the opposite direction, but as far as I am aware, firebird will use a more general index better than some other engines? At least that is what I was working with back in FB2 and 3 days.

[Mark Rotteveel]

On 05/02/2026 11:41, Lester Caine wrote:
> I seem to remember that once I have an index on the primary key that
> removes the need to add further indexes on the same fields, so for
> example I have 'm_file' and 'm_id' as a primary key on 'media' so there
> is no need to add a unique index on 'm_id' and 'm_file' and it does
> actually fail as a duplicate.

I'm not sure I understand your question correctly.

Having a primary key (m_file, m_id) does not remove the need for an
index (m_id) if you need to look up on m_id. It can prevent the need for
an index (m_file), as Firebird can search the prefix of the primary key
index (it cannot search the suffix).

Additionally, defining a _unique_ index (m_id) and _unique_ index
(m_file) achieves something else from the primary: instead of requiring
that the combination of (m_file, m_id) is unique, it requires that
m_file and m_id are individually unique. To me would that suggest that
maybe the primary key should be either (m_file) or (m_id), not (m_file,
m_id).

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 05/02/2026 13:55, 'Mark Rotteveel' via firebird-support wrote:
> Additionally, defining a _unique_ index (m_id) and _unique_ index
> (m_file) achieves something else from the primary: instead of requiring
> that the combination of (m_file, m_id) is unique, it requires that
> m_file and m_id are individually unique. To me would that suggest that
> maybe the primary key should be either (m_file) or (m_id), not (m_file,
> m_id).

Although it can also imply it is a 1:1 link table for entities that may
should have been a single table.

Mark
--
Mark Rotteveel

[Lester Caine]

Although it can also imply it is a 1:1 link table for entities that may
should have been a single table. 

Half of the problem is the fact that the duff choices have now been sorted out, but the Illuminate's migrate process is designed to bring any old build up to the version that is actually running. The current schema is pretty good but still has links that were a bad choice back in the days when webtrees ran on ADOdb rather than Laravel. The media stuff in webtrees is not my favourite solution to the problem but it did at least import all of the media from the phpgedview original!

My bitweaver PHP website software plays better with different databases simply because ADOdb has a larger footprint of drivers to support and even allows stuff that ran on the generic firebird driver to use the PDO firebird driver instead with no other changes and extra indexes are added only if needed.