Форум пользователей MySQL

seyfer · 11.12.2011 13:12:39

Добрый день.

Ситуация такая. Во первых мы имеем дело с большой таблицей debtors 3000-5000 записей. Мне нужно выбрать из этой таблицы записи только те, которые не входят в записи, получаемые другим запросом.

Сам запрос

$obz_query = 

        "SELECT debtors.*, FROM_UNIXTIME(block_start_date) AS date

        FROM debtors 

        WHERE users_id NOT IN(SELECT users_id FROM debtors, complete, notes        

        WHERE block_start_date < compl_date AND compl_note = note_id 

        AND n_abon_id = users_id AND is_juridical = 0)

        AND is_juridical = 0 

        ORDER BY {$_SESSION['sort']} {$sort_by} 

        LIMIT $start, $num";

Complete - база с обработанными debtors.
Notes - заметки к debtors.

Соответственно через notes идет связь между Complete и Notes по compl_note = note_id AND n_abon_id = users_id.
Сейчас в Complete и Notes по 500 записей но они будут расти до 10000 и больше.

Нагрузка на сервер получается дикая.

Как-то можно иначе составить запрос?

deadka · 11.12.2011 16:27:45

Добрый день.

Конструция not in обычно плохо работает в mysql, как вариант - переписать запрос через join.
Помимо этого:
еще покажите пожалуйста explain всего запроса,
explain подзапроса, и
show create table всех табличек, а то не очень понятно, какие поля к какой таблице относятся.

seyfer · 12.12.2011 05:58:15

--

-- Table structure for table `complete`

--

