Joomla-cms: Very slow article list query on MariaDB 10.2 with many articles when “Content - Vote“ plugin is enabled

[teoteo]

Steps to reproduce the issue

1. Install Joomla!
2. add many articles (I have 11726 articles)
3. enable the “Content - Vote plugin” plugin
4. visit the backend Content > Articles page

Expected result

A quick list of articles in 1-2 seconds

Actual result

I have to wait more than a minute to get the listed article

System information (as much as possible)

Tested System 1

With some load because it is a production server

$ cat /etc/centos-release
CentOS Linux release 7.5.1804 (Core) 

MariaDB [(none)]> SHOW VARIABLES LIKE "%version%";

+-------------------------+---------------------------------+
| Variable_name           | Value                           |
+-------------------------+---------------------------------+
| innodb_version          | 5.7.24                          |
| protocol_version        | 10                              |
| slave_type_conversions  |                                 |
| version                 | 10.2.19-MariaDB-log             |
| version_comment         | MariaDB Server                  |
| version_compile_machine | x86_64                          |
| version_compile_os      | Linux                           |
| version_malloc_library  | system                          |
| version_ssl_library     | OpenSSL 1.0.1e-fips 11 Feb 2013 |
| wsrep_patch_version     | wsrep_25.23                     |
+-------------------------+---------------------------------+

Tested System 2

$ cat /etc/centos-release
CentOS release 6.10 (Final)

MariaDB [(none)]> SHOW VARIABLES LIKE "%version%";

+-------------------------+---------------------------------+
| Variable_name           | Value                           |
+-------------------------+---------------------------------+
| innodb_version          | 5.7.24                          |
| protocol_version        | 10                              |
| slave_type_conversions  |                                 |
| version                 | 10.2.19-MariaDB                 |
| version_comment         | MariaDB Server                  |
| version_compile_machine | x86_64                          |
| version_compile_os      | Linux                           |
| version_malloc_library  | system                          |
| version_ssl_library     | OpenSSL 1.0.1e-fips 11 Feb 2013 |
| wsrep_patch_version     | wsrep_25.23                     |
+-------------------------+---------------------------------+

Tested system 3

$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.14.1

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.23                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.23                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | osx10.9                      |
+-------------------------+------------------------------+

Joomla! version:

3.9.0

PHP version

5.6.38

Additional comments

The same site on the same server, with MariaDB 10.0 and 10.1 or on another system with MySQL 5.7.23 the artilce list is fast.

Checkin the longest queries, I can see this:

SELECT DISTINCT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.created_by_alias, a.modified, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.note,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,c.title AS category_title, c.created_user_id AS category_uid, c.level AS category_level,parent.title AS parent_category_title, parent.id AS parent_category_id, 
                                parent.created_user_id AS parent_category_uid, parent.level AS parent_category_level,ua.name AS author_name,COALESCE(NULLIF(ROUND(v.rating_sum  / v.rating_count, 0), 0), 0) AS rating, 
                    COALESCE(NULLIF(v.rating_count, 0), 0) as rating_count,COUNT(asso2.id)>1 as association

  FROM jos_content AS a

  LEFT JOIN `jos_languages` AS l 
  ON l.lang_code = a.language

  LEFT JOIN jos_users AS uc 
  ON uc.id=a.checked_out

  LEFT JOIN jos_viewlevels AS ag 
  ON ag.id = a.access

  LEFT JOIN jos_categories AS c 
  ON c.id = a.catid

  LEFT JOIN jos_categories AS parent 
  ON parent.id = c.parent_id

  LEFT JOIN jos_users AS ua 
  ON ua.id = a.created_by

  LEFT JOIN jos_content_rating AS v 
  ON a.id = v.content_id

  LEFT JOIN jos_associations AS asso 
  ON asso.id = a.id 
  AND asso.context='com_content.item'

  LEFT JOIN jos_associations AS asso2 
  ON asso2.key = asso.key

  WHERE (a.state = 0 OR a.state = 1)

  GROUP BY a.id, l.title, l.image, uc.name, ag.title, c.title, ua.name, c.created_user_id, c.level, parent.id, v.rating_sum, v.rating_count

  ORDER BY a.id desc 
  LIMIT 20
  ```

Selecting in phpMyAdmin the table jos_contents and executing the query, I get:


**Tested system 1**: Query took **58.0740 seconds**"

**Tested system 2**: Query took **48.6852 seconds**"

**Tested system 3**: Query took **0.9777 seconds**"

![Profiling_on_system_2-query-1](https://issues.joomla.org/uploads/1/cab1ef15322c65345d7bc20f78e3a95e.png)

---

#### Disabling “**Content - Vote plugin**” plugin

the query changes in 

```sql
SELECT DISTINCT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.created_by_alias, a.modified, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.note,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,c.title AS category_title, c.created_user_id AS category_uid, c.level AS category_level,parent.title AS parent_category_title, parent.id AS parent_category_id, 
                                parent.created_user_id AS parent_category_uid, parent.level AS parent_category_level,ua.name AS author_name,COUNT(asso2.id)>1 as association

  FROM jos_content AS a

  LEFT JOIN `jos_languages` AS l 
  ON l.lang_code = a.language

  LEFT JOIN jos_users AS uc 
  ON uc.id=a.checked_out

  LEFT JOIN jos_viewlevels AS ag 
  ON ag.id = a.access

  LEFT JOIN jos_categories AS c 
  ON c.id = a.catid

  LEFT JOIN jos_categories AS parent 
  ON parent.id = c.parent_id

  LEFT JOIN jos_users AS ua 
  ON ua.id = a.created_by

  LEFT JOIN jos_associations AS asso 
  ON asso.id = a.id 
  AND asso.context='com_content.item'

  LEFT JOIN jos_associations AS asso2 
  ON asso2.key = asso.key

  WHERE (a.state = 0 OR a.state = 1)

  GROUP BY a.id, l.title, l.image, uc.name, ag.title, c.title, ua.name, c.created_user_id, c.level, parent.id

  ORDER BY a.id desc 
  LIMIT 20
  ```


Selecting in phpMyAdmin the table jos_contents and executing the query, I get:

**Tested system 2**: Query took **0.7925 seconds**"

**Tested system 1**: Query took **0.7869 seconds**"

**Tested system 3**: Query took **0.5500 seconds**"

![Profiling_on_system_2-query-2](https://issues.joomla.org/uploads/1/fa221cc9f09bfea7a302c84f5e7379fd.png)
---

Using the "Profile" function of phpMyAdmin I get a lot of time removing duplicates.
Is the
```sql
COALESCE(NULLIF(ROUND(v.rating_sum  / v.rating_count, 0), 0), 0)

