Меню
Что нового?

слишком долгий селект

Paradigma

Новичок
Есть данные в таблице где более 500 000 зыписей, так вот, я понимаю в принципе где проблема, но не понимаю как правильно её решить, вот запрос, которые выполняется 0,2 сек

SELECT DISTINCT commerce_order.order_id AS order_id

FROM commerce_order commerce_order
LEFT JOIN field_data_commerce_customer_billing field_data_commerce_customer_billing ON
commerce_order.order_id = field_data_commerce_customer_billing.entity_id
LEFT JOIN field_data_commerce_customer_traveler field_data_commerce_customer_traveler ON
commerce_order.order_id = field_data_commerce_customer_traveler.entity_id AND (field_data_commerce_customer_traveler.entity_type = 'commerce_order' AND field_data_commerce_customer_traveler.deleted = '0')

LEFT JOIN field_data_field_billing_first_name commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_first_name ON
field_data_commerce_customer_billing.commerce_customer_billing_profile_id = commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_first_name.entity_id AND (commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_first_name.entity_type = 'commerce_customer_profile' AND commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_first_name.deleted = '0')
LEFT JOIN field_data_field_first_name commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_first_name ON
field_data_commerce_customer_traveler.commerce_customer_traveler_profile_id = commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_first_name.entity_id AND (commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_first_name.entity_type = 'commerce_customer_profile' AND commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_first_name.deleted = '0')

LEFT JOIN field_data_field_billing_last_name commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_last_name ON
field_data_commerce_customer_billing.commerce_customer_billing_profile_id = commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_last_name.entity_id AND (commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_last_name.entity_type = 'commerce_customer_profile' AND commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_last_name.deleted = '0')
LEFT JOIN field_data_field_last_name commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_last_name ON
field_data_commerce_customer_traveler.commerce_customer_traveler_profile_id = commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_last_name.entity_id AND (commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_last_name.entity_type = 'commerce_customer_profile' AND commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_last_name.deleted = '0')

WHERE ((
(CONCAT_WS(' ', commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_last_name.field_billing_last_name_value,
' ', commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_last_name.field_last_name_value) LIKE '%Mat%')))
GROUP BY order_id
ORDER BY order_id DESC
LIMIT 31 OFFSET 0;

а вот запрос которые выполняется 5 секунд


SELECT DISTINCT commerce_order.order_id AS order_id

FROM commerce_order commerce_order
LEFT JOIN field_data_commerce_customer_billing field_data_commerce_customer_billing ON
commerce_order.order_id = field_data_commerce_customer_billing.entity_id
LEFT JOIN field_data_commerce_customer_traveler field_data_commerce_customer_traveler ON
commerce_order.order_id = field_data_commerce_customer_traveler.entity_id AND (field_data_commerce_customer_traveler.entity_type = 'commerce_order' AND field_data_commerce_customer_traveler.deleted = '0')

LEFT JOIN field_data_field_billing_first_name commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_first_name ON
field_data_commerce_customer_billing.commerce_customer_billing_profile_id = commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_first_name.entity_id AND (commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_first_name.entity_type = 'commerce_customer_profile' AND commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_first_name.deleted = '0')
LEFT JOIN field_data_field_first_name commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_first_name ON
field_data_commerce_customer_traveler.commerce_customer_traveler_profile_id = commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_first_name.entity_id AND (commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_first_name.entity_type = 'commerce_customer_profile' AND commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_first_name.deleted = '0')

LEFT JOIN field_data_field_billing_last_name commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_last_name ON
field_data_commerce_customer_billing.commerce_customer_billing_profile_id = commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_last_name.entity_id AND (commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_last_name.entity_type = 'commerce_customer_profile' AND commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_last_name.deleted = '0')
LEFT JOIN field_data_field_last_name commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_last_name ON
field_data_commerce_customer_traveler.commerce_customer_traveler_profile_id = commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_last_name.entity_id AND (commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_last_name.entity_type = 'commerce_customer_profile' AND commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_last_name.deleted = '0')

