Оптимизация "LIMIT $start, $end" [SQLite]

Тема в разделе "Базы данных", создана пользователем alexz15, 27 мар 2015.

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

    alexz15

    Регистр.:
    3 окт 2008
    Сообщения:
    394
    Симпатии:
    190
    • Имеется sqlite база, например, 500к записей:
      Код:
      CREATE TABLE pages(
          id INTEGER PRIMARY KEY,
          content TEXT,
          category TEXT,
          true INTEGER
      );
      CREATE INDEX IF NOT EXISTS true_idx ON pages (true);
      CREATE INDEX IF NOT EXISTS category_idx ON pages (category);
      Выборка происходила так:
      Код:
      SELECT * FROM pages WHERE category='cat' AND true='1' ORDER BY id DESC LIMIT $start, $end;
      Как можно оптимизировать данный запрос?

      Есть такой вариант:
      Код:
      SELECT * FROM pages WHERE category='cat' AND true='1' AND id>$start ORDER BY id DESC LIMIT $end; 
      но если запись будет удалена, то LIMIT может не вывести нужно кол-во $end.

      Можно ли использовать такой запрос:
      Код:
      SELECT * FROM pages WHERE category='cat' AND true='1' AND id BETWEEN $start AND $end ORDER BY id DESC
      ?
    • И второй вопрос.
      Правильно ли я понял, что этот запрос оптимизирован:
      Код:
      SELECT * FROM pages WHERE category='cat' AND true='1' AND id IN (1,5,8,123,5634...) ORDER BY RANDOM()
      по сравнению с этим:
      Код:
      SELECT * FROM pages WHERE category='cat' AND true='1' ORDER BY RANDOM() LIMIT $end
      ?
     
    Последнее редактирование: 28 мар 2015
  2. latteo

    latteo Эффективное использование PHP, MySQL

    Moderator
    Регистр.:
    28 фев 2008
    Сообщения:
    1.452
    Симпатии:
    1.248
    1) LIMIT отрабатывает на той выборке, которая была сделана после всех условий, таким переносом кода AND id>$start вы вероятнее всего нарушите логику скрипта и на выходе получите совсем не те данные...
    2) считается, что ORDER BY RANDOM() долгая операция и лучше всего её избегать, а она у вас в обоих вариантах...
     
    alexz15 нравится это.
  3. alexz15

    alexz15

    Регистр.:
    3 окт 2008
    Сообщения:
    394
    Симпатии:
    190
    По первому вопросу я пока остановился на этом:
    Код:
    SELECT * FROM pages WHERE category='cat' AND true='1' AND id BETWEEN $start AND $end ORDER BY id DESC
    По второму - в первом примере ORDER BY RANDOM() выполняется, по идее, после выборки конкретных id, и именно в выбранных id..? Когда во втором примере ORDER BY RANDOM() выполняется для всех записей. Или я не прав?
     
  4. dima_8_16_80

    dima_8_16_80 Постоялец

    Регистр.:
    1 фев 2012
    Сообщения:
    108
    Симпатии:
    25
    Куда-то не туда понесло тебя
    Для BETWEEN $start AND $end принципиально, чтобы $start был меньше $end.
    А исходя из первичной потребности, "LIMIT $start, $end" = "нужно взять $end элементов, начиная с $start".
    И если будет "взять 50 элементов, начиная с 250", то LIMIT $start, $end (LIMIT 250, 50) отработает нормально, а BETWEEN $start AND $end (BETWEEN 250 AND 50) нет.
    На самом деле, BETWEEN не быстрее LIMIT, им не оптимизируешь.
    И тем более "но если запись будет удалена" BETWEEN выведет то же самое.
     
    alexz15 нравится это.
  5. alexz15

    alexz15

    Регистр.:
    3 окт 2008
    Сообщения:
    394
    Симпатии:
    190
    Да, действительно не туда понесло)

    По первому вопросу я сделал как здесь http://habrahabr.ru/post/217521/

    А вот по второму пока не ясно...
    Код:
    в первом примере ORDER BY RANDOM() выполняется, по идее, после выборки конкретных id, и именно в выбранных id..? Когда во втором примере ORDER BY RANDOM() выполняется для всех записей. Или я не прав?