CODEWITHSUNDEEP
mysqlsqlmariadb
NAG
NAG

Reputation: 501

How to improve slow query with indexed tables MariaDB?

This query takes on average 2/3 seconds. Every fields in join and where are indexes.

How I can improve this query?

SELECT DISTINCT pro_id, pro_url, pro_data, pro_capa, pro_destaque, pro_destaque_data, pro_nome, pro_likes, pro_views, pro_comentarios_total, pro_autor FROM projeto JOIN utilizador_projeto ON pro_id=utp_proid JOIN utilizador ON utp_utiid=uti_id WHERE pro_activo=1 AND pro_privacidade=1 ORDER BY pro_destaque_data DESC LIMIT 24;

slow_query_log:

# Time: 190923  1:10:58
# User@Host: root[root] @  [10.133.247.241]
# Thread_id: 36  Schema: db  QC_hit: No
# Query_time: 3.575462  Lock_time: 0.000114  Rows_sent: 24  Rows_examined: 104820
# Rows_affected: 0  Bytes_sent: 3974

MariaDB [db]> EXPLAIN SELECT DISTINCT pro_id, pro_url, pro_data, pro_capa, pro_destaque, pro_destaque_data, pro_nome, pro_likes, pro_views, pro_comentarios_total, pro_autor FROM projeto JOIN utilizador_projeto ON pro_id=utp_proid JOIN utilizador ON utp_utiid=uti_id WHERE pro_activo=1 AND pro_privacidade=1 ORDER BY pro_destaque_data DESC LIMIT 24;
+------+-------------+--------------------+--------+-----------------------------------------------------------------------------------------------+---------------------+---------+---------------------------------+-------+------------------------------+
| id   | select_type | table              | type   | possible_keys                                                                                 | key                 | key_len | ref                             | rows  | Extra                        |
+------+-------------+--------------------+--------+-----------------------------------------------------------------------------------------------+---------------------+---------+---------------------------------+-------+------------------------------+
|    1 | SIMPLE      | projeto            | ref    | PRIMARY,pro_destaques_index,pro_likes_index,pro_comments_index,pro_views_index,pro_date_index | pro_destaques_index | 2       | const,const                     | 17865 | Using where; Using temporary |
|    1 | SIMPLE      | utilizador_projeto | ref    | utp_utiid,utp_proid                                                                           | utp_proid           | 4       | db.projeto.pro_id               |     1 | Distinct                     |
|    1 | SIMPLE      | utilizador         | eq_ref | PRIMARY                                                                                       | PRIMARY             | 4       | db.utilizador_projeto.utp_utiid |     1 | Using index; Distinct        |
+------+-------------+--------------------+--------+-----------------------------------------------------------------------------------------------+---------------------+---------+---------------------------------+-------+------------------------------+

Upvotes: 0

Views: 84

Answers (1)

Rick James
Rick James

Reputation: 142258

    WHERE  pro_active=1
      AND  pro_privacy=1
    ORDER BY  pro_highlight_date DESC

begs for this 'composite' index:

INDEX(pro_active, pro_privacy,   -- in either order
      pro_highlight_date)

Probably Rows_examined: 139482 will drop to Rows_examined: 24.

That assumes those 3 columns are in the same table. Instead of prefixing column names with table names, do the following:

...
FROM project AS pro
...
WHERE pro.active = 1 ...

That eliminates any ambiguity (and takes about the same number of keystrokes).

Every fields in join and where are indexes

Overkill. And single-column indexes are often not as useful as multi-column (composite) indexes. See: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Upvotes: 2

Related Questions