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
