2016-07-12 17:43 GMT+01:00 Paul Poulain <paul.poul...@biblibre.com>: > Hi all,
Hi Paul, > Those days, we're working on a pretty large DB ( >1M biblio), for a customer > that want to do many statistics on some fields. > We discovered that something "simple" like: > SELECT publicationyear, count(publicationyear) FROM biblioitems GROUP BY > publicationyear; > > was giving no result in 10mn. > This is a test DB, not optimized, but we were surprised by the results. > After investigating we had the idea to create a biblioitems2 table with the > same structure EXCEPT MARCXML and MARC fields > > launch the same SQL query : result in 3seconds ! > This could be reproduced on any query (on fields without index). > > I think it's because the innoDB is storing each line in one "object", so, > even if you need only one column, you have to read everything. > In our case, that was 12GB+ of data to read. > biblioitems2 is just a few dozen MB. > (all caching values are minimum and there's no index, so not involved in the > results) > > MY CONCLUSIONS: > * the biblioitems.marc field must be removed quickly: it's useless since > years, and is only resulting in slowing things Yep, see bug 10455 and argue in this direction :) I am just waiting for a go to submit a patch (I have already some changes on a old local branch). > * the bilbioitems.marcxml field should be moved outside from this table. > Something like biblio_blob, with biblionumber, biblioitemnumber and marcxml. > When we need it, just join the tables. > > I'm almost sure it would have an important impact on Koha, as biblioitems > table is called and used "everywhere". Let's remove biblioitems? Ok it won't fix the issue, but let's remove it anyway :p > any opinion ? Did you test the same structure removing marcxml only and then removing marc only? Just out of curiosity :) > -- > Paul Poulain, Associé-gérant / co-owner > BibLibre, Services en logiciels libres pour les bibliothèques > BibLibre, Open Source software and services for libraries > > > _______________________________________________ > Koha-devel mailing list > Koha-devel@lists.koha-community.org > http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel > website : http://www.koha-community.org/ > git : http://git.koha-community.org/ > bugs : http://bugs.koha-community.org/ _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/