mysql — что быстрее? SQL-запрос с подстановочными знаками и OR, или универсальный запрос с циклом php для фильтрации?
Я запускаю запрос для одной таблицы. Мне нужно, чтобы конечный результат был массивом строк, в котором текущий пользователь указан как участник сообщения, которое записывает строка БД. Столбцы двух участников staff а также clientsи содержат сериализованные массивы идентификаторов пользователя и / или имени роли (например, a:3:{i:0;s:8:"staffman";i:1;s:3:"203";i:2;s:3:"170";}).
Будет ли быстрее попытаться отфильтровать все несоответствия для текущего пользователя по всем запросам SQL или выполнить универсальный запрос (возможно, по нескольким сотням строк), а затем выполнить цикл по ним, чтобы отфильтровать те, где пользователь не участник?
Опция SQL:
$query = "SELECT * FROM ".self::$messages;
if($is_client) $query .= " WHERE type = 'clients'";
elseif($is_staff) $query .= " WHERE type = 'staff' AND (staff LIKE '%\"".$user->ID."\"%' OR staff LIKE 'staff\"%')";
elseif(!$is_admin)
{
$query .= " WHERE type = 'staff' AND (staff LIKE '%\"".$user->ID."\"%' OR staff LIKE 'staff\"%' OR staff LIKE 'clientman%'";
if($is_staffman) $query .= " OR staff LIKE 'staffman%'";
if($is_accountant) $query .= " OR staff like 'accountant%'";
$query .= ")";
}
$query .= " ORDER BY updated DESC";
Вариант LOOP:
$col = $is_client ? 'clients' : 'staff';
$query = "SELECT * FROM ".self::$messages." WHERE type = {$col} ORDER BY updated DESC";
// do the query, then check if there are results. if results...
foreach($results as $msg)
{
if(empty($msg->$col)) continue;
$users = unserialize($msg->$col);
if($is_client)
{
if(!in_array($user->ID, $users) && !in_array('client', $users)) continue;
}
elseif(!$is_admin && $msg->created_by != $user->ID)
{
if(!in_array($user->ID, $users))
{
if(!in_array('staff', $users))
{
if(!$is_clientman || !in_array('clientman', $users))
{
if(!$is_staffman || !in_array('staffman', $users))
{
if(!$is_accountant || !in_array('accountant', $users)) continue;
}
}
}
}
}
// match found, do stuff with it here
}
Решение
Вы всегда должны использовать опцию SQL в таком случае.
$query = "SELECT * FROM ".self::$messages;
if($is_client) $query .= " WHERE type = 'clients'";
elseif($is_staff) $query .= " WHERE type = 'staff' AND (staff LIKE '%\"".$user->ID."\"%' OR staff LIKE 'staff\"%')";
elseif(!$is_admin)
{
$query .= " WHERE type = 'staff' AND (staff LIKE '%\"".$user->ID."\"%' OR staff LIKE 'staff\"%' OR staff LIKE 'clientman%'";
if($is_staffman) $query .= " OR staff LIKE 'staffman%'";
if($is_accountant) $query .= " OR staff like 'accountant%'";
$query .= ")";
}
$query .= " ORDER BY updated DESC";
Зачем ?
1. Посмотрите, что вы уже получаете меньше записей (они нужны только) путем фильтрации по предложению WHERE. Нет смысла извлекать все записи и фильтровать их в php.
2. Увидеть головную боль, чтобы управлять кодом в PHP, как он стал сложным.
3. Меньше строк.
4. Большую часть времени он управляется SQL, логика php замедлит его.
Другие решения
Я принял ответ выше, потому что это был четкий и окончательный ответ на поставленный мной вопрос. Тем не менее, Стюарт (в комментариях) предложил мне попытаться нормализовать свою структуру, а не сканировать сериализованные массивы с помощью LIKES и т.п. Вот что я сделал.
Вместо сохранения моего массива участников в сериализованной форме в той же таблице, что и messages таблица, я создал новую таблицу под названием message_recipients всего три колонки (id,mid,user), где mid относится к id колонка из messages стол и где user хранит индивидуальный идентификатор пользователя или имя роли.
Таким образом, самая сложная часть (которая на самом деле не была такой сложной) заключалась в том, как управлять добавлением и удалением строк из message_recipients таблица, когда пользователь изменяет разрешенных получателей для данного сообщения. (В форме у них есть два раскрывающихся списка с несколькими выборками — один для персонала, другой для клиентов).
Итак, вот как я справился с обновленной / изменяющейся частью участников:
Для каждой области (персонал и клиенты) я передаю свою функцию двум массивам, один с новыми участниками и один со старыми участниками. (Если сообщение создается, то массив старых участников просто пуст.)
Тогда вот функция (в основном только четыре строки фактической работы):
public function update_participants($mid = false, $new_staff = array(), $old_staff = array(), $new_clients = array(), $old_clients = array())
{
global $wpdb;
if(empty($mid)) return array();
$add = array_filter(array_unique(array_merge(array_diff($new_staff, $old_staff), array_diff($new_clients, $old_clients))));
$delete = array_filter(array_unique(array_merge(array_diff($old_staff, $new_staff), array_diff($old_clients, $new_clients))));
if(!empty($add)) $wpdb->query("INSERT INTO ".self::$participants." (mid,user) VALUES(".$mid.",\"".implode("\"),(".$mid.",\"", $add)."\")");
if(!empty($delete)) $wpdb->query("DELETE FROM ".self::$participants." WHERE mid = {$mid} AND user IN (\"".implode("\", \"", $delete)."\")");
return array('added'=>$add, 'deleted'=>$delete);
}
Затем я возвращаю полученные массивы людей, которые были добавлены или удалены, чтобы я мог поиграть с этим в своем материале уведомлений по электронной почте.
Затем, теперь, когда мы нормализовались, первоначальная проблема запроса таблицы для получения всех сообщений, в которых текущий пользователь является участником, стала намного быстрее и проще:
$query = 'SELECT DISTINCT a.* FROM '.self::$messages.' a, '.self::$participants.' b';
if(!$is_admin)
{
$query .= ' WHERE (b.user = '.$user->ID;
if($is_client) $query .= ' OR b.user = "client"';
elseif($is_staff) $query .= ' OR b.user = "staff"';
else
{
if($is_clientman) $query .= ' OR b.user = "clientman"';
if($is_staffman) $query .= ' OR b.user = "staffman"';
if($is_accountant) $query .= ' OR b.user = "accountant"';
$query .= ' OR a.created_by = '.$user->ID;
}
$query .= ') AND b.mid = a.id';
}
$query .= ' ORDER BY a.updated DESC';
Администраторы получают все, а все остальные получают сообщения только в том случае, если в таблице участников указан их идентификатор пользователя или роль пользователя. (Некоторые роли имеют несколько ролей, следовательно, тройной if заявления, а не elseifs,
Я проверил это с каждой возможной конфигурацией с различными пользователями. Работает как шарм.
И никаких LIKE, никаких подстановочных знаков и никаких циклов PHP.
Спасибо всем за помощь.