Solve slow SQL queries

Question

I enabled to log slow queries for more than 30 seconds, so that I can check what went wrong. In the log file, I get the following result multiple times.

How can I solve this?

/usr/sbin/mysqld, Version: 10.1.20-MariaDB (MariaDB Server). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
# Time: 170118 10:15:55
# User@Host: magento[magento] @ localhost []
# Thread_id: 76  Schema: magento  QC_hit: No
# Query_time: 35.514841  Lock_time: 0.000914  Rows_sent: 0  Rows_examined: 346605
# Rows_affected: 346605
use magento;
SET timestamp=1484730955;
DELETE FROM `catalog_product_index_price_idx`;
# Time: 170118 10:17:12
# User@Host: magento[magento] @ localhost []
# Thread_id: 76  Schema: magento  QC_hit: No
# Query_time: 44.621547  Lock_time: 0.000037  Rows_sent: 0  Rows_examined: 346605
# Rows_affected: 346605
SET timestamp=1484731032;
DELETE FROM `catalog_product_index_price`;

Answer 1

The queries you've submitted in your question are not solvable without rewriting the way how Magento price indexer works. Ideally, every time you run the indexation, it should create a fresh table and then alias those tables when it is done.

Refer to my talk about building the indexer properly: https://ivanchepurnyi.github.io/slides/indexation-techiques/index.html