запросег (для отоморозков)

[zingel]

Существует кривой запрос:

Код: Выделить всё

UPDATE dbmail_messages SET deleted_flag ='1' WHERE mailbox_idnr =(SELECT message_idnr, dbmail_physmessage.internal_date, dbmail_messages.physmessage_id, unique_id, rfcsize, messagesize, seen_flag, answered_flag, deleted_flag, status FROM dbmail_messages LEFT JOIN dbmail_physmessage ON dbmail_messages.physmessage_id = dbmail_physmessage.id WHERE dbmail_messages.mailbox_idnr = (SELECT spambox FROM user_prefs) AND dbmail_messages.mailbox_idnr =  (select message_idnr from  dbmail_messages m join dbmail_mailboxes b ON
m.mailbox_idnr=b.mailbox_idnr JOIN dbmail_physmessage p ON
p.id=m.physmessage_id WHERE p.internal_date < 'NOW - 30 days' ) AND status=3 AND seen_flag=1;

как бы его улучьшить? (не надо мне ссылки на общее

[Хостинг HostFood.ru]

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2000 рублей (HP Proliant G5, Intel Xeon E5430 (2.66GHz, Quad-Core, 12Mb), 8Gb RAM, 2x300Gb SAS HDD, P400i, 512Mb, BBU):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/

[Гость]

как бы его улучьшить?

честно говоря с трудом верится, что он вообще работает

Код: Выделить всё

UPDATE 
	dbmail_messages 
SET 
	deleted_flag ='1' 
WHERE 
	mailbox_idnr = 
	(
		SELECT 
			message_idnr, 
			dbmail_physmessage.internal_date, 
			dbmail_messages.physmessage_id, 
			unique_id, 
			rfcsize, 
			messagesize, 
			seen_flag, 
			answered_flag, 
			deleted_flag, 
			status 

		FROM 
			dbmail_messages 
				LEFT JOIN 
					dbmail_physmessage 
				ON 
					dbmail_messages.physmessage_id = dbmail_physmessage.id 

		WHERE 
			dbmail_messages.mailbox_idnr = 
			(
				SELECT 
					spambox 
				FROM 
					user_prefs
			) 
				AND 
			dbmail_messages.mailbox_idnr = 
			(
				select 
					message_idnr 
				from 
					dbmail_messages m 
						join 
							dbmail_mailboxes b 
						ON
							m.mailbox_idnr = b.mailbox_idnr 

						JOIN 
							dbmail_physmessage p 
						ON 
							p.id = m.physmessage_id 

				WHERE 
					p.internal_date < 'NOW - 30 days' 
			) 
				AND 
			status = 3 
				AND 
			seen_flag = 1;

элементарный подсчёт скобок показывает интригующее соотношение 3:2

[zingel]

элементарный подсчёт скобок показывает интригующее соотношение 3:2

ну в общем то да (после суток у меня они все на 1 лицо, ну и потом я опытный и у меня есть тестовая база - там не жалко и упустить) упростил до такого (вы тут не 1 маньяк);

Код: Выделить всё

UPDATE dbmail_messages SET deleted_flag=1,status=2 WHERE message_idnr IN (SELECT message_idnr FROM dbmail_messages m INNER JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr INNER JOIN dbmail_physmessage p ON p.id=m.physmessage_id WHERE b.name LIKE 'SPAM' AND p.internal_date >= current_timestamp - interval '7 days');

задача запроса - удалять сообщения в папке spam которые старше 7 дней.

Годный на Ваш взгляд или можно как-то изящнее?

[Гость]

Код: Выделить всё

UPDATE 
	dbmail_messages 
SET 
	deleted_flag = 1,
	status = 2 

WHERE 
	message_idnr IN 
	(
		SELECT 
			message_idnr 

		FROM 
			dbmail_messages m 
				INNER JOIN 
					dbmail_mailboxes b 
				ON 
					m.mailbox_idnr = b.mailbox_idnr 

				INNER JOIN 
					dbmail_physmessage p 
				ON 
					p.id = m.physmessage_id 

		WHERE 
			b.name LIKE 'SPAM' 
				AND 
			p.internal_date >= current_timestamp - interval '7 days'
	);

при таком запросе данные из подзапроса попадут во временную таблицу без индекса, в результате услвие IN будет перебирать все данные подзапроса подряд. Тут будет логарифмическая зависимость между количеством данных и временем работы скрипта. Если же сделать отдельную временную таблицу с индексом по message_idnr, то зависимость станет линейная, что приведёт к заметному росту производительности на больших объёмах данных.

[zingel]

на самом деле это не столь Важно скрипт 1-2-3 разовый (к примеру упадёт бекэнд с филтрацией и всё будет лупиться как спам или какой паразит нагенерирует боунсов которые пометятся как спам e.t.c.) суть в том, что мне досталась база весом 6 гигабайт от старого админа и теперь порой нужно проходить по ящикам и убивать там старые письма, думаю написать скрипт и в крон его раз в месяц, то что он грузный это понятно, по этому 1 раз в месяц глубоко ночью.

Если с такой точки зрения, годный?

[Гость]

Если с такой точки зрения, годный?

если работает, то вполне

[zingel]

Код: Выделить всё

dbmail=# UPDATE dbmail_messages SET deleted_flag=1,status=2 WHERE message_idnr IN (SELECT message_idnr FROM dbmail_messages m INNER JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr INNER JOIN dbmail_physmessage p ON p.id=m.physmessage_id WHERE b.name LIKE 'SPAM' AND p.internal_date >= current_timestamp - interval '7 days');
UPDATE 28348
dbmail=#

отработал без нареканий....чего-то мне всеравно не нравиться что-то в нём, пока не понял что...ну вот вобщем кому нужно фунция(DBI:Pg)

1)Убивает ящики без пользователя
2)Не удалившиеся по какой-то причине
3)Чистящая спам (при желании можно вынести *spam* в отдельную переменную)

