Hi Marc! Can you please try with 2.4.0? Is the generated query the same or without the column?
LieGrue, strub On Saturday, 10 September 2016, 2:14, Marc Logemann <[email protected]> wrote: > > >Hi, > >i suspect i found a bug which has bad consequences on MariaDB not using an >index anymore. Lets take this JPAQL: > >select d from Distribution d join fetch d.distributionContainerList where >d.client = ?1 and d.deleted = ?2 order by d.oid desc > >My Distribution entity is quite big when it comes to 1:n relations and >stuff. So i wont get into the details here, but this JPAQL will result in >the following SQL (compressed because too big otherwise): > >SELECT t0.oid, t0.jpaversion, t0.created, t0.createdby, ... > FROM distribution t0 LEFT OUTER JOIN dist_altfrom t1 ON t0.altfrom_oid >= t1.oid > LEFT OUTER JOIN clients t3 ON t0.client_oid = t3.oid LEFT OUTER >JOIN > cmrcarrier t17 ON t0.cmrcarrier_oid = t17.oid LEFT OUTER JOIN >countries > t20 ON t0.empf_country = t20.isocode2 LEFT OUTER JOIN users t21 ON > t0.user_oid = t21.oid INNER JOIN dist_container t24 ON t0.oid = > t24.distribution_oid LEFT OUTER JOIN countries t2 ON t1.country = > t2.isocode2 LEFT OUTER JOIN address t4 ON t3.address_oid = t4.oid >LEFT > OUTER JOIN bankaccount t6 ON t3.bankaccount_oid = t6.oid LEFT OUTER > JOIN communication t7 ON t3.communication_oid = t7.oid LEFT OUTER >JOIN > persons t8 ON t3.cperson_oid = t8.oid LEFT OUTER JOIN bankaccount >t10 > ON t3.nnbankaccount_oid = t10.oid LEFT OUTER JOIN workplaces t11 ON > t3.workplaceoid = t11.oid LEFT OUTER JOIN address t18 ON > t17.address_oid = t18.oid LEFT OUTER JOIN communication t19 ON > t17.communication_oid = t19.oid LEFT OUTER JOIN persons t22 ON > t21.person_oid = t22.oid LEFT OUTER JOIN workplaces t23 ON > t21.workplaceoid = t23.oid LEFT OUTER JOIN distribution t25 ON > t24.old_distribution_oid = t25.oid LEFT OUTER JOIN countries t5 ON > t4.country_id = t5.isocode2 LEFT OUTER JOIN communication t9 ON > t8.communication_oid = t9.oid LEFT OUTER JOIN balance t12 ON > t11.balance = t12.oid LEFT OUTER JOIN balance t13 ON t11.balance2 = > t13.oid LEFT OUTER JOIN clients t14 ON t11.client_oid = t14.oid >LEFT > OUTER JOIN printer t15 ON t11.labelprinter = t15.oid LEFT OUTER >JOIN > printer t16 ON t11.laserprinter = t16.oid > WHERE (t0.client_oid = ? AND t0.deleted = ?) > ORDER BY t0.oid DESC, t24.distribution_oid ASC LIMIT ?, ? > > >Just look at the order by clause in the SQL. It correctly used t0.oid >because JPAQL said so. But why on earth is there another order clause with >the field "t24.distribution_oid" ?? This is a back reference for a 1:n >relation from Table "dist_container" back to "distribution". > >The real problem is: as soon as there is another sorting parameter from a >joined table, MariaDB doesnt use my ForeignKey Index anymore and does a >FULL-Scan on the t24 table, which is pretty heavy on a multi million >records table. When i leave out that ugly t24.distribution_oid ordering >field, everything is fast and ok. > >Can anyone explain to me how this ordering field gets into the picture? > >thanks >marc > > >