CREATE TABLE `complete` (

  `compl_id` int(11) NOT NULL AUTO_INCREMENT,

  `compl_note` int(11) NOT NULL,

  `c_full_name` varchar(255) NOT NULL,

  `c_address` varchar(255) NOT NULL,

  `c_w_telephone` varchar(100) NOT NULL,

  `c_h_telephone` varchar(100) NOT NULL,

  `c_m_telephone` varchar(100) NOT NULL,

  `c_balance` float NOT NULL,

  `c_credit` float NOT NULL,

  `c_services` varchar(255) NOT NULL,

  `c_tariff_name` varchar(120) NOT NULL,

  `c_account_id` int(11) NOT NULL,

  `c_block_date` int(11) NOT NULL,

  `compl_date` int(11) NOT NULL,

  PRIMARY KEY (`compl_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=99 ;

-- --------------------------------------------------------

--

-- Table structure for table `debtors`

--

CREATE TABLE `debtors` (

  `users_id` int(11) NOT NULL DEFAULT '0',

  `full_name` varchar(255) NOT NULL,

  `actual_address` varchar(255) NOT NULL,

  `building` varchar(15) NOT NULL,

  `flat_number` varchar(15) NOT NULL,

  `work_telephone` varchar(100) NOT NULL,

  `home_telephone` varchar(100) NOT NULL,

  `mobile_telephone` varchar(100) NOT NULL,

  `balance` float NOT NULL DEFAULT '0',

  `credit` float NOT NULL DEFAULT '0',

  `services` varchar(255) NOT NULL,

  `tariff_name` varchar(120) NOT NULL DEFAULT ' ',

  `account_id` int(11) NOT NULL DEFAULT '0',

  `block_start_date` int(11) NOT NULL DEFAULT '0',

  `is_juridical` int(11) NOT NULL DEFAULT '0',

  KEY `users_id` (`users_id`)

) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

-- --------------------------------------------------------

--

-- Table structure for table `notes`

--

CREATE TABLE `notes` (

  `note_id` int(11) NOT NULL AUTO_INCREMENT,

  `comment` text NOT NULL,

  `n_oper_id` int(5) NOT NULL,

  `n_abon_id` int(11) NOT NULL,

  `n_why_id` int(3) NOT NULL,

  `n_date` int(11) NOT NULL,

  PRIMARY KEY (`note_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=102 ;

deadka · 12.12.2011 06:01:44

explain запросов (полного и отдельно подзапроса) приложите плиз.

seyfer · 12.12.2011 06:02:24

Вот так выглядит без PHP

SELECT debtors . * , FROM_UNIXTIME( block_start_date ) AS DATE

FROM debtors

WHERE users_id NOT 

IN (

SELECT users_id

FROM debtors, complete, notes

WHERE block_start_date < compl_date

AND compl_note = note_id

AND n_abon_id = users_id

AND is_juridical =0

)

AND is_juridical =0

ORDER BY DATE DESC 

LIMIT 0 , 20

Я что-то не пойму логику с Join как сделать...

deadka · 12.12.2011 06:04:04

Покажите результат

explain SELECT debtors . * , FROM_UNIXTIME( block_start_date ) AS DATE
FROM debtors
WHERE users_id NOT
IN (

SELECT users_id
FROM debtors, complete, notes
WHERE block_start_date < compl_date
AND compl_note = note_id
AND n_abon_id = users_id
AND is_juridical =0
)
AND is_juridical =0
ORDER BY DATE DESC
LIMIT 0 , 20

и

explain SELECT users_id
FROM debtors, complete, notes
WHERE block_start_date < compl_date
AND compl_note = note_id
AND n_abon_id = users_id
AND is_juridical =0

seyfer · 12.12.2011 06:05:04

Полного

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY debtors ALL NULL NULL NULL NULL 3706 Using where; Using filesort
2 DEPENDENT SUBQUERY debtors ref users_id users_id 4 func 1 Using where
2 DEPENDENT SUBQUERY complete ALL NULL NULL NULL NULL 93 Using where; Using join buffer
2 DEPENDENT SUBQUERY notes eq_ref PRIMARY PRIMARY 4 rightside_debt.complete.compl_note 1 Using where

Подзапроса

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE complete ALL NULL NULL NULL NULL 93
1 SIMPLE notes eq_ref PRIMARY PRIMARY 4 rightside_debt.complete.compl_note 1
1 SIMPLE debtors ref users_id users_id 4 rightside_debt.notes.n_abon_id 1 Using where

deadka · 12.12.2011 06:16:16

Так-с...

Для начала

добавьте составной ключ на deptors.users_id,deptors.is_juridical
alter table deptors add index users_id__is_juridical(users_id,is_juridical);

И ключ на complete.compl_date
alter table `complete` add index compl_date(compl_date);

И составной ключ на debtors.is_juridical,debtors.block_start_date
alter table debtors add index is_juridical__block_start_date(is_juridical,block_start_date);

Есть разница в скорости?

explain снова приведите плиз.

Отредактированно deadka (12.12.2011 06:29:28)

seyfer · 12.12.2011 06:21:18

Сделаю.

Я вот погуглил.

1
подскажите как составить запрос который бы выводил только те поля из l_client где l_client.l_client_id != b_account.l_client_id?

2
SELECT ..
FROM
l_client
LEFT JOIN
b_account
ON l_client.l_client_id = b_account.l_client_id
WHERE b_account.l_client_id IS NULL;

имхо так.

Может на основе этого попробовать составить запрос.

seyfer · 12.12.2011 06:23:50

alter table complete add index is_juridical__date(is_juridical,date);

Тут date у меня нету, это у меня название было AS date. А так block_start_date ставить?

deadka · 12.12.2011 06:24:32

Ну да, идея left join для данного запроса именно такова.

Что-то такое (не проверял)

SELECT debtors . * , FROM_UNIXTIME( debtors . block_start_date ) AS DATE

FROM debtors left join

(

SELECT users_id

FROM debtors, complete, notes

WHERE block_start_date < compl_date

AND compl_note = note_id

AND n_abon_id = users_id

AND is_juridical =0

) t using(users_id) WHERE t.users_id IS NULL and

is_juridical =0

ORDER BY DATE DESC

LIMIT 0 , 20

deadka · 12.12.2011 06:26:30

Поправил сообщение, где перечислены рекомендуемые индексы.

Отредактированно deadka (12.12.2011 06:29:59)

seyfer · 12.12.2011 06:29:16

 Key column 'is_juridical' doesn't exist in table

Он в debtors. Сейчас проверю с первыми двумя индексами, напишу explain

seyfer · 12.12.2011 06:32:47

Полный

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY debtors range is_juridical__block_start_date is_juridical__block_start_date 4 NULL 3475 Using where; Using filesort
2 DEPENDENT SUBQUERY debtors ref users_id,users_id__is_juridical,is_juridical__bloc... users_id 4 func 1 Using where
2 DEPENDENT SUBQUERY complete ALL compl_date NULL NULL NULL 93 Using where; Using join buffer
2 DEPENDENT SUBQUERY notes eq_ref PRIMARY PRIMARY 4 rightside_debt.complete.compl_note 1 Using where

Подзапрос

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE complete ALL compl_date NULL NULL NULL 93
1 SIMPLE notes eq_ref PRIMARY PRIMARY 4 rightside_debt.complete.compl_note 1
1 SIMPLE debtors ref users_id,users_id__is_juridical,is_juridical__bloc... users_id 4 rightside_debt.notes.n_abon_id 1 Using where

Скорость вроде как подросла, но незначительно. Надо делать с left join. Попробую.
Благодарю за наводку.

Отредактировал с третьим индексом.

Отредактированно seyfer (12.12.2011 06:35:27)

deadka · 12.12.2011 06:34:46

Странно. Покажите еще раз show create table таблиц плиз...
Или приложите дамп таблиц, сам поковыряю чуть позже.

Отредактированно deadka (12.12.2011 06:35:18)

seyfer · 12.12.2011 06:40:11

Мой запрос с NOT IN - Showing rows 0 - 19 (20 total, Query took 2.8619 sec)

Ваш запрос с LEFT JOIN (кстати работает)
Showing rows 0 - 19 (20 total, Query took 0.0606 sec)

Разница на лицо. огромная благодарность от меня Вам.
Сейчас попробую внедрить в приложение.

deadka · 12.12.2011 06:42:25

Да, хороший прирост . Пожалуйста. А если уж так душа просит использования in и not in - то попробуйте MariaDB.

seyfer · 14.12.2011 08:35:10

теперь другая проблема

http://sqlinfo.ru/forum/viewtopic.php?pid=28995

Форум пользователей MySQL

#1 11.12.2011 13:12:39

Очень медленно работает запрос с подзапросом NOT IN ()

#2 11.12.2011 16:27:45

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#3 12.12.2011 05:58:15

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#4 12.12.2011 06:01:44

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#5 12.12.2011 06:02:24

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#6 12.12.2011 06:04:04

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#7 12.12.2011 06:05:04

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#8 12.12.2011 06:16:16

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#9 12.12.2011 06:21:18

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#10 12.12.2011 06:23:50

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#11 12.12.2011 06:24:32

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#12 12.12.2011 06:26:30

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#13 12.12.2011 06:29:16

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#14 12.12.2011 06:32:47

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#15 12.12.2011 06:34:46

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#16 12.12.2011 06:40:11

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#17 12.12.2011 06:42:25

Re: Очень медленно работает запрос с подзапросом NOT IN ()

#18 14.12.2011 08:35:10

Re: Очень медленно работает запрос с подзапросом NOT IN ()

Board footer