part of the query problematic on MariaDB 10.2?

[PhilETaylor]

Please can you send a copy of the database (or subset of the tables) to me at [email protected] - data will only be used for the purposes of this issue.

I cannot replicate this locally at the moment. Or on any of the live sites I have with large numbers of articles.

I have checked the query (with EXPLAIN) and Joomla is already correctly using indexes for all the joins.

[csthomas]

@teoteo Only for test, can you remove the DISTINCT word from the SELECT and in the GROUP BY leave only one column a.id. IMO it should return the same result and should be faster.

[teoteo]

Please can you send a copy of the database (or subset of the tables) to me at [email protected] - data will only be used for the purposes of this issue.

I’ll clean the 4000 rows of user data and I’ll send you the interested tables, thanks.

@teoteo Only for test, can you remove the DISTINCT word from the SELECT and in the GROUP BY leave only one column a.id. IMO it should return the same result and should be faster.

You are right:

SELECT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.created_by_alias, a.modified, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.note,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,c.title AS category_title, c.created_user_id AS category_uid, c.level AS category_level,parent.title AS parent_category_title, parent.id AS parent_category_id, 
                                parent.created_user_id AS parent_category_uid, parent.level AS parent_category_level,ua.name AS author_name,COALESCE(NULLIF(ROUND(v.rating_sum  / v.rating_count, 0), 0), 0) AS rating, 
                    COALESCE(NULLIF(v.rating_count, 0), 0) as rating_count,COUNT(asso2.id)>1 as association

  FROM jos_content AS a

  LEFT JOIN `jos_languages` AS l 
  ON l.lang_code = a.language

  LEFT JOIN jos_users AS uc 
  ON uc.id=a.checked_out

  LEFT JOIN jos_viewlevels AS ag 
  ON ag.id = a.access

  LEFT JOIN jos_categories AS c 
  ON c.id = a.catid

  LEFT JOIN jos_categories AS parent 
  ON parent.id = c.parent_id

  LEFT JOIN jos_users AS ua 
  ON ua.id = a.created_by

  LEFT JOIN jos_content_rating AS v 
  ON a.id = v.content_id

  LEFT JOIN jos_associations AS asso 
  ON asso.id = a.id 
  AND asso.context='com_content.item'

  LEFT JOIN jos_associations AS asso2 
  ON asso2.key = asso.key

  WHERE (a.state = 0 OR a.state = 1)

  GROUP BY a.id

  ORDER BY a.id desc 
  LIMIT 20

takes 0.0030 seconds on tested system 1!

[ghost]

@csthomas as your Suggestions works is there a PR for 4.0 coming or closing the Issue?

[ghost]

@csthomas reminder.

[PhilETaylor]

This is not about the voting plugin. Its about multilingual enabled sites.

the

 GROUP BY a.id, l.title, l.image, uc.name, ag.title, c.title, ua.name, c.created_user_id, c.level, parent.id, v.rating_sum, v.rating_count

grouping is added when JLanguageAssociations::isEnabled() returns true in the /administrator/components/com_articles/items.php model.

This can grind a mysql server to its knees with a lot of articles (the site Im debugging now has over 40,000 articles

All indexes are correct, the problem is just a very very badly constructed mysql query that no one has bothered profiling with a reasonable amount of data (content)

When you run an EXPLAIN on the query you can see that it has to use temporary storage, and filesort - the slowest ever type of ordering...

Someone needs to benchmark Joomla again, when multilingual is enabled, with a large dataset, thats not been done for many many years now.

This issue effectively makes a multi lingual large site impossible in Joomla.

[teoteo]

This is not about the voting plugin. Its about multilingual enabled sites.

If there is something I can test, I manage a site with about 13000 articles and 5 content languages.

[SharkyKZ]

This should already be fixed in 4.0, see https://github.com/joomla/joomla-cms/pull/26465. I guess this could be backported to 3.x too.

[alikon]

@SharkyKZ whould you still like to backport #26465 to 3.x?

[SharkyKZ]

OK, working on it.

[SharkyKZ]

See #27338 for removing group clause.

[SharkyKZ]

For removing DISTINCT test https://github.com/joomla/joomla-cms/pull/27339 please.

[joomla-cms-bot]

Set to "closed" on behalf of @SharkyKZ by The JTracker Application at issues.joomla.org/joomla-cms/23090