В очередной раз пытаюсь оптимизировать запрос, наткнулся на странную вещь. Немного поэкспериментировав, получил такой запрос:

SELECT
     owner_id
FROM
     (SELECT
           owner_id
     FROM
           audio
     LIMIT
           1000000)a
LIMIT
     10

В профайлинге:

Starting    41 µs
Checking Permissions    5 µs
Opening Tables    13 µs
System Lock    71 µs
Optimizing    6 µs
Statistics    9 µs
Preparing    6 µs
Executing    2 µs
Sending Data    302.5 ms (99,8%)
Init    14 µs
Optimizing    3 µs
Statistics    14 µs
Preparing    5 µs
Executing    10 µs
Executing    13 µs
End    13 µs
Query End    2 µs
Closing Tables    1 µs
Removing Tmp Table    657 µs
Closing Tables    13 µs
Freeing Items    74 µs
Logging Slow Query    2 µs
Cleaning Up    4 µs

И если увеличивать внутренний лимит до 10+млн, фаза Sending data растягивается на 5 минут. О какой передаче данных идёт речь? Если выбирать из таблицы напрямую, то фаза эта будет занимать считанные доли секунд.

Ап

получаешь через fetchall?

вообще уже давно надо было понять, что внутренний селект у тебя тебе нафиг не нужен

тут вот пишут, что сендинг дата - это время от начала выполнения до передачи последней строки, т.е. не только передача, но и выполнение щапроса

запроса

(2) Что это?
(3) Запрос тестовый - разбирался откуда берётся Sending data.  В последнем предложении указал, что делал и без внутреннего селекта (т.е. это очевидно, что можно и без него)

сделай EXPLAIN твойзапрос

(4) Есть же Executing..
Да и без внутреннего запроса Sending data вообще исчезает (сейчас проверил)

(7) Вот..
https://dl.dropbox.com/u/43161184/Screenshots/Capture4.PNG

А вот для прямого запроса (запрос есть в скрине):
https://dl.dropbox.com/u/43161184/Screenshots/Capture5.PNG

вот, например:

EXPLAIN SELECT score, distance, date, nickname
FROM (
SELECT score, distance, date, nickname
FROM jsgleest_results
ORDER BY id DESC
LIMIT 1000
) AS innerSelect
ORDER BY score DESC
LIMIT 10

1    PRIMARY    <derived2>    ALL    NULL    NULL    NULL    NULL    1000    Using filesort
2    DERIVED    jsgleest_results    ALL    NULL    NULL    NULL    NULL    13283    Using filesort

против

EXPLAIN SELECT score, distance, date, nickname
FROM (
SELECT score, distance, date, nickname
FROM jsgleest_results
WHERE id > (
SELECT Count( * ) -1000
FROM jsgleest_results )
) AS innerSelect
ORDER BY score DESC
LIMIT 10

1    PRIMARY    <derived2>    ALL    NULL    NULL    NULL    NULL    1016    Using filesort
2    DERIVED    jsgleest_results    ALL    PRIMARY    NULL    NULL    NULL    13283    Using where
3    SUBQUERY    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away


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

(9) давай картинки целиком, а то вообще непонятно.

(12) Это експлейн для запроса из (0). Больше в таблице ничего нет

(13) ну так он у тебя 16 миллионов строк колбасит

просто запрос из (0) лишен смысла - он у тебя равнозначен
SELECT
           owner_id
     FROM
           audio
     LIMIT
           10

только без индекса

Так вопрос про Sending data, а не про запрос. Что он куда отправляет?

(16) я ж говорю - пока запрос выполняется - сендинг дата считается

а повторы есть?

(17) Выполнение запроса - это Executing (сендинг дата нет в прямом запросе)
(18) Нет, все разные (строки разные, но owner_id есть одинаковые. Но суть опять же в сендинг дата)

(19).1 еще раз. в твоем случае - это чтение 16 мегастрок для последующего запроса. Так понятнее?

мехматовцев позорят. да исчо МГУ! грусть

