<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Segoe UI Symbol";
panose-1:2 11 5 2 4 2 4 2 2 3;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-AU link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><a name="_MailEndCompose"><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>+1 from me too<o:p></o:p></span></a></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>It’ll hopefully go a long way to moving away from MARC in the long-term too…<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>David Cook<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Systems Librarian<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Prosentient Systems<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>72/330 Wattle St<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Ultimo, NSW 2007<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Office: 02 9212 0899<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Direct: 02 8005 0595<o:p></o:p></span></p></div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri",sans-serif'> koha-devel-bounces@lists.koha-community.org [mailto:koha-devel-bounces@lists.koha-community.org] <b>On Behalf Of </b>Chris Cormack<br><b>Sent:</b> Wednesday, 13 July 2016 4:43 AM<br><b>To:</b> koha-devel@lists.koha-community.org<br><b>Subject:</b> Re: [Koha-devel] biblioitems.marcxml & biblioitems.marc / HUGE performance issue !<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='margin-bottom:12.0pt'>+1 from me<o:p></o:p></p><div><p class=MsoNormal>On 13 July 2016 5:11:36 AM NZST, Tomas Cohen Arazi <<a href="mailto:tomascohen@gmail.com">tomascohen@gmail.com</a>> wrote:<o:p></o:p></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm'><div><p class=MsoNormal>I agree 100%, but I'd go for a metadata_record table with schema, (id), biblionumber, format and metadata columns to start supporting more and more schemas. Example:<o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>| id | format | schema | metadata<o:p></o:p></p></div><div><p class=MsoNormal>| 1 | marcxml | marc21 | ... <o:p></o:p></p></div><div><p class=MsoNormal>| 2 | usmarc | unimarc | ...<o:p></o:p></p></div><div><p class=MsoNormal>| 3 | mij | marc21 | ...<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>pretty much like we do with Koha::MetadataRecord actually :-D<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Nice catch, Paul!<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>El mar., 12 jul. 2016 a las 13:43, Paul Poulain (<<a href="mailto:paul.poulain@biblibre.com">paul.poulain@biblibre.com</a>>) escribió:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm'><div><p class=MsoNormal>Hi all,<br><br>Those days, we're working on a pretty large DB ( >1M biblio), for a customer that want to do many statistics on some fields.<br>We discovered that something "simple" like:<br>SELECT publicationyear, count(publicationyear) FROM biblioitems GROUP BY publicationyear;<br><br>was giving <b>no result in 10mn</b>.<br>This is a test DB, not optimized, but we were surprised by the results.<br>After investigating we had the idea to create a biblioitems2 table with the same structure <b>EXCEPT MARCXML and MARC fields<br><br></b>launch the same SQL query : <b>result in 3seconds</b> !<b><br></b>This could be reproduced on any query (on fields without index).<br><br>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.<br>In our case, that was 12GB+ of data to read.<br>biblioitems2 is just a few dozen MB.<br>(all caching values are minimum and there's no index, so not involved in the results)<br><br>MY CONCLUSIONS:<br> * the biblioitems.marc field must be removed quickly: it's useless since years, and is only resulting in slowing things<br> * 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.<br><br>I'm almost sure it would have an important impact on Koha, as biblioitems table is called and used "everywhere".<br><br>any opinion ?<br><br><o:p></o:p></p><pre>-- <o:p></o:p></pre><pre>Paul Poulain, Associé-gérant / co-owner<o:p></o:p></pre><pre>BibLibre, Services en logiciels libres pour les bibliothèques<o:p></o:p></pre><pre>BibLibre, Open Source software and services for libraries<o:p></o:p></pre></div><p class=MsoNormal>_______________________________________________<br>Koha-devel mailing list<br><a href="mailto:Koha-devel@lists.koha-community.org" target="_blank">Koha-devel@lists.koha-community.org</a><br><a href="http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel" target="_blank">http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel</a><br>website : <a href="http://www.koha-community.org/" target="_blank">http://www.koha-community.org/</a><br>git : <a href="http://git.koha-community.org/" target="_blank">http://git.koha-community.org/</a><br>bugs : <a href="http://bugs.koha-community.org/" target="_blank">http://bugs.koha-community.org/</a><o:p></o:p></p></blockquote></div><div><p class=MsoNormal>-- <o:p></o:p></p></div><div><div><div><p class=MsoNormal><span style='font-size:9.5pt;font-family:"Helvetica",sans-serif;color:#757575'>Tomás Cohen Arazi<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:9.5pt;font-family:"Helvetica",sans-serif;color:#757575'>Theke Solutions (<a href="http://theke.io/">https://theke.io</a>)<br></span><span style='font-size:9.5pt;font-family:"Segoe UI Symbol",sans-serif;color:#757575'>✆</span><span style='font-size:9.5pt;font-family:"Helvetica",sans-serif;color:#757575'> +54 9351 3513384<br>GPG: B2F3C15F<o:p></o:p></span></p></div></div></div><pre style='text-align:center'><hr size=2 width="100%" align=center></pre><pre><br>Koha-devel mailing list<br><a href="mailto:Koha-devel@lists.koha-community.org">Koha-devel@lists.koha-community.org</a><br><a href="http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel">http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel</a><br>website : <a href="http://www.koha-community.org">http://www.koha-community.org</a>/<br>git : <a href="http://git.koha-community.org">http://git.koha-community.org</a>/<br>bugs : <a href="http://bugs.koha-community.org/">http://bugs.koha-community.org/</a><o:p></o:p></pre></blockquote></div><p class=MsoNormal><br>-- <br>Sent from my Android device with K-9 Mail. Please excuse my brevity.<o:p></o:p></p></div></div></body></html>