WHERE (((CONCAT_WS(' ', commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_first_name.field_billing_first_name_value,
' ', commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_first_name.field_first_name_value) LIKE '%Erik%') AND
(CONCAT_WS(' ', commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_last_name.field_billing_last_name_value,
' ', commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_last_name.field_last_name_value) LIKE '%Mat%')))
GROUP BY order_id
ORDER BY order_id DESC
LIMIT 31 OFFSET 0;

я понимаю что ему приходится уже искать не по одной таблице, а по двум значения, но как-то ведь можно решить проблему?

тип таблиц InnoDB индексы есть
 
c0dex

c0dex

web.dev 2002-...
Команда форума
Партнер клуба
Проблему решить наверное можно. Explain тебе в руки.
 

Paradigma

Новичок
Та я смотрел explain там все хорошо

+----+-------------+-----------------------------------------------------------------------------------------------------+-------+-------------------------------------------------+-----------+---------+---------------------------------------------------------------------------------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------------------------------------------------------------------------------+-------+-------------------------------------------------+-----------+---------+---------------------------------------------------------------------------------------------------------------+------+-------------+
| 1 | SIMPLE | commerce_order | index | NULL | PRIMARY | 4 | NULL | 31 | Using index |
| 1 | SIMPLE | field_data_commerce_customer_billing | ref | entity_id | entity_id | 4 | eurail_prod_interrail.commerce_order.order_id | 1 | |
| 1 | SIMPLE | field_data_commerce_customer_traveler | ref | PRIMARY,entity_id,full_type | PRIMARY | 391 | const,eurail_prod_interrail.commerce_order.order_id,const | 1 | |
| 1 | SIMPLE | commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_first_name | ref | PRIMARY,entity_type,deleted,entity_id,full_type | PRIMARY | 391 | const,eurail_prod_interrail.field_data_commerce_customer_billing.commerce_customer_billing_profile_id,const | 1 | |
| 1 | SIMPLE | commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_first_name | ref | PRIMARY,entity_type,deleted,entity_id,full_type | PRIMARY | 391 | const,eurail_prod_interrail.field_data_commerce_customer_traveler.commerce_customer_traveler_profile_id,const | 1 | Using where |
| 1 | SIMPLE | commerce_customer_profile_field_data_commerce_customer_billing__field_data_field_billing_last_name | ref | PRIMARY,entity_type,deleted,entity_id | PRIMARY | 391 | const,eurail_prod_interrail.field_data_commerce_customer_billing.commerce_customer_billing_profile_id,const | 1 | |
| 1 | SIMPLE | commerce_customer_profile_field_data_commerce_customer_traveler__field_data_field_last_name | ref | PRIMARY,entity_type,deleted,entity_id | PRIMARY | 391 | const,eurail_prod_interrail.field_data_commerce_customer_traveler.commerce_customer_traveler_profile_id,const | 1 | Using where |
+----+-------------+-----------------------------------------------------------------------------------------------------+-------+-------------------------------------------------+-----------+---------+---------------------------------------------------------------------------------------------------------------+------+-------------+
 
c0dex

c0dex

web.dev 2002-...
Команда форума
Партнер клуба
@Paradigma, профилируй свой запрос, чудес не бывает.
 

Paradigma

Новичок
я понимаю, но у меня уже идеи кончились, я не знаю каким образом его можно оптимизировать, вот чего и написал сюда, что может кто-то даст дельный совет как можно реорганизовать WHERE или еще что-то
 

AnrDaemon

Продвинутый новичок
Надо профилировать, прежде чем оптимизировать.
 

Paradigma

Новичок
Не срвсем понимаю что значит профилировать? Каким образом?
 
fixxxer

fixxxer

К.О.
Партнер клуба
Чтобы у кого-то появилось желание дать совет, имеет смысл привести такой SQL, который можно прочитать с первого раза и увидеть разницу, показать структуру таблиц с индексами и все explain-ы, ну и вообще пояснить что ты делаешь.

