За последние 24 часа нас посетили 20477 программистов и 1126 роботов. Сейчас ищут 714 программистов ...

Оптимизация SQL запроса и PHP кода

Тема в разделе "Прочие вопросы по PHP", создана пользователем vayas, 19 фев 2013.

  1. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    Всех приветствую.
    Вообщем есть раздел "МОИ ОБЪЯВЛЕНИЯ" в нем содержаться более 200 объявлений у каждого юзера.
    Так же есть постраничная навигация, на каждую страницу выходит десять объявлений. (Это предысловие)
    Всё работает следующим образом:
    Выполняется запрос который достает объявления, сам по себе он достаточно ресурсоёмкий, т.к. имеется несколько джоинов.
    Дальше мы получаем массив и в цикле выводятся эти объявления (запрос нам возвращает объявления недвижимости)
    И когда я получаю одно объявление, оно кладется в первую ячейку <TD>
    А вторая ячейка <TD> формирует график (статистика просмотров)
    График формируется следующим образом
    Кода происходит одна итерация в ней мы получим ID объявления соответсвенно я снова обращаюсь к базе, и в условии пишу
    WHERE obj = :eek:bj т.е. ID этого объявления и запрос на каждой итерации нам возвращает три строки, из них я собираю данные для того что бы их спарсить в графике.
    И потом следующая итерация и т.д.
     
  2. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    11.068
    Симпатии:
    1.231
    Адрес:
    там-сям
  3. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    А как быть с актуализацией данных?

    Добавлено спустя 1 минуту 2 секунды:
    Или переобнавлять кеш при каждом изменении?
     
  4. igordata

    igordata Суперстар
    Команда форума Модератор

    С нами с:
    18 мар 2010
    Сообщения:
    32.410
    Симпатии:
    1.768
    дублировать инфу избегая джоинов таким образом. показывай запросы и структуру. так ничего путного не насоветуем.
     
  5. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    Ок вот первый запрос, он нам возвращает список, из объявлений
    Код (Text):
    1.  
    2. SELECT realty.id, realty.city, realty.address, realty.type_house, realty.square, img.img_source, (offer.name) type_offer
    3.                 FROM of_obj_relaty realty
    4.                 LEFT JOIN obj_img img ON realty.id = img.id_obj  
    5.                 LEFT JOIN type_offer offer ON realty.type_offer = offer.id  
    6.                 LEFT JOIN type_realty tRealty ON realty.type_realty = tRealty.id
    7.                 LEFT JOIN type_material material ON realty.type_material = material.id  
    8.                 WHERE realty.user_id= :id GROUP BY realty.id ORDER BY realty.date_create_change DESC
    После того как у нас получился список из десяти объявлений
    Мы достаем их циклом
    Код (Text):
    1.  
    2.  while ($row = $query->fetch())
    3. {
    4.  //// здесь ячейка с самим объявлением
    5. /// здесь метод который формирует нам статистику
    6. $rows = $objStatistic->SelectDefaultStatistic($_SESSION['user_data']['id'], $row['id']);
    7. }
    Ну и сам метод возвращает строку с помощью которой формируется график на googleAPI
    Код (Text):
    1.  
    2.  public function SelectDefaultStatistic($id, $objId)
    3.  {  
    4.         $query = $this->core->dbh->prepare("SELECT * FROM obj_statistic WHERE user_id = ? AND obj_id = ? ORDER BY date DESC LIMIT 3");
    5.         $query->execute(array($id, $objId));        
    6.         while($res = $query->fetch())
    7.         {            
    8.             $rows .=  "['" . date('Y.m.d', $res['date']) . "'," . $res['metrosphera_views']. "," . $res['metrosphera_show']. "," . $res['rkk_show']. "," . $res['rkk_view']. "],";
    9.         }
    10.         return $rows;        
    11. }
    SelectDefaultStatistic() т.е. метод будет вызываться при каждой итерации...
    Ну и получается вместо одного запроса, десять.
    LIMIT 3 кстати нужен для того что бы на графике отображать количество дней
    Ещё попросили что бы можно было самому задать количество дней, т.е. присоединить джоином таблицу obj_statistic из метода SelectDefaultStatistic() не получиться. Если бы день брался один тогда проще конечно. А так проблемка.
     
  6. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    11.068
    Симпатии:
    1.231
    Адрес:
    там-сям
    Насчет актуализации данных - тебе виднее. Если они в пределах одной пользовательской сессии не меняются, нафига лишние запросы делать?


    Не знаю твоих данных, в SelectDefaultStatistic в $rows реально получается несколько строк или там всегда одна?
    Ну допустим может быть сколько угодно строк:

    Способ №1: Если чисто по учебнику, то напрашивается prepare вынести из функции и внешнего цикла. По идее достаточно один раз подготовить, а потом биндить параметры и выполнять. Но реально это ничего не даст :)

    Способ №2: могу предположить, что user_id у тебя в цикле не меняется. Значит ты можешь работать с таким условием:
    ... WHERE user_id=? and obj_id IN($objs) // за один запрос получить всё что надо.
    список айдишников, к сожалению, в плейсхолдер не засунуть. надо самому формировать строку как implode(',', $array), можно не экранировать, т.к. твои данные не от пользователя, а из другого запроса и, подозреваю, могут быть только целыми.
     
  7. igordata

    igordata Суперстар
    Команда форума Модератор

    С нами с:
    18 мар 2010
    Сообщения:
    32.410
    Симпатии:
    1.768
    Каким нахрен циклом? о_О

    Добавлено спустя 25 секунд:
    что такое объявления и при чем тут статистика?
     
  8. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    Вообще-то меняться они могут.
    На счет вот этого не знаю правильно ли я тебя понял, но у меня такой запрос только один, так что смысл выносить этот prepare я не вижу.
    Но он и так получается один, правда на каждую итерацию
    Могу скинуть логи пароль что бы меня немного поняли увидив что там происходит, раздел статистика

    Добавлено спустя 4 минуты 25 секунд:
    WHILE
    У меня на каждую новую итерацию генериться график(статистика), данные для графика(статистика) приходят из базы
     
  9. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    Фигня у меня какая-то написана, немного переписал топик.
     
  10. igordata

    igordata Суперстар
    Команда форума Модератор

    С нами с:
    18 мар 2010
    Сообщения:
    32.410
    Симпатии:
    1.768
    я не понял а статистика каким боком к этому всему делу?
     
  11. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    Статистика - кол-во просмотров объявлений с определенного сайта недвижимости Ну просто сколько просмотрели определенное объявление, например за какую-то дату
     
  12. igordata

    igordata Суперстар
    Команда форума Модератор

    С нами с:
    18 мар 2010
    Сообщения:
    32.410
    Симпатии:
    1.768
    ну вот допустим у нас есть таблица:
    №объявления, №сайта, просмотры

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

    или объявления - их вообще из одной/двух таблиц выбираешь. там где нагрузка? =)

    вот это вот
    GROUP BY realty.id
    оно зачем? ты можешь обойтись без этого?
     
  13. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    Объявления тянуться из одной таблицы.
    Нет без GROUP BY не могу
    Ну как где цикл, перебираем первый массив раз цикл, и в массиве с каждой новой итерацией ещё цикл, это считай одна проблема, плюс есть проблема это десять лишних обращений к базе, вместо например одного.
    Джоины нужны что бы сформировать объявление, потому что в таблице с обявлениями, храняться к примеру: не "рубрика - новостройка", а id рубрики. Поэтому джоины.

    Добавлено спустя 7 минут 58 секунд:
    в таблице с объявлениями ещё цепляется при помощи джоина таблица с изображениями, поэтому без GROUP BY никак, иначе объявления дублируются
     
  14. igordata

    igordata Суперстар
    Команда форума Модератор

    С нами с:
    18 мар 2010
    Сообщения:
    32.410
    Симпатии:
    1.768
    погоди, погоди. давай разберёмся =)
    давай тогда разбивать на два запроса. зачем этот групбай тут? смысла в нём ноль. Делай выборку, обрабатывай, выбирай id картинок, потом картинки выбирай одним запросом через WHERE `id` IN (...).
     
  15. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    Именно в этом списке объявлений, который мы получаем на выходе, мне нужна всего одна любая картинка соответствующая данному объявлению. Что то на счет выборки я не понял как это...
    Код (Text):
    1.  
    2. Вот к примеру у меня запрос
    3. SELECT * FROM advert LIMIT 10
    4. Вы предлааете два запроса.
    5. Я вижу только один способ,когда мы получим массив из строк в цикле.
    6. При каждой итерации доставая нужный ID выполнять новый запрос.
     
  16. igordata

    igordata Суперстар
    Команда форума Модератор

    С нами с:
    18 мар 2010
    Сообщения:
    32.410
    Симпатии:
    1.768
    ну если ты из запроса картинки исключишь, тебе не надо будет делать групбай?
     
  17. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    Нет, тогда не нужно будет
     
  18. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    11.068
    Симпатии:
    1.231
    Адрес:
    там-сям
    Не обижайся, но у меня такое ощущение от всех твоих ответов в теме -- фигня и непонимание.

    Твой "один запрос" делается в функции, которая вызывается в цикле. Ты не видишь в этом проблемы? Очень жаль.
    Объясняю: это главное правило оптимизации по скорости -- разгружай циклы. Даже сами SQL-запросы можно оптимизировать в этом ключе, добивайся чтобы на каждую добытую строку (там тоже цикл) сервер делал как можно меньше вычислений.
    А если удается вообще отказаться от цикла или заменить цикл PHP на один вызов встроенной функции, это дает прирост скорости в разы!
     
  19. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    Я не обижаюсь.
    Если бы я знал как этого добиться я бы не пришел сюда за ответами, я не знаю поэтому и спрашиваю
    Но как сделать лучше, того что у меня есть сейчас...
    То что я написал где то пятым сообщением сверху
    Я думаю основная проблема это ДЕСЯТЬ ЛИШНИХ ОБРАЩЕНИЙ К БАЗЕ вместо одного, и мне бы хотелось в первую очередь, исправить именно это.
     
  20. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    11.068
    Симпатии:
    1.231
    Адрес:
    там-сям
    Я ведь именно про это тебе и пишу )))
     
  21. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    Код (Text):
    1.  
    2. <?php          
    3.             $sql = "SELECT realty.id, realty.city, realty.address, realty.type_house, realty.square, img.img_source, (offer.name) type_offer
    4.                 FROM of_obj_relaty realty
    5.                 LEFT JOIN obj_img img ON realty.id = img.id_obj  
    6.                 LEFT JOIN main_type_offer offer ON realty.type_offer = offer.id  
    7.                 WHERE realty.user_id='" . $_SESSION['user_data']['id'] . "'
    8.                 GROUP BY realty.id ORDER BY realty.date_create_change DESC";
    9.             include $_SERVER["DOCUMENT_ROOT"] . '/classes/pagination.class.php';
    10.             $pager = new Pagination($sql, 5, 3, null);
    11.             $query = $pager->paginate();
    12.             if($query == false) {
    13.               echo "Записей не найденно";
    14.             }
    15.             else
    16.             {
    17.             ?>
    18.             <table class='obj'>
    19.                 <?php
    20.                 $i = 0;
    21.                 while ($row = $query->fetch()) {                    
    22.                     ?>
    23.                     <tr>                      
    24.                         <td style="width: 250px;">
    25.                             <div><b><?= $row['type_offer']; ?> 1-комн квартира по адресу ул. <?= $row['address']; ?></b><br />
    26.                                 Этаж 12/12 Площадь общ/жил/кух (<?= $row['square']; ?>)  Материал: монолитно каркасный
    27.                                 <?= $row['type_relaty']; ?> 1-комн квартира  Тип комнат: <?= $row['type_house']; ?></div>
    28.                         </td>                        
    29.                         <td width="200px;">                                        
    30.                             <?php $rows = $objStatistic->SelectDefaultStatistic($_SESSION['user_data']['id'], $row['id']); ?>
    31.                             <script type="text/javascript">
    32.                                 google.load("visualization", "1", {packages:["corechart"]});
    33.                                 google.setOnLoadCallback(drawVisualization);
    34.                                 function drawVisualization() {          
    35.                                     var json = [['Имя', 'Метросфера показы', 'Метросфера просмотры', 'РКК просмотры', 'РКК показы'],
    36.                                     <?php echo ($rows == '') ? "['0',0,0,0,0]," : $rows; ?>]
    37.                                                     var data = google.visualization.arrayToDataTable(json);
    38.                                                     var ac = new google.visualization.ComboChart(document.getElementById('visualization-<?= $i ?>'));
    39.                                                     ac.draw(data, {
    40.                                                         width: 170,
    41.                                                         height: 55
    42.                                                     });
    43.                                                 }
    44.                             </script>
    45.                             <div id="visualization-<?= $i ?>" ></div>
    46.                         </td>
    47.                     </tr>
    48.                     <?php
    49.                     $i++;
    50.                 }
    Надеюсь кто нибудь подскажет как можно переписать этот блок кода
    Тут в принципе все то, что описано было выше.
    Как видите кроме выполнения повторного запроса тут ещё каждый раз генериться гугловский график.
    В доках пока решения не нашел.
    Может кто то работал с подобным?
     
  22. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    11.068
    Симпатии:
    1.231
    Адрес:
    там-сям
    Ответ: находишь цикл и смотришь нельзя ли взять кусок кода и вынести его ЗА рамки цикла. Твой код я повторять не буду, чтобы не ленился.
    Объясняю в общем виде: допустим есть такой говнокод
    Код (PHP):
    1. $result = mysql_query("SELECT * FROM table1 WHERE user_id={$user_id}");
    2. while ($row1=mysql_fetch_assoc($result)) {
    3.   $result2 = mysql_query("SELECT * FROM table2 WHERE post_id={$row1['post_id']}");
    4.   while ($row2=mysql_fetch_assoc($result2)) {
    5.     echo $row1['post_id']. $row2['name'];
    6.   }
    7. }
    Если первый запрос вернет нам 10 строк, то всего будет вызвано 11 запросов. Понятно?

    можно безболезненно заменить вот на такое
    Код (PHP):
    1. $result = mysql_query("SELECT * FROM table1 WHERE user_id={$user_id}");
    2. $id_array = array();
    3. while ($row1=mysql_fetch_assoc($result)) {
    4.   $id_array[] = $row1['post_id'];
    5. }
    6. $ids = implode(',', $id_array);
    7. $result2 = mysql_query("SELECT * FROM table2 WHERE post_id IN({$ids})");
    8. while ($row2=mysql_fetch_assoc($result2)) {
    9.     echo $row2['post_id']. $row2['name'];
    10.   }
    11. }
    Будет 2 запроса в любом случае. Понятно?
     
  23. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    :) Это понятно, но как сделать что бы у каждого ID было по три объявления
    Код (Text):
    1.  
    2. SELECT * FROM obj_statistic WHERE obj_id = ? DESC LIMIT 3
    3.  
    4. вот при таком запросе ведь это невозможно
    5. SELECT * FROM table2 WHERE post_id IN({$ids})
     
  24. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    11.068
    Симпатии:
    1.231
    Адрес:
    там-сям
    никогда не говори "невозможно"! это выходит за рамки темы, но есть решения, зависят от диалекта SQL и вашей структуры таблиц
    https://www.google.ru/search?q=запрос+sql+первые+три+записи+для+каждого+id
     
  25. vayas

    vayas Активный пользователь

    С нами с:
    13 дек 2012
    Сообщения:
    167
    Симпатии:
    9
    Адрес:
    Пермь
    Ты прав скорее я не знаю, чем это не возможно.
    У меня MYSQL.
    Таблица со статистикой вот в принципе других нет ну таблица с юзерами там ничего такого особенного, таблица с объявлениями в принципе то же обычная, вот в статистике есть id объявления и idUser остальное это дата и число просмотров
    [​IMG]

    Посмотреть как выглядет готовое решение примерно можно здесь
    http://cabinet.metrosphera.ru/profile/statistic
    логин 1824235883
    пароль fkc3n8m45xp
    Раздел статистика, их там всего пять(разделов), думаю не запутаетесь
    На счет гет запроса не совсем понял