Hi Csthomas, got huge problem to make work Xampp or Wampp on windows 10, Georgios Papadakis got a copy of my akeeba backup he will try to extract and if the archive is not corrupt he will test the patch too, thanks a lot to all. f.

Not sure i've full understand point 1)

11,000 artilces in a category with DataBase on SSD

3.6 seconds becomes 3 seconds

but the website does not use Tags,
this is improvement is meant for websites with Tags, right ?

Also this is not the slowest query in the website
... the 'numitems' thing (counting items when getting the category) is the slowest query
it is about 6 seconds

i will test your other PR #19261
it should be improving on the issue

but the website does not use Tags,
this is improvement is meant for websites with Tags, right ?

Yes,

I added one more commit at the moment but it is not important for performance.

Maybe adding one or two tags manually in phpmyadmin to almost all articles will help in testing.

You can test it by adding tags to category menu item or directly to link like: /menu-to-category?filter_tag={id-of-tag} or /menu-to-category?filter_tag[0]={id-of-tag}&filter_tag[1]={id-of-tag2}

The issue is described at https://www.itoctopus.com/on-the-careless-usage-of-mysql-distinct-in-joomla-com-content

@csthomas

I mean this line with 'tag' is IMO useless. I found a bug in my PR, there are duplicated articles.

Do you mean that this PR needs more work ?,
or you meant that after removing it you fix the duplicate articles issue created by the PR ?

I mean this line with 'tag' is IMO useless.

I removed line 55. That all.

I found a bug in my PR, there are duplicated articles.

It is fixed now. I added DISTINCT to subquery.

Do you mean that this PR needs more work ?,
It is ready to test.

The optimization / fix of this PR is good
but it is not enough

The issue is described at https://www.itoctopus.com/on-the-careless-usage-of-mysql-distinct-in-joomla-com-content

The article is partly wrong

• the problem is not DISTINCT itself,

the duplicate added rows together with DISTINCT to remove them, only multiplied the problem,
the problem is

• the size of the SELECT
• plus SELECT has columns that are repeated thousands of times like author name, for no good reason, other than creating it everything inside a single big SQL query

3 years ago i had asked to break the data retrieval queries to at least 2 queries (or more),
if we care about performance and good name of Joomla, lets do this
And if changes ?? mean / are considered a B/C break then add them to J4

i made some tests with half job done (a part works on MySQL only, will need to think of it to support all DBs)
and total query time (of 3 queries) was about 0.3 - 0.8 seconds instead of 3.5 seconds with DB on SSD,
if not on SSD multiply the benefit by 5x or 10x, and if website under heavy load then again even more benefit

I ll have some time at end of month but if someone else wants to do this now, then please go ahead

Do you think this PR can go for now (after I will resolve the conflict) or you want to do something better for 3.x and I should wait.

3 years ago i had asked to break the data retrieval queries to at least 2 queries (or more),

Yes. this is good idea.

There is lot of option to speed up it:

1. Avoid file sort of rows in table, add missing indexes, simplified ORDER BY
2. Tables with left join but not in where statement can be added in separated queries/wrapped query. This way we can speed up 2 queries (SELECT [columns] and SELECT COUNT(*))
3. Do not count all articles but use something like SELECT COUNT(*) FROM ([origin query] LIMIT 10*items_in_page OFFSET start_page_offset). This way mysql does not need to scan whole table.
   Visitors do not have to have link to the oldest articles [End page] from the first page.

Do you think this PR can go for now (after I will resolve the conflict) or you want to do something better for 3.x and I should wait.

Your performance fix is all valid (looks good on review),
and any PR i make will not touch your performance fix

In any case,
my suggestion is indepedent of your PR
this PR should be tested and merged for J3.8.4 if there is enough time to add it there

[EDIT]
Only reason i have not posted successful test,
is that i have not tested with a big web-site with tags,
someone with some spare time, please test

Conflict has been removed.

hi all, i'm working with a long time friend Yiannis joomla stack dev, we have implemented php7 and upgraded to 3.8.3 (the speed issue remain) we will install PR 19261 and PR 19284 on the site tonite, you can saw the speed issue on live on https://www.crashdebug.fr beware we use jot cache so you need to find a page or a category who are not in cache (use left menu), i will keep you updated

@Folamour Please mark your test result here: https://issues.joomla.org/tracker/joomla-cms/19284

Yiannis seem busy so I update the thread he have applied PR 19261 and PR 19284 pagetime load went from 4.5sec in joomla 3.7.5 to 3.5 sec in Joomla 3.8.3 (9 sec without PR), and start display page is 2.4 sec, for user Pagespeed load is 1 sec !!!!!!!!!! so its a wonderdull success !!!!!, I wand to sincerelly thanks Csthomas for is fantastic work ! and of course Georgios (ggppdk) who offer me is help, and all the Joomla devs, if I can exprim myself on the internet its because I discovered Joomla 8 year ago, I want to thank you all, you have to know that there is a lot of people using your code, and you make our life better, joomla 3.8.4 update will be awesome for user, just take a look : https://www.crashdebug,fr navigate througth the site and found a page not in cache (using left menu, and navigation bar at the bottom) they are displayed quasi instantly, you made it guys !!!!! Joomla is now as fast as wordpress with 14k article !!!!

Thanks,

Guillaume

@Folamour please mark your Test as successfully:

• open Issue Tracker
• Login with your github-Account
• Click on blue "Test this"-Button above Authors-Picture
• mark your Test as successfully
• hit "submit test result"

I have tested this item ✅ successfully on bdc7ea7

done :)

Hi folks, sorry to buggy you but its time to update, so does anyone can tell me if PR 19261 and PR 19284 is included in joomla 3.8.5 or if i have to do the update then install these PR again,

thanks a lot,

f.

If the pull requests aren't marked as merged then they aren't included in the main code base (and inherently any release).

PR #19261 is included in 3.8.4 and 3.8.5. This PR is still waiting for one more human test.

ok, so we will install PR19284 again after upgrade, (will confirm result when done)

thanks a lot to all

f.

i've experienced a sensible performance gain when only one tag is used as filter tag option (looking at explain) on postgres, but i don't have a big data set /time to test more, i suppose on mysql the gain will be more sensible 😃

I have tested this item ✅ successfully on bdc7ea7

RTC

Fyi PR 19261 and PR 19284 successfully tested on Joomla 3.8.5, good job !