С этим твоим бэм-sql возникает другое желание - закрыть нафиг окно.
 

Paradigma

Новичок
То был кусок запроса, вот полный запрос, который обрабатывает база


Код: 
SELECT DISTINCT commerce_order.order_id AS order_id, users_commerce_order.uid AS users_commerce_order_uid, commerce_order.created AS commerce_order_created, commerce_order.changed AS commerce_order_changed, users_commerce_order.name AS users_commerce_order_name, commerce_customer_profile_field_data_commerce_customer_billing.profile_id AS commerce_customer_profile_field_data_commerce_customer_billi, users_commerce_order.mail AS users_commerce_order_mail, commerce_customer_profile_field_data_commerce_customer_contact.profile_id AS commerce_customer_profile_field_data_commerce_customer_conta, commerce_order.status AS commerce_order_status, commerce_customer_profile_field_data_commerce_customer_traveler.profile_id AS commerce_customer_profile_field_data_commerce_customer_trave, commerce_order.order_id AS commerce_order_order_id, 'user' AS field_data_field_email_address_user_entity_type, 'commerce_order' AS field_data_field_order_number_commerce_order_entity_type, 'user' AS field_data_field_first_name_user_entity_type, 'commerce_customer_profile' AS field_data_field_billing_first_name_commerce_customer_profil, 'user' AS field_data_field_last_name_user_entity_type, 'commerce_customer_profile' AS field_data_field_billing_last_name_commerce_customer_profile, 'commerce_customer_profile' AS field_data_field_email_address_commerce_customer_profile_ent, 'commerce_order' AS field_data_field_put_order_on_hold_commerce_order_entity_typ, 'commerce_order' AS field_data_commerce_order_total_commerce_order_entity_type, 'commerce_order' AS field_data_field_is_imported_commerce_order_entity_type, 'commerce_customer_profile' AS field_data_field_first_name_commerce_customer_profile_entity, 'commerce_customer_profile' AS field_data_field_last_name_commerce_customer_profile_entity_
FROM commerce_order commerce_order
LEFT JOIN field_data_commerce_customer_billing field_data_commerce_customer_billing ON commerce_order.order_id = field_data_commerce_customer_billing.entity_id AND (field_data_commerce_customer_billing.entity_type = 'commerce_order' AND field_data_commerce_customer_billing.deleted = '0')
LEFT JOIN commerce_customer_profile commerce_customer_profile_field_data_commerce_customer_billing ON field_data_commerce_customer_billing.commerce_customer_billing_profile_id = commerce_customer_profile_field_data_commerce_customer_billing.profile_id
STRAIGHT_JOIN users users_commerce_order ON commerce_order.uid = users_commerce_order.uid
LEFT JOIN field_data_commerce_coupon_order_reference field_data_commerce_coupon_order_reference ON commerce_order.order_id = field_data_commerce_coupon_order_reference.entity_id AND (field_data_commerce_coupon_order_reference.entity_type = 'commerce_order' AND field_data_commerce_coupon_order_reference.deleted = '0')
LEFT JOIN commerce_coupon commerce_coupon_field_data_commerce_coupon_order_reference ON field_data_commerce_coupon_order_reference.commerce_coupon_order_reference_target_id = commerce_coupon_field_data_commerce_coupon_order_reference.coupon_id
LEFT JOIN field_data_commerce_customer_contact field_data_commerce_customer_contact ON commerce_order.order_id = field_data_commerce_customer_contact.entity_id AND (field_data_commerce_customer_contact.entity_type = 'commerce_order' AND field_data_commerce_customer_contact.deleted = '0')
LEFT JOIN commerce_customer_profile commerce_customer_profile_field_data_commerce_customer_contact ON field_data_commerce_customer_contact.commerce_customer_contact_profile_id = commerce_customer_profile_field_data_commerce_customer_contact.profile_id
LEFT JOIN commerce_payment_transaction commerce_payment_transaction_commerce_order ON commerce_order.order_id = commerce_payment_transaction_commerce_order.order_id
LEFT JOIN field_data_commerce_customer_traveler field_data_commerce_customer_traveler ON commerce_order.order_id = field_data_commerce_customer_traveler.entity_id AND (field_data_commerce_customer_traveler.entity_type = 'commerce_order' AND field_data_commerce_customer_traveler.deleted = '0')
LEFT JOIN commerce_customer_profile commerce_customer_profile_field_data_commerce_customer_traveler ON field_data_commerce_customer_traveler.commerce_customer_traveler_profile_id = commerce_customer_profile_field_data_commerce_customer_traveler.profile_id
LEFT JOIN field_data_field_email_address users_commerce_order__field_data_field_email_address ON users_commerce_order.uid = users_commerce_order__field_data_field_email_address.entity_id AND (users_commerce_order__field_data_field_email_address.entity_type = 'user' AND users_commerce_order__field_data_field_email_address.deleted = '0')
LEFT JOIN field_data_field_email_address commerce_customer_profile_field_data_commerce_customer_contact__field_data_field_email_address ON commerce_customer_profile_field_data_commerce_customer_contact.profile_id = commerce_customer_profile_field_data_commerce_customer_contact__field_data_field_email_address.entity_id AND (commerce_customer_profile_field_data_commerce_customer_contact__field_data_field_email_address.entity_type = 'commerce_customer_profile' AND commerce_customer_profile_field_data_commerce_customer_contact__field_data_field_email_address.deleted = '0')
WHERE (( (CONCAT_WS(' ', users_commerce_order__field_data_field_email_address.field_email_address_value, ' ', users_commerce_order.mail, ' ', commerce_customer_profile_field_data_commerce_customer_contact__field_data_field_email_address.field_email_address_value) LIKE '%[email protected]%') ))
GROUP BY order_id
ORDER BY commerce_order_order_id DESC
LIMIT 31 OFFSET 0;
 

