Additional DB indices for Koha?
Hi :) Has there already been the need of additional DB indices for Koha? To speed up some queries that doesn't use a primary or foreign key. From what I see in kohastructure.sql , there is no index besides the implicit ones for keys. Cheers, -- Victor Grousset/tuxayo
Hi, There has been this on (by me) : Bug 3445: add more indexes on the action_logs table Best regards Le 21/03/2022 à 07:40, Victor Grousset/tuxayo a écrit :
Hi :)
Has there already been the need of additional DB indices for Koha? To speed up some queries that doesn't use a primary or foreign key.
From what I see in kohastructure.sql , there is no index besides the implicit ones for keys.
Cheers,
-- Fridolin SOMERS <fridolin.somers@biblibre.com> Software and system maintainer 🦄 BibLibre, France
We have lots of indexes. KEY and INDEX are basically synonyms in MySQL. We in fact might have too many indexes defined. This is a valid hypothesis when the index size (on disk) is too close to the DB size for a table. El lun, 21 mar 2022 a las 14:40, Victor Grousset/tuxayo (<victor@tuxayo.net>) escribió:
Hi :)
Has there already been the need of additional DB indices for Koha? To speed up some queries that doesn't use a primary or foreign key.
From what I see in kohastructure.sql , there is no index besides the implicit ones for keys.
Cheers,
-- Victor Grousset/tuxayo _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : https://www.koha-community.org/ git : https://git.koha-community.org/ bugs : https://bugs.koha-community.org/
-- Tomás Cohen Arazi Theke Solutions (http://theke.io) ✆ +54 9351 3513384 GPG: B2F3C15F
Ah we use KEY to add indices! Thanks Fridolin and Tomas. On 22-03-22 00:13, Tomas Cohen Arazi wrote:
We in fact might have too many indexes defined. This is a valid hypothesis when the index size (on disk) is too close to the DB size for a table.
That might also slow down inserts and updates or is that not significant? Cheers, -- Victor/tuxayo
Hi all, Indexes and in generally speaking database performance tuning is quite complex in some cases and is very related with your system, data, size of customers, etc. It is better to look at every system specifically. In general, it is not true that indexes give performance everytime. I may see, in many cases, indexes become a big issue in lots of databases after a while. As you mentioned, in some cases insert,updates can be quite slow in some situations due to indexes. On 22.03.2022 02:43, Victor/tuxayo wrote:
Ah we use KEY to add indices! Thanks Fridolin and Tomas.
On 22-03-22 00:13, Tomas Cohen Arazi wrote:
We in fact might have too many indexes defined. This is a valid hypothesis when the index size (on disk) is too close to the DB size for a table.
That might also slow down inserts and updates or is that not significant?
Cheers,
We add some issues with indexes on timestamp. 1 lines never have the exact same timestamp to the index is never efficient right ? Best regards, Le 23/03/2022 à 10:12, DevinimKoha a écrit :
Hi all,
Indexes and in generally speaking database performance tuning is quite complex in some cases and is very related with your system, data, size of customers, etc.
It is better to look at every system specifically.
In general, it is not true that indexes give performance everytime. I may see, in many cases, indexes become a big issue in lots of databases after a while.
As you mentioned, in some cases insert,updates can be quite slow in some situations due to indexes.
On 22.03.2022 02:43, Victor/tuxayo wrote:
Ah we use KEY to add indices! Thanks Fridolin and Tomas.
On 22-03-22 00:13, Tomas Cohen Arazi wrote:
We in fact might have too many indexes defined. This is a valid hypothesis when the index size (on disk) is too close to the DB size for a table.
That might also slow down inserts and updates or is that not significant?
Cheers,
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : https://www.koha-community.org/ git : https://git.koha-community.org/ bugs : https://bugs.koha-community.org/
-- Fridolin SOMERS <fridolin.somers@biblibre.com> Software and system maintainer 🦄 BibLibre, France
Actually, having an index on a timestamp column can make searching much more efficient, because the indexed data is stored in sorted order by timestamp. That makes it quicker and easier to use a WHERE clause like "WHERE timestamp > date1". It can just do a binary search which is very efficient. Otherwise, the database needs to brute force the search by looping through every row, sorting the whole dataset by the timestamp, and then checking the condition. When you get into millions of rows and/or gigabytes of timestamped data, you really really want an index on that column. (Note: PostgreSQL is much better than MySQL for demonstrating this using the "EXPLAIN" and "ANALYZE" keywords, although it looks like MariaDB supports "ANALYZE" since 10.1.0 for evaluating queries using the same output format as "EXPLAIN"). In general, if the table has a small amount of data, there's no advantage adding an index (unless it's something like a UNIQUE index). If the table is large or likely to grow, it can be useful to add an index to fields that will be used for searching: - Tables like action_logs, old_issues, old_reserves, statistics, accountlines, linktracker log transactional data forever, so they're good to index as they'll grow over time and querying those tables will become more burdensome over time. Tables like items and biblio* probably make sense, because they could potentially be large. - A small table like "letter", it probably doesn't need any additional indexes besides ones for foreign keys and unique indexes. (Funny that I randomly chose this table since Jonathan and Martin have recently updated the DB structure for this table along those lines.) Of course, even with indexes, sometimes a query can request too much data, and it'll cause performance problems. The "Reports" module is dangerous like that actually. But that's a whole other thing... Btw, if you want to see what indexes are defined for a MySQL table, you can just use "SHOW INDEXES from tablename". In MySQL, you can also use non-standard "index hints": ( E.g. ANALYZE SELECT * FROM biblio FORCE INDEX (blbnoidx) WHERE biblionumber > 1; ). Generally this isn't necessary, but I have used it from time to time for specific MySQL SQL reports. Anyway, that's my little rant on indexes 😅. David Cook Senior Software Engineer Prosentient Systems Suite 7.03 6a Glen St Milsons Point NSW 2061 Australia Office: 02 9212 0899 Online: 02 8005 0595 -----Original Message----- From: Koha-devel <koha-devel-bounces@lists.koha-community.org> On Behalf Of Fridolin SOMERS Sent: Thursday, 24 March 2022 7:17 AM To: koha-devel@lists.koha-community.org Subject: Re: [Koha-devel] Additional DB indices for Koha? We add some issues with indexes on timestamp. 1 lines never have the exact same timestamp to the index is never efficient right ? Best regards, Le 23/03/2022 à 10:12, DevinimKoha a écrit :
Hi all,
Indexes and in generally speaking database performance tuning is quite complex in some cases and is very related with your system, data, size of customers, etc.
It is better to look at every system specifically.
In general, it is not true that indexes give performance everytime. I may see, in many cases, indexes become a big issue in lots of databases after a while.
As you mentioned, in some cases insert,updates can be quite slow in some situations due to indexes.
On 22.03.2022 02:43, Victor/tuxayo wrote:
Ah we use KEY to add indices! Thanks Fridolin and Tomas.
On 22-03-22 00:13, Tomas Cohen Arazi wrote:
We in fact might have too many indexes defined. This is a valid hypothesis when the index size (on disk) is too close to the DB size for a table.
That might also slow down inserts and updates or is that not significant?
Cheers,
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : https://www.koha-community.org/ git : https://git.koha-community.org/ bugs : https://bugs.koha-community.org/
-- Fridolin SOMERS <fridolin.somers@biblibre.com> Software and system maintainer 🦄 BibLibre, France _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : https://www.koha-community.org/ git : https://git.koha-community.org/ bugs : https://bugs.koha-community.org/
participants (6)
-
dcook＠prosentient.com.au -
DevinimKoha -
Fridolin SOMERS -
Tomas Cohen Arazi -
Victor Grousset/tuxayo -
Victor/tuxayo