Доброго дня.


Существует большой массив строк, содержащий WHERE условия запросов. По сути это список файлов (порядка 50 000).

Необходимо сформировать в цикле запросы, для определения присутствия или отсутствия имени файла в определённой таблице.

Размер таблицы post приблизительно 350 МБ (порядка 440 000 строк).

Пытаюсь делать так:




id - некоторое уникальное число в таблице; $db - класс, с методом без закрытия конекта... (хотя суть не в нём...)

В результате, даже при размере массива = 500 записям MYSQL вешается и сваливается с внутренней ошибкой (500).

Как можно оптимизировать этот запрос?

mysql или все же apache?
оптимизировать могут посоветовать не запрос, а процесс.
но для этого лучше, чтоб ты привел-таки пример, как у тебя выглядят эти "$where[$i]"(как будет выглядеть сформированный запрос) и как у тебя хранятся имена файлов.

э.. Я выполняю скрипт на php 5.х, подключаясь к MYSQL... 

Примеры where следующие:

т.е. я ищу имена файлов в short_story и full_story. Затем:



Заметил интересную особенность (поставил микротаймер для отладки): если запускаю процесс повторно, то он выполняется в сотни раз быстрее. Если же время выполнения превышает, помоему, 60 сек, - то Internal Error (полагаю MYSQL возвращает ошибку). Тут же F5 (перезапуск скрипта с теме же параметрами - всё прекрасно. Время выполнения - 2 сек. Кол-во итераций: 2518.

Похоже на кэширование, но мне от него не дегче... Впереди 500к файлов...... :(

что за "ошибка 500"? точнее, где ты её видишь?
в логах апача? в браузере? или же в логе mysql.log написано "Error #500"?

Добавлено через 7 минут и 49 секунд

если ты это видишь в браузере, то нет, эту ошибку генерирует Apache. из-за слишком длительного выполнения РНР-скприта, который(скрипт) в свою очередь, ожидает выполнения SQL-запроса. надо отличать одно от другого. это критично.
если видишь ошибки  на экране в окне браузера - это сообщения, сгенерированный Apache или РНР. ошибки mysql можно найти в логе mysql_error.log на сервере, или же вывести в браузер же при помощи функции mysql_error:
касательно самой процедуры поиска.
лучше отдельно хранить список картинок: имя файла + идентификатор записи в таблице с полем short_story(данные в этой таблице обновлять сразу при вставке записи в "основную таблицу" или по некоему расписанию, скажем, раз в сутки), где хранится упоминание этого файла.
тогда для поиска можно будет использовать не тормознутейший LIKE, а простое сравнение. а само поле с именем файла проиндексировать и поиск будет происходить быстрее на порядок.
вот только зачем сам процесс поиска упоминания файлов? не для того ли, чтоб удалять файлы, на которые нет ссылки? если да, то:

• дешевле может быть плюнуть на это дело: дисковое пространство намного дешевле процессорного времени(если у тебя выделенный сервер, то проще докупить ещё винт на 500ГБ, чем ежедневно на 5 минут грузить процессор на 100%)
• при использовании отдельной таблицы со списком упоминаемых файлов(см. выше) можно сразу удалять файлы при удалении "статьи"

мне кажется все таки нужно оптимизировать запрос.

не совсем понял условия про имена файлов, но запросы like '%...%' нежелательны по той простой причине что мускул не использует индексы, а сканирует всю таблицу, отсюда и долгое зависание. Может все же стоит немного изменить структуру или условия хранения, тогда бд будет возвращать быстро все. (~ до 0.1-0.2сек)
Минусы:
 . надо править структуру
 
Второй вариант поступить иначе. И парсить на стороне клиента (рнр), тогда будет "зависать" РНР скрипт.
плюсы: 
 . бд не нагружается так сильно. и может обрабатывать другие запросы быстрей.

Третий вариант: 1 раз написать скрипт который проиндексирует ваши записи.
то есть пройдет по БД по всем записям пост и повытягивает оттуда строки [a-z0-9].(jpg|bmp|gif...) и составит соответствие айдипоста-строка.
Так будет искать быстрее.
плюсы: быстрый поиск
минусы: не реал тайм информация

Выбирайте вариант который более по душе. У каждого есть + и -.

Ошибка генерируется Апач. Internal S\erver Error (500)


В точку.  Практика показала, что файлов хранится много лишних (из-за неверное работы третьего скрипта ранее) и о них нет упоминания в "текстах"...
 Скрипт пишу на коленке, чтобы запустить раз, вычистить и забыть...

Иметь бОльшее дисковое пространство можно, но хотелось всё же решить проблему в правильном ключе.

Спасибо за ответы, но всё же как лучше быть? Натыкался на mysql_ping() (что то вроде...) может она поможет?


Как я писал выше - это скрипт без юзер интерфеса и "на раз". Т.е. удалили ненужные файлы и ушли..

Добавлено через 3 минуты и 31 секунду
Вот лог:
[Fri Apr 16 15:27:07 2010] [warn] mod_fcgid: read data timeout in 40 seconds

Добавлено через 8 минут и 24 секунды
В скрипте, разумеется есть строки:

Возникает только такие мысли:

читать директории и имена файлов циклом, и внутри ЭТОГО цикла проверять каждый файл на наличие в БД. Т.о. между запросами будет небольшая пауза.

На настоящие момент я использую новую функцию glob() для получения массива файлов, и уже потом использую массив для поиска по базе.

А что мешает записать все файлы во временную таблицу и запускать скрипт с LIMIT? У прочеканных файлов выставлять статус  есть \ нет, а потом выбрать все "нет", можно так же через LIMIT если их много, и грохнуть их. И в конце грохнуть временную таблицу. Как то так.

capitan
Немного не понял...

Проблемы с созданием списка файлов не существует. Существует проблема циклического построения запросов вида:



Таких запросов порядка 400к с различными значениями LIKE %%

А почему бы не поместить в таблицу все эти 50000 строк и не выяснить кто в каком домике живет единственным запросом?

вероятно, правильным решением будет - перелопатить таблицу с файлами, порезать имя файла на пару частей - собственно имя файла и путь к нему. тогда если поставить имя файла индексированным полем - получим очень приличный бонус по скорости выполнения ;) 
В любом случае - like не самое эффективное по скорости решение для текстового поиска в больших таблицах. 

slva2000,  
1. загоняем все файлы в таблицу temp_img   
img | status | check |
img - имя картинки
status - true / false (есть в таблице post или нет)
check - статус проверки true / false ( проверялся в таблице post или нет)

2. Далее запускаем на чек, которые не проверены с лимитом
   SELECT img 
      From temp_img 
      WHERE check = 'false' 
      LIMIT 10

получили список файлов.
3. Проверили на наличие этих файлов в таблице post
4. выставили в temp_img 
check = true
status = true / false (в зависимости от наличия)
5. Повторили 2-4  до тех пор пока есть WHERE check = 'false' 
6. Выбрали из temp_img WHERE status = 'false'  и грохнули их
7. Удалили temp_img

Смысл запускать на проверку пачками , но отмечать уже проверенные. Путей реализаций может быть множество.

ksnk
ТС пояснил, что это одноразовое решение, необходимое для разовой очистки картинок с сервера, которых нет в постах.


Это сообщение отредактировал(а) capitan - 16.4.2010, 19:57

Что имеем: кучу статей с картинками, ещё бОльшую кучу картинок которые не используются.
Что делаем: парсим статьи, ища картинки и заносим их таблицу, поля сам придумаешь. Потом ищем уже в файловой системе ненужные картинки и удаляешь их, вот и всё. 
awdev, уже тебе рассказал как надо.

ksnk уловил мысль!

Спасибо.

Поправь, если ошибаюсь:

табилца файлов (filelist):

id   path   filename


Запрос:



Добавлено @ 20:03


Помоги пожалуйста... тут я слаб

Добавлено через 13 минут и 36 секунд
пока писал, появились новые посты. Действительно решений предложили несколько...

capitan спасибо. 

IgorIV, немного не так. Ситуация такова: Картинок действительно очень много. Но не используемых не так много. Порядка 3-5%. Статей меньше в 10 раз, чем ратинок. Но объём каждой может достигать 300 кБ.

 Определяю ненужные проверкой из созданой таблицы с картинками и наличии её в списке существ. файлов?... Сложно выйдет, т.к. парсить статьи придётся с учётом пути до картинки (а их использованно очень много и регуляркой здесь вряд ли обойдёшься, т.к. путь тоже нужно забирать).


Это сообщение отредактировал(а) slva2000 - 16.4.2010, 20:04

Запрос должен быть примерно таким:

А надо это не делать постоянно. 
Это делается один единственный раз при добавлении документа.

А правильное решение предложил skyboy, 


Добавлено через 1 минуту и 44 секунды

Жесть вы извращенцы.

запрос может быть только вида


Какие like и зачем?