Преобразование запроса UNION в MySQL

Какие у вас индексы на этой таблице? Этот индекс:

СОЗДАТЬ ИНДЕКС fs_search_idx ON fs_walk_scan (file_path, file_size desc)

значительно ускорит этот запрос ... если у вас его еще нет.

Обновлять:

Вы сказали, что уже есть индексы для file_path и file_size ... это отдельные индексы? Или существует единый индекс, в котором оба столбца индексируются вместе? Для этого запроса разница будет огромной. Даже с 22 подзапросами, если они правильно проиндексированы, это должно работать быстро.

Как насчет чего-то вроде этого (не тестировал, но похоже):

select * from fs_walk_scan where file_path like '\\server' and file_path like 'root$\%' order by file_size desc 

Таким образом, вы выполняете пару сравнений в отдельном поле, которое в целом будет соответствовать тому, что вы описали. Возможно, можно будет использовать и регулярное выражение, но я этого не делал.

Вы можете использовать регулярное выражение:

select * from fs_walk_scan
  where file_path regexp '^\\server(1\[ghi]|2\[fg]|3\h|[45]\i)root$\'

В противном случае, если вы можете изменить структуру своей таблицы, добавьте два столбца для хранения имени сервера и базового пути (и их индексации), чтобы вы могли создать более простой запрос:

select * from fs_walk_scan
  where server = 'server1' and base_path in ('groot$', 'hroot$', 'iroot$')
     or server = 'server2' and base_path in ('froot$', 'groot$')

Вы можете либо настроить триггер для инициализации полей при вставке записи, либо после этого выполнить массовое обновление, чтобы заполнить два дополнительных столбца.

Вы можете сделать что-то вроде этого ... при условии, что в fs_list есть список ваших поисковых запросов типа "LIKE":

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`proc_fs_search` $$
CREATE PROCEDURE `test`.`proc_fs_search` ()
BEGIN

DECLARE cur_path VARCHAR(255);
DECLARE done INT DEFAULT 0;


DECLARE list_cursor CURSOR FOR select file_path from fs_list;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET @sql_query = '';

OPEN list_cursor;

REPEAT
  FETCH list_cursor INTO cur_path;

  IF NOT done THEN
    IF @sql_query <> '' THEN
      SET @sql_query = CONCAT(@sql_query, ' UNION ALL ');
    END IF;

    SET @sql_query = CONCAT(@sql_query, ' (select * from fs_walk_scan where file_path like ''', cur_path , ''' order by file_size desc limit 0,30)');
  END IF;

UNTIL done END REPEAT;

SET @sql_query = CONCAT(@sql_query, ' order by file_path, file_size desc');

PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END $$

DELIMITER ;

Попробуйте это.
Вы хотите получить каждую запись, содержащую менее 30 записей, с большим размером файла и тем же путем.

SELECT * 
FROM   fs_walk_scan a
WHERE  ( SELECT COUNT(*) 
         FROM   fs_walk_scan b 
         WHERE  b.file_size  > a.file_size 
         AND    b.file_path  = a.file_path
       ) < 30

Редактировать:

Очевидно, это работает как собака. Итак ... Как насчет синтаксиса цикла?

SELECT DISTINCT file_path
INTO tmp1
FROM   fs_walk_scan a

DECLARE path VARCHAR(255);

SELECT MIN(file_path)
INTO   path
FROM   tmp1 

WHILE  path IS NOT NULL DO
    SELECT * 
    FROM   fs_walk_scan
    WHERE  file_path = path
    ORDER BY file_size DESC
    LIMIT 0,30

    SELECT MIN(file_path)
    INTO   path
    FROM   tmp1
    WHERE  file_path > path 
END WHILE

Идея здесь в том, чтобы 1. получить список путей к файлам 2. цикл, выполняя запрос для каждого пути, который получит 30 самых больших размеров файлов.

(Я просмотрел синтаксис, но мне не очень нравится MySQL, поэтому извиняюсь, если он не совсем там. Не стесняйтесь редактировать / комментировать)

Для этого вы можете использовать группировку и самостоятельное присоединение.

SELECT substring_index(file_path, '\', 4), file_path
from fs_walk_scan as ws1
WHERE 30<= (
select count(*) from fs_Walk_scan as ws2
where substring_index(ws2.file_path, '\', 4) = substring_index(ws1.file_path, '\', 4)
and ws2.file_size > ws1.file_size
and ws2.file_path <> ws1.file_path)
group by substring_index(file_path, '\', 4)

Это по-прежнему запрос O (n) (n - количество групп), но он более гибкий и короче.

Редактировать: Другой подход - использование переменных. Выполнимость для вашей цели будет зависеть от того, как вы собираетесь выполнять этот запрос.

set @idx=0; set @cur_vol=0;                                                                      
SELECT file_volume, file_path, file_size FROM (
    SELECT file_volume, file_path, file_size,
    IF(@cur_vol != a.file_volume, @idx:=1, @idx:=@idx+1) AS row_index,
    IF(@cur_vol != a.file_volume, @cur_vol:=a.file_volume, 0) AS discard
    FROM (SELECT substring_index(file_path, '\', 4) as file_volume, file_path, file_size 
        FROM fs_walk_scan
        ORDER BY substring_index(file_path,'\',4), file_size DESC) AS a
    HAVING row_index <= 30) AS b;

Я еще не пробовал этот код, но концепцию переменных можно использовать для ваших целей.