(20) Если это чтение, то понятно) Спасибо
(21) Ахаха
Что грусть то (кроме твоей логики(? Как вообще МГУ и мускуль связаны?

(22) да у тебя все ветки одинаковые, и при этом одинаково странные с одинаково малосмысленными запросами

(23) Ветки одинаковые только из-за наличия скуля?
Везде разные вопросы.
А запрос я однажды выкладывал полный - помощи ноль было - поэтому приходится всё по частям разбирать

(24) нет, ветки одинаковые из-за того, что у тебя все время запрос селект фром (селект лимит 100500) лимит 1.

(25) Бред. Тогда мне это нужно было, чтобы всю таблицу не тянуть и не ждать. Сейчас я разбирался почему внутренний селлект даёт сендинг дата

(26) ты так ни разу и не сформулировал целиком, что ты хочешь получить, сразу лепишь селекты...

(27) Формулировал. Даже в первом посте выкладывал описание:
SQL. Оптимизировать ещё один запрос

(28) ты хоть раз объяснил, зачем тебе лимит во внутреннем запросе? зачем там вообще внутренний запрос?

Только там в (3) правильный запрос.
Без внутреннего селекта у меня это дело во временную таблицу не помещается, и мускуль это всё на диск кидает. А ждать концы такого запроса не имеет смысла.

В той же теме и писал зачем мне внутренний запрос

(31) вот это? SELECT
   A,
   COUNT(*) AS Cnt
FROM
   (SELECT
       A
   FROM
       tbl
   WHERE
       owner_id IN (SELECT DISTINCT owner_id FROM tbl WHERE A=:A)
   ) a  
GROUP BY
   A
ORDER BY
   Cnt DESC
LIMIT 10

(32) Да, это самый главный запрос.
До чего я дошёл - прикрутить хеш-множество в ущерб точности и скорости добавления/удаления. Но пока не сдаюсь)

а почему не так?

SELECT
  A,
  COUNT(*) AS Cnt
FROM
  tbl
WHERE
  owner_id IN (SELECT DISTINCT owner_id FROM tbl WHERE A=:A)
GROUP BY
  A
ORDER BY
  Cnt DESC
LIMIT 10

Внутренний запрос нужен был для LIMIT. В (32) его забыл. А LIMIT нужен потому, что запрос (34) выполняется больше 10 минут

(35) а EXPLAIN что выводит?

(36) А что он может нового вывести? Таблица на 16 млн строк. Ясен пень, что в оперативки не поместится для группировке, приходится на диск депить. Отсюда и время

для группировки*

лепить*

(37) 16мегастрок после фильтрации по овнерид?

(40) Ошибся. Меньше конечно. Но в оперативку уже не влезает

(41) прям не влезает? а какой тип у A?

если у обоих int(11) то это примерно 8 байт на строку

(42) Да.. Наврал получается.
https://dl.dropbox.com/u/43161184/Screenshots/Capture6.PNG
https://dl.dropbox.com/u/43161184/Screenshots/Capture7.PNG

(44) это от (34)?

т.е. победа?

еще от derived избавится, заменив все на джоин можно попробовать

(45) Да, но только через джоин.
(46) Не сказал бы, что 20 секунд это победа - дальше база расти будет

так тип-то какой?

и текст запроса

я вот не пойму, ты стесняешься, или думаешь, что твой запрос без данных кому-то нужен?

(49) Int
SELECT
   a.artist_id,
   a.title_id,
   count(*) count
FROM
   audio a
JOIN
   (SELECT DISTINCT
       owner_id
   FROM
       audio
   ".$whereClause.") owners
   ON owners.owner_id = a.owner_id
GROUP BY
   a.artist_id,
   a.title_id
LIMIT 100;
b
Where artist_id = 234

+order by count

SELECT
  a.artist_id,
  a.title_id,
  count(*) count
FROM
  audio a
INNER JOIN audio b owners
  ON owners.owner_id = a.owner_id
   AND owners.artist_id = 234
GROUP BY
  a.artist_id,
  a.title_id

INNER JOIN audio owners

кстати, так хорошо все было через PDO, а тут вдруг рваный запрос...

(54) Джоин с той же таблицей? Эквиваленто условию WHERE a.artist_id = 234

Запрос выдаёт не то

(58) ты проверил?

(59) Да
(26) В смысле "рваный запрос"? Я на yii пишу. Там удобно выполнять его

(59) Извиняюсь. Забыл id на более популярный сменить.
Про (57) тоже был не прав)

https://dl.dropbox.com/u/43161184/Screenshots/Capture8.PNG
https://dl.dropbox.com/u/43161184/Screenshots/Capture9.PNG

(60) рваный в том смысле, что клеится из кусочков, вместо использования PDO:PreparedStatement как в SQL. Оптимизировать ещё один запрос в (0) сообщении
Это заставляет много думать про фильтрацию входных данных, т.е. тех кусочков, из которых сам запрос клеится.

(61) ну так что, получше стало?

а сколько всего колонок в таблице?

попробуй поменяй count(*) на sum(1)

(63) В yii тоже можно собирать запрос. Но т.к. у меня запросы примитивные, я даже не стал вдаваться в подробности.
Лучше особо не стало, разве только explain получше стал. Да и запрос) Возьму себе) Спасибо)
(64) Колонок 6, все инты

(65) Те же 90 секунд..
Это он копируует во временную таблицу для группировки?

И всё-таки (54) оказался косячным. Там не учитывается условие DISTINCT owners_id, и строки задваиваются

интересно, что кажут

SHOW COLUMNS FROM audio

и

SHOW INDEX FROM audio

ишшо надо статистику пару раз обновить - analyze table

Field        Type    Null    Key    Default    Extra
id        int(11)    NO    PRI    NULL    auto_increment
artist_id    int(11)    NO    MUL    NULL    
title_id    int(11)    NO    MUL    NULL    
aid        int(11)    NO        NULL    
owner_id    int(11)    NO    MUL    NULL    
duration    int(11)    NO        NULL    



Table    Non_uni    Key_name    Seq_in_index    Column_name    Collation    Cardinality    Sub_part    Packed    Null    Index_type    Comment    Index_comment
audio    0    PRIMARY        1        id        A        15952265    NULL        NULL        BTREE        
audio    1    art_tit_index    1        artist_id    A        1227097    NULL    NULL        BTREE        
audio    1    art_tit_index    2        title_id    A        3988066    NULL    NULL        BTREE        
audio    1    title_id    1        title_id    A        2658710    NULL    NULL        BTREE        
audio    1    owner_id    1        owner_id    A        60886    NULL    NULL        BTREE

Field    Type    Null    Key    Default    Extra
id    int(11)    NO    PRI    NULL    auto_increment
artist_id    int(11)    NO    MUL    NULL    
title_id    int(11)    NO    MUL    NULL    
aid    int(11)    NO        NULL    
owner_id    int(11)    NO    MUL    NULL    
duration    int(11)    NO        NULL    

ANALYZE TABLE audio = Table is already up to date

Спасибо за участие!) Пока решил резать число владельцев - получается вполне сносно. Дальше буду ещё думать)

сделай 1 индекс на 2 поля: ownerId и artistId

Попробовал. Время не изменилось

А по artistId отдельно?