Помогите составить быстрый запрос SElect затем Update

Тема в разделе "Базы данных", создана пользователем silmarion, 16 сен 2014.

Модераторы: latteo
  1. silmarion

    silmarion

    Регистр.:
    21 июн 2012
    Сообщения:
    195
    Симпатии:
    19
    Доброго всем дня, провожу выборку из Ьд, запись выбранных строк в файл, затем Update использованных строк в таблице, все происходит оооооочень долго. А долго всё это дело происходит именно из-за UPDATE, неверно составлено.
    Помогите пожалуйста оптимизировать.

    Код:
    $ff = fopen("test.txt", "a");
    $ath = mysql_query("select mail,author from `emails` where `checked` = '0' ORDER BY 'id' LIMIT 10");
    while ($author = mysql_fetch_array($ath))
    {
    $mytext2 = $author['mail'].":".$author['author ']."\n";
    $a=$author['mail'];
    $test = fwrite($ff, $mytext2);
    $ch=mysql_query("update `emails` set `checked`='1' where `mail` LIKE '$a'");
    }
    fclose($ff);
    
    Получается делаю выборку 10 строк из бд, пишу их в файл, затем помечаю выбранные строки чтобы они в дальнейшем не попадали под выбор.

    Как всё это дело объединить, а то получается делаешь 1 select все быстро, но затем 10 UPDATE портит всю малину.

    Нашел похожую тему, но там ответа тоже не нашел(
    https://www.nulled.cc/threads/216308/
     
    Последнее редактирование: 16 сен 2014
  2. Demian12

    Demian12 Создатель

    Регистр.:
    13 авг 2014
    Сообщения:
    12
    Симпатии:
    9
    Варианты ускорения:
    1. Простейший. Заменить LIKE на '=' (условие `mail` = '$a') и сделать индекс по столбцу mail.
    Если не хватит, то:
    2. Сделать строку из мэйлов в кавычках через запятую и делать 1 update (индекс тоже нужен). Конечный запрос должен иметь вид:
    PHP:
    "update `emails` set `checked`='1' where `mail` IN ($x)"
    а $x выглядеть примерно так - 'mail1@example.com','mail2@example2.com' и т.д. (с кавычками и запятыми).
    Т.е. разобранный запрос после подстановки будет такой:
    Код:
    update `emails` set `checked`='1' where `mail` IN ('mail1@example.com','mail2@example2.com')
    Скорость в 10 раз выше, 5000 запросов 448 сек, сейчас 45 секунд

    Как то странно модератор отредактировал сообщение) взял кусок от моего сообщения, и поставил в твоё.
    Ну да ладно, все равно спасибо. И модераторы, почему я могу сообщение Demian12 редактировать?
     
    Последнее редактирование модератором: 16 сен 2014
    silmarion и latteo нравится это.
  3. DarkDalamar

    DarkDalamar Создатель

    Регистр.:
    24 июл 2013
    Сообщения:
    22
    Симпатии:
    1
    Помогите составить запрос к БД пожалуйста.
    Есть БД от магазина на Opencart, нужно в таблице описание `oc_product_description` в поле `description` найти определенное слово (пусть это будет для примера "код") и заменить в этом же поле сам код (он все время разный, и как его искать я вообще не понимаю) на код из поля "Код товара".
    Помогите, кто силен, уже всю голову сломал!
     
  4. segalp

    segalp Создатель

    Регистр.:
    11 май 2013
    Сообщения:
    11
    Симпатии:
    4
    Тут все просто, нужно разделить строку на 2 части - до слова "код" и после. Для этого можно использовать различные функции, например LEFT - возвращает подстроку до определенного символа и SUBSTRING который так-же возвращает подстроку.
    Запрос писался без проверки, должно быть что-то вроде такого: (рекомендую потренироваться на копии таблицы)
    UPDATE `oc_product_description` SET `description` = CONCAT(LEFT(`description`, LOCATE('код', `description`)), `id`, SUBSTRING(`description` FROM (LOCATE('код', `description`) + 3)));
    В конце +3 - длина слова "код", чтобы отсечь его
     
  5. DarkDalamar

    DarkDalamar Создатель

    Регистр.:
    24 июл 2013
    Сообщения:
    22
    Симпатии:
    1
    Ох, столько времени прошло, я уже забыл, что про это спрашивал.
    Вообщем с горем пополам я написал вот такой код, все работает, но мне кажется, что я его слишком усложнил, посмотрите покритикуйте, что и где можно сократить.

    Код:
    <?php
    $host='localhost';
    $database='***';
    $user='***';
    $pswd='***';
    
    $dbh = mysql_connect($host, $user, $pswd) or die("Не могу соединиться с MySQL.");
    mysql_select_db($database) or die("Не могу подключиться к базе.");
    
    mysql_query("UPDATE `oc_product_description` SET `description` = REPLACE(`description`, '----------------------------------------------------------------------', '')");
    mysql_query("UPDATE `oc_product_description` SET `description` = REPLACE(`description`, '-->', '')");
    mysql_query("UPDATE `oc_product_description` SET `description` = REPLACE(`description`, '<br>', '')");
    mysql_query("UPDATE `oc_product_description` SET `description` = REPLACE(`description`, '<p></p>', '')");
    
    mysql_query("UPDATE `oc_product_description` SET `description` = REPLACE(`description`, '<p><p>', '<p class=\"infocell_right\"><i>')");
    mysql_query("UPDATE `oc_product_description` SET `description` = REPLACE(`description`, '</p></p>', '</!></!>')");
    mysql_query("UPDATE `oc_product_description` SET `description` = REPLACE(`description`, '<p>', '<div class=\"infostring\"><p class=\"infocell_left\"><b>')");
    mysql_query("UPDATE `oc_product_description` SET `description` = REPLACE(`description`, '</p>', ':</b></p class=\"infocell_close\">')");
    mysql_query("UPDATE `oc_product_description` SET `description` = REPLACE(`description`, '</!></!>', '</i></p class=\"infocell_close\"></div>')");
    
    $a = mysql_query("SELECT COUNT(1) FROM `oc_product_description`");
    $b = mysql_fetch_array( $a );
    echo "Количество товаров с описаниями в базе - ".$b[0]."<br><br>";
    
    $a = 0;
    while($a < $b[0])
    {
    $query = "SELECT `description`,`product_id` FROM `oc_product_description` LIMIT ".$a.",1";
    $res = mysql_query($query);
    $row = mysql_fetch_array($res);
    $string = $row['description'];
    $id = $row['product_id'];
    $query = "SELECT `sku` FROM `oc_product` WHERE `product_id` = '".$id."'";
    $res = mysql_query($query);
    $row = mysql_fetch_array($res);
    $sku = $row['sku'];
    
    $a++;
    echo "<b>Товар ".$a."</b>, Product_ID ".$id.", SKU ".$sku.": <br>";
    //------------------------------ Поиск "Код" и замена на "Артикул" ------------------------------
    $find = "Код";
    $pos = strpos($string, $find);
    if ($pos) {
            echo "   Вхождение <b>".$find."</b> найдено и заменено на Артикул.<br>";
            $substring = substr($string, 0, $pos);
            $result_string = $substring."Артикул:";
           
            $substring = substr($string, $pos + 4);
            $find = "<i>";
            $pos = strpos($substring, $find);
            $result_string = $result_string.substr($substring, 0, $pos + 3).$sku;
           
            $find = "</i>";
            $pos = strpos($substring, $find);
            $result_string = $result_string.substr($substring, $pos);
            $string = $result_string;
    } else    {
             echo "   Вхождение <b>".$find."</b> не найдено, товар был обработан ранее.<br>";
    }
    $query = "UPDATE `oc_product_description` SET `description` = '".$string."' WHERE `product_id` = '".$id."'";
    mysql_query($query);
    }
    ?>
     
  6. segalp

    segalp Создатель

    Регистр.:
    11 май 2013
    Сообщения:
    11
    Симпатии:
    4
    Хм, ну да, наворотили конечно черезчур.
    Часть до цикла я проигнорирую.
    Во первых замените ваш цикл на такой:
    $query = "SELECT `description`,`product_id` FROM `oc_product_description`";
    $res = mysql_query($query);
    while($row = mysql_fetch_array($res)) {

    }
    Данный шаг позволит уменьшить количество обращений к базе до 1го вместо 1001 (если в базе 1000 товаров).
    Во вторых для формирования новой строки у вас используется php - это не плохо, не хорошо, но я обычно стараюсь не использовать его в тех случаях, где без него можно обойтись.
    В MySQL достаточно строковых функций чтобы решить практически любую задачу, в большинстве случает в лоб. Изредка приходится импровизировать.

    В качестве лирического отступления предлагаю представить что происходит в вашем коде:
    Есть 2 завода в Москве и Владивостоке.
    В Москве изготавливаете кирпичи во Владивостоке они окрашиваются.
    Пришел клиент попросил 1000 кирпичей, с крестиком на одной стороне.
    Во налаженной схеме вы решили изготовить кирпичи в Москве.
    Потом почему-то начали возить по одному во Владивосток для нанесения крестика.
    Хотя можно во первых отвезти все одной партией.
    А во вторых потратить 10 минут на обучение нанесения крестика в Москве.
     
  7. kazam224

    kazam224 Писатель

    Заблокирован
    Регистр.:
    27 дек 2014
    Сообщения:
    1
    Симпатии:
    0
  8. greatbart

    greatbart Постоялец

    Регистр.:
    25 авг 2007
    Сообщения:
    59
    Симпатии:
    12
    В запросе после WHERE все столбцы должны иметь индексы
     
  9. Zmeyonish

    Zmeyonish Создатель

    Регистр.:
    29 мар 2013
    Сообщения:
    10
    Симпатии:
    0
    даже в исходном коде LIKE должен работать быстро (при наличии индекса по полю mail) поскольку нет шаблонов '%'
     
  10. BaBL

    BaBL Постоялец

    Регистр.:
    13 ноя 2012
    Сообщения:
    144
    Симпатии:
    87
    А это что за золотое правило такое? Если вы им пользуетесь - ждите эпик фэйлов в своей работе.

    Количество индексов должно быть достаточным и что не маловажным - правильным (что касается типа индекса и состава полей), иначе в некоторых случаях такие "индексы на все что после where" вам могут дать такую регрессию, что мало не покажется.