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