@test @nadeeshaan i can confirm the query change. ok here. Is there any special thing that can/need be tested? It still retrun the same count of rows on my end.

Before patch:

After patch

Query time is drastically reduced

@zero-24
Please consider the execution time of the query. Not the number of rows.
cause number of rows should be the same. What I explained was in the sub
select it selects a reduced number of rows in order to join with the next
table

On Wed, Aug 13, 2014 at 7:50 PM, zero-24 notifications@github.com wrote:

@test https://github.com/test @nadeeshaan
https://github.com/nadeeshaan i can confirm the query change. ok here.
Is there any special thing that can/need be tested? It still retrun the
same count of rows om my end.

—
Reply to this email directly or view it on GitHub
#4106 (comment).

Nadeeshaan Gunasinghe
Department of Computer Science and Engineering
University of Moratuwa
Sri Lanka

thanks @nadeeshaan I can confirm the result by @infograf768

My results are:
Query time bevor the patch: 6.76 ms
Query time after the patch: 1.78 ms

So full success here.

My test results are different:
Query time before the patch: 2.18 ms

Query time after the patch: 2.29 ms

@810
For 3 rows ? Weird...

@nadeeshan
This should be tested with the other types of DB

@infograf768
Yes of course. For that we need the support of community members who use the other type of DBs. I think all will give the support in this testing process.
:)

@nadeeshaan Use of subquery would makes sense but think there is an error in your description (mentioning x.). Additionally, in this case the LEFT JOIN could be avoided completely imho, as b is not used at all in the query!!!

@all Imho, those optimization tests should be conducted with large datasets and after having queried the database at least 20 times if not more, so that it has "warmed-up", not with just 3, 30 or even 300 rows, if you want to have any meaningful results, thousands or tens of thousands of rows are needed to see a significant result.

@beat com_overload is sufficient for the task? Any links for a Joomla 3 compatible version?

@dgt41 don't understand your question. What is com_overload ?

What I said, is that even though table b is joined in "Original Query before the Optimization" above in the description, none of its columns is used. Moreover, as it is a left join, the only benefit is to have multiple repeated rows depending on if more than 1 left join is possible. So the query itself seems buggy. I guess its original intend was to compute the level by counting the joined elements, but as the (redundant) "level" column exists and is used here, the join is a leftover from the past (from when nested trees were implemented according to The Book).

So not sure that the optimization makes any sense (didn't look at the PHP code as the diff shows not that easily understandable original code).

com_overload is a component that creates mass content in joomla: http://github.com/nikosdion/com_overload
But I am not so sure that is up to date, last commit was like a year ago

com-overload should work fine on j3,3 - I have it running on my test site right now building over 1000 categories and 1500 articles.

On 15 August 2014 22:40, dgt41 notifications@github.com wrote:

com_overload is a component that creates mass content in joomla:
http://github.com/nikosdion/com_overload
https://github.com/nikosdion/com_overload
But I am not so sure that is up to date, last commit was like a year ago

—
Reply to this email directly or view it on GitHub
#4106 (comment).

Brian Teeman
Co-founder Joomla! and OpenSourceMatters Inc.
http://brian.teeman.net/

Brian, I already setup a new test bench, I will post some results when the script finish adding stuff
https://www.dropbox.com/s/crkqx6mfpg7zty6/Screenshot%202014-08-16%2001.01.14.png

Yeah. I've set mine running and will test tomorrow
On 15 Aug 2014 23:03, "dgt41" notifications@github.com wrote:

[image: ] Brian, I already setup a new test bench, I will post some
results when the script finish adding stuff

https://www.dropbox.com/s/crkqx6mfpg7zty6/Screenshot%202014-08-16%2001.01.14.png

—
Reply to this email directly or view it on GitHub
#4106 (comment).

Here are my test results

No PHP opcode, cache, no joomla cache, no MYSQL query_cache
PHP 5.5.10, Mysql 5.5.34
Data set:
Table assets: 18.509
Table categories: 1.119
Table content: 17.364

Before: 404.42ms
After: 9.11ms

@test
Tested with 1178 categories and 57345 articles (created by com_overload)
before 884.51ms
after 16.39ms

By the way @beat's suggestion to remove the last left join
line:101 ->join('LEFT', $db->quoteName('#__categories') . ' AS b ON a.lft > b.lft AND a.rgt < b.rgt’);
made things worst:

@test
test conditions:
no MYSQL query_cache, Mysql 5.6.16
Data set:
Table assets: 15427
Table categories: 1561
Table content: 13639

tool used: mysqlslap
only the affected query is tested pure sql

old query

alikon@PC-ALIKON c:\xampp
# mysqlslap --concurrency=10 --iterations=10 --query=old_pr6.sql --create-schema
=gtest  -uroot -padmin
Warning: Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 27.457 seconds
        Minimum number of seconds to run all queries: 26.557 seconds
        Maximum number of seconds to run all queries: 27.900 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

new query

alikon@PC-ALIKON c:\xampp
# mysqlslap --concurrency=10 --iterations=10 --query=new_pr6.sql --create-schema
=gtest  -uroot -padmin
Warning: Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 0.599 seconds
        Minimum number of seconds to run all queries: 0.265 seconds
        Maximum number of seconds to run all queries: 1.359 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

Merged into the 3.4-dev branch. Thanks!