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"

---

Disabling “Content - Vote plugin” plugin

the query changes in

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"

Using the "Profile" function of phpMyAdmin I get a lot of time removing duplicates.
Is the

COALESCE(NULLIF(ROUND(v.rating_sum  / v.rating_count, 0), 0), 0)

part of the query problematic on MariaDB 10.2?

[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 phil@phil-taylor.com - 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.

[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 #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 #27339 please.

[joomla-cms-bot]

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