Paradigma

Новичок
а вот его експлейн

Код: 
+----+-------------+------------------------------------------------------------------------------------------------+--------+---------------------------------------+----------+---------+-------------------------------------------------------------------------------------------------------------+------+-------------+
| id | select_type | table                                                                                          | type   | possible_keys                         | key      | key_len | ref                                                                                                         | rows | Extra       |
+----+-------------+------------------------------------------------------------------------------------------------+--------+---------------------------------------+----------+---------+-------------------------------------------------------------------------------------------------------------+------+-------------+
|  1 | SIMPLE      | commerce_order                                                                                 | index  | uid                                   | PRIMARY  | 4       | NULL                                                                                                        |   31 |             |
|  1 | SIMPLE      | field_data_commerce_coupon_order_reference                                                     | ref    | PRIMARY,entity_type,deleted,entity_id | PRIMARY  | 391     | const,eurail_prod_interrail.commerce_order.order_id,const                                                   |    1 |             |
|  1 | SIMPLE      | commerce_coupon_field_data_commerce_coupon_order_reference                                     | eq_ref | PRIMARY                               | PRIMARY  | 4       | eurail_prod_interrail.field_data_commerce_coupon_order_reference.commerce_coupon_order_reference_target_id  |    1 | Using index |
|  1 | SIMPLE      | field_data_commerce_customer_billing                                                           | ref    | PRIMARY,entity_type,deleted,entity_id | PRIMARY  | 391     | const,eurail_prod_interrail.commerce_order.order_id,const                                                   |    1 |             |
|  1 | SIMPLE      | field_data_commerce_customer_contact                                                           | ref    | PRIMARY,entity_type,deleted,entity_id | PRIMARY  | 391     | const,eurail_prod_interrail.commerce_order.order_id,const                                                   |    1 |             |
|  1 | SIMPLE      | commerce_customer_profile_field_data_commerce_customer_billing                                 | eq_ref | PRIMARY                               | PRIMARY  | 4       | eurail_prod_interrail.field_data_commerce_customer_billing.commerce_customer_billing_profile_id             |    1 | Using index |
|  1 | SIMPLE      | users_commerce_order                                                                           | eq_ref | PRIMARY                               | PRIMARY  | 4       | eurail_prod_interrail.commerce_order.uid                                                                    |    1 | Using where |
|  1 | SIMPLE      | commerce_customer_profile_field_data_commerce_customer_contact                                 | eq_ref | PRIMARY                               | PRIMARY  | 4       | eurail_prod_interrail.field_data_commerce_customer_contact.commerce_customer_contact_profile_id             |    1 | Using index |
|  1 | SIMPLE      | commerce_payment_transaction_commerce_order                                                    | ref    | order_id                              | order_id | 4       | eurail_prod_interrail.commerce_order.order_id                                                               |    1 | Using index |
|  1 | SIMPLE      | field_data_commerce_customer_traveler                                                          | ref    | PRIMARY,entity_type,deleted,entity_id | PRIMARY  | 391     | const,eurail_prod_interrail.commerce_order.order_id,const                                                   |    1 |             |
|  1 | SIMPLE      | commerce_customer_profile_field_data_commerce_customer_traveler                                | eq_ref | PRIMARY                               | PRIMARY  | 4       | eurail_prod_interrail.field_data_commerce_customer_traveler.commerce_customer_traveler_profile_id           |    1 | Using index |
|  1 | SIMPLE      | users_commerce_order__field_data_field_email_address                                           | ref    | PRIMARY,entity_type,deleted,entity_id | PRIMARY  | 391     | const,eurail_prod_interrail.users_commerce_order.uid,const                                                  |    1 |             |
|  1 | SIMPLE      | commerce_customer_profile_field_data_commerce_customer_contact__field_data_field_email_address | ref    | PRIMARY,entity_type,deleted,entity_id | PRIMARY  | 391     | const,eurail_prod_interrail.commerce_customer_profile_field_data_commerce_customer_contact.profile_id,const |    1 | Using where |
+----+-------------+------------------------------------------------------------------------------------------------+--------+---------------------------------------+----------+---------+-------------------------------------------------------------------------------------------------------------+------+-------------+
Если нужны структуры таблиц, скажите каких, покажу.