Код: Выделить всё

!/usr/bin/perl
use DBI();
use strict;
use warnings;

my $DBMA_dbname     = "база";
my $DBMA_port       = "3306";
my $DBMA_host       = "хост";
my $DBMA_user       = "юзер";
my $DBMA_password   = "пароль";
my $sqltype         = "pgsql";


my ( $message_idnr, $dbh, $sth );
if ($sqltype eq "pgsql")
    {
unless (
    $dbh = DBI->connect(
"DBI:Pg:user=$DBMA_user;password=$DBMA_password;dbname=$DBMA_dbname;port=$DBMA_port;host=$DBMA_host",
        "$DBMA_user",
        "$DBMA_user",
        { AutoCommit => 1 }
    )
  )
{
    print "$DBI::errstr";
    exit;
}
&dbmail_check4;
}

sub dbmail_check4 {
    $sth =
      $dbh->prepare(
"UPDATE dbmail_messages SET status = '003' where  deleted_flag = '1' OR status >0"
      );
    $sth->execute();
    $sth->finish();
    $sth = $dbh->prepare(
        "SELECT message_idnr from dbmail_messages
JOIN dbmail_mailboxes ON dbmail_messages.mailbox_idnr = dbmail_mailboxes.mailbox_idnr
LEFT JOIN dbmail_users ON dbmail_users.user_idnr = dbmail_mailboxes.owner_idnr
WHERE dbmail_users.user_idnr IS NULL"
    );
    $sth->execute();
    while ( ($message_idnr) = $sth->fetchrow_array ) {
        $sth =
          $dbh->prepare(
"UPDATE dbmail_messages
SET deleted_flag=1,status=2
WHERE message_idnr IN
(SELECT message_idnr FROM dbmail_messages m
INNER JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
INNER JOIN dbmail_physmessage p ON p.id=m.physmessage_id
WHERE b.name LIKE 'SPAM' AND p.internal_date >=
current_timestamp - interval '7 days');"
          );
        $sth->execute();
    }
    $sth->finish();
    $sth = $dbh->prepare(
        "UPDATE dbmail_messages
LEFT JOIN dbmail_mailboxes ON dbmail_messages.mailbox_idnr = dbmail_mailboxes.mailbox_idnr
SET dbmail_messages.deleted_flag = '1', status = '001'
WHERE dbmail_mailboxes.mailbox_idnr IS NULL"
    );
    $sth->execute();
    $sth->finish();
    print "\nKill me?.\n
so sloooow.\n
(kill -9)\n\n";
    exit;
}

[ss25]

Дурной тон выкладывать проблемные запросы без EXPLAIN ANALYZE.

[zingel]

да что Вы?

p.s. дурной тон пользовать чужие скрипты не подумав и не заточив под себя (с)

[ss25]

да что Вы?

Без комментариев.

p.s. дурной тон пользовать чужие скрипты не подумав и не заточив под себя (с)

Не понял причем тут я.

========================>
ТС без обид как можно что либо улучшить не зная процессов происходящих внутри.
п.с. тема больше мне не интересна.

[ProFTP]

оптимизировать можно по разному... разные запросы и разные нагрузки

тебе эти запросы нужны сейчас или кому? если да, то бери и оптимизируй...

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

что тут не понятного?

[Dirty.Eager]

элементарный подсчёт скобок показывает интригующее соотношение 3:2

ну в общем то да (после суток у меня они все на 1 лицо, ну и потом я опытный и у меня есть тестовая база - там не жалко и упустить) упростил до такого (вы тут не 1 маньяк);

Код: Выделить всё

UPDATE dbmail_messages SET deleted_flag=1,status=2 WHERE message_idnr IN (SELECT message_idnr FROM dbmail_messages m INNER JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr INNER JOIN dbmail_physmessage p ON p.id=m.physmessage_id WHERE b.name LIKE 'SPAM' AND p.internal_date >= current_timestamp - interval '7 days');

задача запроса - удалять сообщения в папке spam которые старше 7 дней.

Годный на Ваш взгляд или можно как-то изящнее?

А что, нельзя без IN обойтись? Вот так, например:

Код: Выделить всё

UPDATE  dbmail_messages m
SET     deleted_flag=1,
        status=2 
FROM    dbmail_mailboxes b, 
        dbmail_physmessage p
WHERE   m.mailbox_idnr=b.mailbox_idnr 
        AND     p.id=m.physmessage_id 
        AND     b.name LIKE 'SPAM'
        AND     p.internal_date >= current_timestamp - interval '7 days'

[zingel]

я просто думали что Вы хотите меня упрекнуть, сори