Помогите оптимизировать запрос для миллиона записей.

Павел Шклярик
Дата: 29.01.2013 20:21:37
SELECT 
        cmns.id AS id,
	cmns.title AS title,
	cmns.cdate AS cdate,
	cmns.status AS status,
	(SELECT IF((cmns.account_id > 0),(SELECT a.status FROM accounts AS a WHERE a.id = cmns.account_id),cmns.status)) AS common_status,
	cmns.account_id AS account_id,
	CONCAT(cmns.title,cmns.status,(SELECT IFNULL(SUM(cmpl.damage),0)FROM complaints as cmpl WHERE cmns.id = defendant_id),
	(SELECT IFNULL(SUM(refunded),0) FROM complaints WHERE cmns.id = defendant_id ),(SELECT 	COUNT(*)
					FROM complaints as cmpl
					INNER JOIN accounts as a ON(cmpl.claimant_id = a.id)
				WHERE cmpl.status <> 'deleted'
					AND a.status <> 'deleted'
					AND cmpl.defendant_id = cmns.id),(SELECT COUNT(*)
					FROM comments as com
					INNER JOIN complaints as cmpl ON(com.complaint_id = cmpl.id)
				WHERE com.status <> 'deleted'
					AND cmpl.status <> 'deleted'
					AND com.account_id = cmns.account_id)) AS search,(SELECT SUM(damage) FROM complaints WHERE cmns.id = defendant_id ) AS sum_damage,(SELECT SUM(damage) FROM complaints WHERE cmns.id = defendant_id AND refunded > 0 ) AS sum_refunded,(
				SELECT 	COUNT(*)
					FROM complaints as cmpl
					INNER JOIN accounts as a ON(cmpl.claimant_id = a.id)
				WHERE cmpl.status <> 'deleted'
					AND a.status <> 'deleted'
					AND cmpl.defendant_id = cmns.id
	) AS cnt_complaints,
	(SELECT COUNT(*)
					FROM comments as com
					INNER JOIN complaints as cmpl ON(com.complaint_id = cmpl.id)
					INNER JOIN accounts as a ON(cmpl.claimant_id = a.id)
					INNER JOIN companies as c2 ON(cmpl.defendant_id = c2.id)
					WHERE com.status <> 'deleted'
					AND cmpl.status <> 'deleted'
					AND a.status <> 'deleted'
					AND c2.status <> 'deleted'
					AND com.account_id = cmns.account_id
	) AS cnt_comments 
	
	FROM companies as cmns WHERE cmns.status IN ('active') ORDER BY title ASC LIMIT 239500,20;

Если лимит установить в LIMIT 1,20 загружается за 0.4 секунды.
Если лимит установлен в LIMIT 239500,20 то уже за 28.8 секунд.

В базе пока 300 тысяч записей. Но скоро там будет 6-8 млн. записей.
Я думаю заменить что можно на JOIN но как заменить конкатенацию агрегаций на JOIN ума не приложу возможно ли это?
javajdbc
Дата: 29.01.2013 20:28:14
Павел Шклярик,

а какой такой сермяжный смысл в
LIMIT 239500,20 ?
Павел Шклярик
Дата: 29.01.2013 20:29:46
Вот EXPLAIN
+----+--------------------+------------+--------+----------------------------------+--------------+---------+-----------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+----------------------------------+--------------+---------+-----------------------+--------+-----------------------------+
| 1 | PRIMARY | cmns | ALL | NULL | NULL | NULL | NULL | 244634 | Using where; Using filesort |
| 11 | DEPENDENT SUBQUERY | com | ref | account_id,complaint_id | account_id | 4 | cpa.cmns.account_id | 6 | Using where |
| 11 | DEPENDENT SUBQUERY | cmpl | eq_ref | PRIMARY,claimant_id,defendant_id | PRIMARY | 4 | cpa.com.complaint_id | 1 | Using where |
| 11 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 4 | cpa.cmpl.claimant_id | 1 | Using where |
| 11 | DEPENDENT SUBQUERY | c2 | eq_ref | PRIMARY | PRIMARY | 4 | cpa.cmpl.defendant_id | 1 | Using where |
| 10 | DEPENDENT SUBQUERY | cmpl | ref | claimant_id,defendant_id | defendant_id | 4 | cpa.cmns.id | 1 | Using where |
| 10 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 4 | cpa.cmpl.claimant_id | 1 | Using where |
| 9 | DEPENDENT SUBQUERY | complaints | ref | defendant_id | defendant_id | 4 | cpa.cmns.id | 1 | Using where |
| 8 | DEPENDENT SUBQUERY | complaints | ref | defendant_id | defendant_id | 4 | cpa.cmns.id | 1 | |
| 7 | DEPENDENT SUBQUERY | com | ref | account_id,complaint_id | account_id | 4 | cpa.cmns.account_id | 6 | Using where |
| 7 | DEPENDENT SUBQUERY | cmpl | eq_ref | PRIMARY | PRIMARY | 4 | cpa.com.complaint_id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | cmpl | ref | claimant_id,defendant_id | defendant_id | 4 | cpa.cmns.id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 4 | cpa.cmpl.claimant_id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | complaints | ref | defendant_id | defendant_id | 4 | cpa.cmns.id | 1 | |
| 4 | DEPENDENT SUBQUERY | cmpl | ref | defendant_id | defendant_id | 4 | cpa.cmns.id | 1 | |
| 3 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 4 | cpa.cmns.account_id | 1 | |
+----+--------------------+------------+--------+----------------------------------+--------------+---------+-----------------------+--------+-----------------------------+
Павел Шклярик
Дата: 29.01.2013 20:30:29
javajdbc,
Ну там постраничная навигация по каталогу компаний
javajdbc
Дата: 29.01.2013 20:41:25
Павел Шклярик
javajdbc,
Ну там постраничная навигация по каталогу компаний


