@alikon, i'm no expert in db as youknow, so let me try to understand this.

Basicly you change the order of the where clauses in the query so the catid is called first, and by that, since the catid has an index (and alias not) it improves the performance, right?

Anyway, this are my results (clean joomla install) with mysql

Before:

After

i notice the "using where" after patch is this how it's supossed to be?

reggarding the query being repeated 3 times:
i only have it one time

Hello

@andrepereiradasilva , @alikon , @pici65

i found time to test, with a big website and a category with 4200 articles
(created an article inside the category that has 4200 articles)

Database is on an SSD

• if it is not on SSD, then you have 10x 20x bigger times ...

I confirm the finding of @alikon, delay is due to the duplicate alias check (duplicate inside the same category)

Article creation

• duplicate alias query executed 3 times 3 x (about) 2 seconds = 4 to 6 seconds

Article update

• duplicate alias query executed 1 times cost: about 2 seconds to 3 seconds

but this PR gives no benefit

i tested by adding an index to the alias column
the query is reduced to ... 6.5 milliseconds

Solution is to remove duplicate alias check or to add an index to the alias column

• note same problem is faced by any TABLE that tries to check for duplicate alias inside the given category, when the table does not have an index for the alias column

The duplicate alias check is required at the PHP level without efficient
SQL schema to enforce duplicate aliases and an efficient manner to execute
a query and rely on that schema to raise the appropriate error for a model
to know it needs to auto-increment the duplicated alias. So the index and
query optimization will have to be good enough for now.

On Thursday, June 2, 2016, Georgios Papadakis notifications@github.com
wrote:

Hello

@andrepereiradasilva https://github.com/andrepereiradasilva , @alikon
https://github.com/alikon , @pici65 https://github.com/pici65

i found time to test, with a big website and a category with 4200 articles
(created an article inside the category that has 4200 articles)

Database is on an SSD

• if it is not on SSD, then you have 10x 20x bigger times ...

I confirm the finding of @alikon https://github.com/alikon, delay is

due to the duplicate alias check (duplicate inside the same category)

Article creation

• duplicate alias query executed 3 times 3 x (about) 2 seconds = 4 to 6 seconds

Article update

• duplicate alias query executed 1 times cost: about 2 seconds to 3 seconds

but this PR gives no benefit

i tested by adding an index to the alias column
the query is reduced to ... 6.5 milliseconds

Solution is to remove duplicate alias check or to add an index to the
alias column

• note same problem is faced by any TABLE that tries to check for duplicate alias inside the given category, when the table does not have an index for the alias column

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#10710 (comment),
or mute the thread
https://github.com/notifications/unsubscribe/AAWfoc_ax1FVENC6B33vCa2dFOGCEO_5ks5qH3UWgaJpZM4IsbO_
.

Ok, surely the solution is to add the index to alias column,

not only because of duplicate alias check being required,

but because in future (or even now) more SQL queries may need the index to be there

The #__content table already have IRC 8 indexes if we want to add another
one maybe the better solution should be to add a composite index
alias+catid to full match the WHERE conditions instead of a partial match
on only the alias field...
On 3 Jun 2016 10:27 am, "Georgios Papadakis" notifications@github.com
wrote:

Ok, surely the solution is to add the index to alias column,

not only because of duplicate alias check being required,

but because in future (or even now) more SQL queries may need the index to
be there

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#10710 (comment),
or mute the thread
https://github.com/notifications/unsubscribe/AALFsWvsPd0EpfJPMc3jg4c3iVsB2Z2Pks5qH-VsgaJpZM4IsbO_
.

ok we agree that a new index is needed

but i will disagree on the composite index, it is best to have an atomic index on the alias column,

• let's not target to optimize this query only, we do not know which other query may use alias in the future or using it already

Hi

i forgot to say and please correct me if i am wrong

• since the alias is unique inside a category (because current PHP will never insert a duplicate alias inside same category)

adding a composite index: alias+catid
is a total waste

[EDIT ... explanation] :
because you would need e.g. 100+ categories to have articles with same alias to get the composite index to produce a slight benefit

so 1 more reason why, the index on alias should be atomic on the alias column only

[EDIT]
adding a composite index: catid+alias is a different case (it will give just a little more performance benefit), but

• it will not be useable by queries that want to limit by alias only
• the benefit that will give compared to the atomic index on alias will be really small, unless you have 100,000 ? a million articles web-site thus it may make a little noticeable difference ?

@ggppdk it's not so uncommon that sites have more than 100 categories as far as i know, but i can agree with you that can be more common for a query to have only the alias field in the where condition so let's go with an atomic index for now after all there is a performance gain, we will see in the future if some report will be issued regarding sites with a lot of categories....

closing this one in favor of #10739