performance and left join

hi there,

we are using more and more flexicontent. for smaller projects it s just amazing, once the projects gets bigger (lot s of content) we meet sometimes performance problems.

i m not the core-programmer, but after some research, i found out that the afterdispatch time can be optimised by replacing left join by inner join.
we did a test on some flexi-mysqls and the time is 100x smaller (3s -> 0,03 s).
is there a reason why flexicontent sticks with left join?

cheers
bart

It is not exactly an SQL performance issue but rather it is that you get a lot less data?


a left join will return data if even if "right data" are not matched, while a INNER join (same as just 'JOIN') will not return the rows that do not have a right matching record

e.g. a LEFT join could return 10000 records while the INNER JOIN could return 100

Up to executing the query performance should be about same, then the difference starts

1. You ask mysql to fetch 100 items instead of 10000 records and
2. You use PHP to process 100 items instead of 10000 records,

I have done extensive profiling to finds bottlenecks, we have improve most of them
-- e.g. our category view is 3x-20x faster than v1.5.4 depending on number of items.
-- e.g. made (new!!) changes (compared to r1601) to building Basic and Advanced in 5x-15x times faster (along with user friendliness and full faceted search filtering !!!)

Some other have been identified , but are left for next version.

About what you ask , we should certainly use a INNER JOIN if this is desirable , have you spotted a specific places where INNER JOIN is the --wanted behavior-- ??

the performance issue is on the category view:
we have different sites where more then 100 items has to displayed on the page, which of course slows down the page.

but in our latest project we changed all left joins from
/component/com_flexicontent/models/category.php into inner join, and it give it a boost & all data seems seems to be there.

interseting return

In general using an (inner) JOIN is preferable because it allows MySQL to optimize which table to select first for matching, but it is strange the difference that you see in the performance it is not normal, are your sure that you are getting the same results? or that these are the only things that you changed?

Some LEFT JOIN could be changed to INNER,
but not all, please consider this,

- let's say you have a user called userA that owns 50000 items (articles),

- let's say that you delete userA,

Your changed query will never return the items (articles) own by (now deleted) userA