Местная обшественность, обсуждая здесь такую ситуацию
раз 30 или 40, пришла к выводу:

Надо послать освсем подальше начальников которые
требуют сделать просмотр дальше чем 5 -10 страниц.
Вы реально думаете что кто-то будет
листать 10.000 (десять тысяч!) страниц?

Поишите темы в данном форуме по словам
"постраничный вывод", "pagination" и еше
какой-то термин был по русски, забыл.
javajdbc
Дата: 29.01.2013 20:46:19
Павел Шклярик
Вот EXPLAIN
+----+--------------------+------------+--------+----------------------------------+--------------+---------+-----------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+----------------------------------+--------------+---------+-----------------------+--------+-----------------------------+
| 1 | PRIMARY | cmns | ALL | NULL | NULL | NULL | NULL | 244634 | Using where; Using filesort |
| 11 | DEPENDENT SUBQUERY | com | ref | account_id,complaint_id | account_id | 4 | cpa.cmns.account_id | 6 | Using where |
| 11 | DEPENDENT SUBQUERY | cmpl | eq_ref | PRIMARY,claimant_id,defendant_id | PRIMARY | 4 | cpa.com.complaint_id | 1 | Using where |
| 11 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 4 | cpa.cmpl.claimant_id | 1 | Using where |
| 11 | DEPENDENT SUBQUERY | c2 | eq_ref | PRIMARY | PRIMARY | 4 | cpa.cmpl.defendant_id | 1 | Using where |
| 10 | DEPENDENT SUBQUERY | cmpl | ref | claimant_id,defendant_id | defendant_id | 4 | cpa.cmns.id | 1 | Using where |
| 10 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 4 | cpa.cmpl.claimant_id | 1 | Using where |
| 9 | DEPENDENT SUBQUERY | complaints | ref | defendant_id | defendant_id | 4 | cpa.cmns.id | 1 | Using where |
| 8 | DEPENDENT SUBQUERY | complaints | ref | defendant_id | defendant_id | 4 | cpa.cmns.id | 1 | |
| 7 | DEPENDENT SUBQUERY | com | ref | account_id,complaint_id | account_id | 4 | cpa.cmns.account_id | 6 | Using where |
| 7 | DEPENDENT SUBQUERY | cmpl | eq_ref | PRIMARY | PRIMARY | 4 | cpa.com.complaint_id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | cmpl | ref | claimant_id,defendant_id | defendant_id | 4 | cpa.cmns.id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 4 | cpa.cmpl.claimant_id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | complaints | ref | defendant_id | defendant_id | 4 | cpa.cmns.id | 1 | |
| 4 | DEPENDENT SUBQUERY | cmpl | ref | defendant_id | defendant_id | 4 | cpa.cmns.id | 1 | |
| 3 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 4 | cpa.cmns.account_id | 1 | |
+----+--------------------+------------+--------+----------------------------------+--------------+---------+-----------------------+--------+-----------------------------+


По мсылу запроса, нет никаких вариантов
убыстрить -- очевидно селективность по флагу АКТИВ будеть
никакая -- полный скан обеспечен...
ну можетбыть индекс по ТИТЛЕ поможет сортировке....хотя врядли
javajdbc
Дата: 29.01.2013 20:49:57
измерьте скорость:

select id from 
FROM companies as cmns 
WHERE cmns.status IN ('active') 
ORDER BY title
LIMIT 239500,20


потом сделайте индекс двойной: (ТИТЛЕ,ИД)
и повторите замер.

Если будет приемлемая скорость, то задача решена:
сделаете этот под-слелецт ИД в FROM секции.
netwind
Дата: 29.01.2013 23:02:20
javajdbc
Вы реально думаете что кто-то будет
листать 10.000 (десять тысяч!) страниц?

яндекс и гугл.
javajdbc
Дата: 29.01.2013 23:05:08
netwind
javajdbc
Вы реально думаете что кто-то будет
листать 10.000 (десять тысяч!) страниц?

яндекс и гугл.


зае.утся ! (пардон май френч)
netwind
Дата: 29.01.2013 23:08:21
javajdbc, роботы не устают.
И это постоянно происходит. Суть индексации в том чтобы предоставить полную информацию и для этого они протыкивают все возможные страницы.