rPman

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

есть хорошее правило, постарайся, все что касается только интерфейса и поведения пользователя в сессии, хранить у пользователя, зачем это на сервер тащить?

Классический алгоритм обхода дерева по линейному списку - рекурсивный.
твой метод должен искать элементы в списке, у которых parent_id указанный в параметрах вызова, возвращает метод html код отрисовки найденных элементов (кстати что возвращать так же зависит от того, как именно ты собираешься рисовать дерево, есть два подхода, рекурсивный и линейный с отступами)

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

Если нужно сгенерровать дерево без не раскрытых ветвей, то где то надо хранить какая ветвь выбрана (раскрыта) и пропускать генерацию вывода для не раскрытых.

Часто генерируют все дерево но для не раскрытых веток указывают стиль - display:none, меняя его при перемещении пользователя по дереву.

это называется pivot table

в mysql (да и многих других) нет их как часть синтаксиса языка, и каждый генерирует sql запросы по ситуации, используя case/if подставляя нужное условие под нужную таблицу - это для вывода (т.е. в select) соответственно создавать такие таблицы не получится, их приходится симулировать, размещая колонки как записи 1-ко-М в одной таблице.

Подавляющее большинство реализуют подобную задачу именно так, в лоб, теряя полезную информацию о типах данных, объединяя их в текстовую строку.

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

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

Это сложнее, но в особых сложных случаях это может быть оправдано.

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

Во первых, ты создаешь html а не excel, да офис умеет открывать такие файлы и даже не ругается, но если хочешь работать с документами напрямую, работай с .xlsx или .ods (open document стандарт поддерживается везде и всеми, это стандарт для документооборота, пользуйся лучше им) - это по факту архив с текстовыми xml документами

По факту, настрой кодировку, с которой ты будешь общаться с базой данных set_charset либо поменяй свою кодировку в php коде на ту же что выдает база данных (utf-8) и пропиши в твоем html в в заголовке

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

Еще момент, но это надо тестировать на твоих данных, как бы это парадоксально не звучало, но скопировать нужные данные в новую таблицу и затем удалить старую целиком может оказаться быстрее (и не будет так нагружать базу), причем заметно, но требует чтобы бакэенд был к такому готов (чтобы вовремя переключиться между таблицами).

Windows конечно медленнее linux работает с диском, но не на порядок же, железо не одинаковое? на linux ssd а на windows hdd?

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

Если железом (индексы нужно размещать в отдельном таблеспейсе на отдельном физическом устройстве, желательно ssd) и настройками все что можно оптимизировал, то усложняй логику.

Например сделай дополнительную таблицу, в которую будешь набирать изменения до определенного количества (или лучше по времени, раз в сутки/час/...) и потом одним запросом переносить, пакетом перестройка индекса идет быстрее.

Логично что и поиск/чтение данных нужно производить сразу из двух этих таблиц, т.е. замедление но незначительное. Если допускать дублирование данных в таблицах, то перенос будет простым и не сильно нагружающим базу (не надо лочить таблицы)

p.s. решать задачу нужно исходя из того как данные читаешь и как в них делаешь поиск. Именно от этого зависит каким образом можно реорганизовать хранение или даже отказаться от mysql

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

where id in (...) работает не быстрее?
ок, залей нужные id в temporary table и делай from table,temp...

Перепроверь, что тип поля действительно числовой а не к примеру varchar (хотя если там числа то это не должно что то менять)
Перепроверь что база данных не повреждена, может просто пересоздать индекс?

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

Так же большее влияние оказывает конфиг и железо, на котором все запускается и к примеру переход от hdd к ssd (кстати даже если индексы отдельно положить на ssd).

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

ты на каждую запись из f_list (отфильльтрованную по условию в where) выводишь ВСЕ users, вот они и умножаются
sql это про умножение таблиц друг на друга, когда они указаны во from

необходимо задать условие как связаны записи из f_list с users, обычно это foreign key индексы (или у тебя там еще табличка промежуточная)

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

Зарос выглядит правильным, значит что то с данными не так.

Посмотри что выводит запрос если оставить только одно условие к примеру по street? и выводи в select *

У тебя таблица work имеет смысл - факты исполняемой работы, а WhoWork - идентификатор Users, кто работает
но мне не понятна связь Work и Order, на схеме она есть но не видно какое поле из Work тут должно использоваться, id у них общий? но это не имеет смысла.

Чем должно являться пустое поле WhoWork? т.е. работа сделана но никем? это так же не имеет смысла и не понятно, по какому условию они должны попасть в результат? все пустые? - тогда добавь их к запросу union