вот его профайлинг

Код: 
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000277 |
| checking permissions | 0.000009 |
| checking permissions | 0.000002 |
| checking permissions | 0.000017 |
| checking permissions | 0.000003 |
| checking permissions | 0.000001 |
| checking permissions | 0.000002 |
| checking permissions | 0.000001 |
| checking permissions | 0.000003 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| checking permissions | 0.000016 |
| Opening tables       | 0.000093 |
| System lock          | 0.000025 |
| init                 | 0.000063 |
| optimizing           | 0.000034 |
| statistics           | 0.000852 |
| preparing            | 0.000045 |
| executing            | 0.000026 |
| Sorting result       | 0.000005 |
| Sending data         | 9.877760 |
| end                  | 0.000018 |
| query end            | 0.000005 |
| closing tables       | 0.000018 |
| freeing items        | 0.000057 |
| logging slow query   | 0.000004 |
| cleaning up          | 0.000022 |
+----------------------+----------+
 

Paradigma

Новичок
Вот индексы таблицы commerce_order


Код: 
+----------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| commerce_order |          0 | PRIMARY      |            1 | order_id     | A         |      317104 |     NULL | NULL   |      | BTREE      |         |               |
| commerce_order |          0 | order_number |            1 | order_number | A         |      317104 |     NULL | NULL   | YES  | BTREE      |         |               |
| commerce_order |          0 | revision_id  |            1 | revision_id  | A         |      317104 |     NULL | NULL   | YES  | BTREE      |         |               |
| commerce_order |          1 | uid          |            1 | uid          | A         |      317104 |     NULL | NULL   |      | BTREE      |         |               |
| commerce_order |          1 | order_status |            1 | status       | A         |          13 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 

Paradigma

Новичок
и еще, запрос меняется в зависимости от выбраных фильтров на странице, эти два фильтра больше всего жрут ресурсов, есть конечно еще и другие, тут конечно понятно что LIKE много требует, но например когда использовать отдельно LIKE на эти таблицы отдельным запросом, тогда летает все
 
Войдите или зарегистрируйтесь для ответа.
Сверху