Joomla! Issue Tracker - CMS

Download
Launch
Feeling Lucky

[#13528] - Speed up query by using only one indexed column in sql where clause

? ? Pending

User tests: Successful: Unsuccessful:

avatar csthomas
csthomas
9 Jan 2017

Summary of Changes

  • Usual mysql databese can only use one index in WHERE clause or use union.
  • Newer version can merge indexes to speed up.

Current query contains OR parent_id = 0 which complicate query optimisation for database.
This PR replace above to use only name column.

Testing Instructions - Old

Unit tests pass.
Code review.

On installed joomla with sample data, mysql may not use indexes because there is too less rows.

Testing Instructions - New

Install joomla without patch, turn off cache and go to front page.
Joomla works OK without any errors.

Apply patch and do the same.
Joomla works OK without any errors.

Documentation Changes Required

N/A

avatar csthomas csthomas - open - 9 Jan 2017
avatar csthomas csthomas - change - 9 Jan 2017
Status New ⇒ Pending
avatar joomla-cms-bot joomla-cms-bot - change - 9 Jan 2017
Category ⇒ Libraries
avatar csthomas
csthomas - comment - 9 Jan 2017

My explanation for mariadb is a little outdated (because mariadb can merge indexes) but new query is better that older.

EXPLAIN SELECT id, name, rules, parent_id FROM #__assets WHERE name IN ('root.1', 'com_users')

Array(
            [id] => 1
            [select_type] => SIMPLE
            [table] => j37_assets
            [partitions] => 
            [type] => range
            [possible_keys] => idx_asset_name
            [key] => idx_asset_name
            [key_len] => 202
            [ref] => 
            [rows] => 2
            [filtered] => 100.00
            [Extra] => Using index condition
        )

And older one:
EXPLAIN SELECT id, name, rules, parent_id FROM #__assets WHERE name IN ('com_users') or parent_id=0

Array(
            [id] => 1
            [select_type] => SIMPLE
            [table] => j37_assets
            [partitions] => 
            [type] => index_merge
            [possible_keys] => idx_asset_name,idx_parent_id
            [key] => idx_asset_name,idx_parent_id
            [key_len] => 202,4
            [ref] => 
            [rows] => 2
            [filtered] => 100.00
            [Extra] => Using union(idx_asset_name,idx_parent_id); Using where
        )
avatar csthomas csthomas - change - 9 Jan 2017
The description was changed
avatar csthomas csthomas - edited - 9 Jan 2017
avatar csthomas csthomas - edited - 9 Jan 2017
avatar csthomas csthomas - change - 30 Jan 2017
The description was changed
avatar csthomas csthomas - edited - 30 Jan 2017
avatar csthomas csthomas - edited - 30 Jan 2017
avatar csthomas csthomas - change - 30 Jan 2017
The description was changed
avatar csthomas csthomas - edited - 30 Jan 2017
avatar csthomas csthomas - change - 30 Jan 2017
The description was changed
avatar csthomas csthomas - edited - 30 Jan 2017
avatar csthomas csthomas - change - 4 Feb 2017
The description was changed
avatar csthomas csthomas - edited - 4 Feb 2017
avatar csthomas csthomas - change - 4 Feb 2017
The description was changed
avatar csthomas csthomas - edited - 4 Feb 2017
avatar csthomas csthomas - change - 16 Feb 2017
Title
…
Do not generate sql error if there are no components loaded
Speed up query by using only one indexed column in sql where clause
avatar csthomas csthomas - edited - 16 Feb 2017
avatar csthomas csthomas - change - 16 Feb 2017
The description was changed
avatar csthomas csthomas - edited - 16 Feb 2017
avatar csthomas csthomas - change - 16 Feb 2017
The description was changed
avatar csthomas csthomas - edited - 16 Feb 2017
avatar csthomas csthomas - change - 16 Feb 2017
The description was changed
avatar csthomas csthomas - edited - 16 Feb 2017
avatar rdeutz rdeutz - change - 27 May 2017
Labels Added: ?
avatar csthomas
csthomas - comment - 30 Jun 2017

This PR has 2 approves and it should be enough as test based on code review.
RTC?

avatar franz-wohlkoenig franz-wohlkoenig - change - 30 Jun 2017
Status Pending ⇒ Ready to Commit
avatar franz-wohlkoenig
franz-wohlkoenig - comment - 30 Jun 2017

RTC after two Approves.

avatar rdeutz
rdeutz - comment - 6 Jul 2017

approval is not tested, at least one test should be done

avatar csthomas csthomas - change - 6 Jul 2017
The description was changed
avatar csthomas csthomas - edited - 6 Jul 2017
avatar alikon alikon - test_item - 7 Jul 2017 - Tested successfully
avatar alikon
alikon - comment - 7 Jul 2017

I have tested this item ✅ successfully on b61cfa9

This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/13528.

avatar rdeutz rdeutz - change - 7 Jul 2017
Status Ready to Commit ⇒ Fixed in Code Base
Closed_Date 0000-00-00 00:00:00 ⇒ 2017-07-07 13:47:14
Closed_By ⇒ rdeutz
Labels Added: ?
avatar rdeutz rdeutz - close - 7 Jul 2017
avatar rdeutz rdeutz - merge - 7 Jul 2017

Add a Comment

Login with GitHub to post a comment