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?