p.s. настоятельно рекомендую провести рефакторинг и переименовать все поля, являющиеся идентификаторами foreign key, в имя_поля_id, при этом имя_поля по возможности должно быть именем таблицы (хотя не всегда это возможно, например две связи),т.е. в твоем случае WhoWork должно именоваться WhoWork_id или user_id.

В этом случае читать запрос и схему станет гораздо проще.

Тестировать - дома на своей виртуалке.

google: lowendstock
cheap budget vps

5-10МБ данные! не надо в базу данных, это плохо!

Настоятельно рекомендую, если это возможно, перенесите обработку строк на клиент, с вероятностью в 99% это не составит труда если вообще требуется (а судя по всему вы просто тупо читаете файл и отдаете его в теле запроса).

Тогда появится возможность передать многопоточную отправку данных веб-серверу, где это реализовано максимально эффективно, а все баги вылизаны годами. Т.е. ваши файлы публикуйте статикой в веб сервере, прямо весь каталог либо создавайте символические ссылки в отдельном публичном каталоге (плохая практика но рабочая) для нужных именно сейчас файлов и с именем, включающим идентификатор сессии (если нужно лимитировать доступ для авторизованных пользователей, создание/удаление симлинка - операция мгновенная, особенно если на рамдиске это делать, если приспичит, можно найти/написать модуль к вебсерверу, управляющий правилом rewrite/location).

Получается вместо отдачи файлов бакэенд должен просто говорить клиентам, какие ссылки нужно открыть, чтобы получить эти файлы (например идентификаторы - они же имена файлов)

Мне кажется эта задача красиво средствами базы данных не решается.
Любое решение будет либо медленным, либо вероятностное распределение итоговое не будет желаемым.

100 запросов в секунду - зачем такой нетипичной задачей грузить базу данных?

Задайся вопросом, сколько у тебя записей? миллионы? миллиарды? может эффективнее будет держать список id на бакэнде массивом и выбирать от туда?
* Запили миниатюрный сервис с сокетами, в который бакэнд при удалении или добавлении записи будет присылать id, а при перезапуске будет загружать весь список id... памяти это занимать будет порядка 16х от количества записей умножить на логарифм (зависит от того какие списки поддерживает бакэнд, нужно хранить упорядочный num -> id, причем это просто массива, при добавлении id добавляется в конец, а при удалении - на место удаленного ставится элемент с конца, к сожалению тогда для быстрого удаления нужен map: id->num).
* Списков таких должно быть несколько - свой по каждому значению веса (считается что вес - целочисленный и вариантов значений значительно меньше общего количества записей), соответственно каждый id попадает в свой список.
* Каждый раз, как идет запрос на случайный id, считаешь два случайных числа:
- первое на интервале [0..максимальное значение веса) - выбираешь какой вес сейчас сработает
это нужно делать с учетом вероятности, которое соответствует каждому весу, т.е. для каждого веса свой интервал значений случайного числа, для 1 это будет попадание между [0..1), для 2 - [1..3), для 3 - [3..6), для 4 - [6..10),.. макс значение интервала равно сумма арифметической прогрессии 1....N где N максимальное значение веса. Левое значение интервала для n считать по формуле суммы арифметической прогрессии а правое + значение веса для него.
- второе, [0..максимальное значение num в соответствующем списке)
второе число даст искомый номер в массиве, а значит и id.
* Для значений весов которые не используются (пустые списки id) нужно будет исключать такое число из списка доступных значений весов, делать новый список с меньшим количеством и давать соответствие значений их этого нового списка с меньшим количеством весов и общим, чтобы такие неиспользуемые веса не попадались.
К примеру из весов 1..10 используются только 1,4 и 10, тогда делаем получаем новый список из 3 элементов, но в формуле расчета интервала для вычисления правой границы использовать значение веса, т.е.:
[0..1), [1,5),[5..15) - общий интервал [0..15)

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

Трудоемкость алгоритма О(1) с очень маленькой константой, но требует память O(n)= n*log(n) с процессором O(n) log(n) на любые модификации.

p.s. Данный алгоритм можно реализовать и в базе данных, на тригерах, так как держать в оперативной памяти списки не требуется, причем базы могут быть отдельные от реальной (очень неудобно и повышенная нагрузка на процессор, лучше использовать key->value базы данных только как хранилище списков id)

Тригерами
если готов что за консистентность данных база данных не будет отвечать, используй что угодно