[Koha-devel] Additional DB indices for Koha?

[dcook at prosentient.com.au]

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 at lists.koha-community.org> On Behalf Of Fridolin SOMERS
Sent: Thursday, 24 March 2022 7:17 AM
To: koha-devel at 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 at 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 at biblibre.com> Software and system maintainer 🦄
BibLibre, France
_______________________________________________
Koha-devel mailing list
Koha-devel at 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/