Walt Disney

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

Соответственно результаты будут состоять из двух запросов: простого селекта по уже агрегированным данным + сам агрегирующий запрос по суточной таблице.

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

1) можно просто по крону выполнять в консольную команду типа cut access.log -d' ' -f 7 | sort | uniq -c и сбрасывать результат в базу.
2) мемкеш не будет удобен, рано или поздно он начнёт сам удалять счётки, по тайм ауту или по нехватке памяти и это надо иметь ввиду, соответственно и решение должно быть рассчитано на потери. Т.е. например если в мемкеш ключ пуст, то забираем значение из базы и кладём в него, для отображения тоже используем значение из мемкеша, при запросе страницы инкрементируем ключ в мемкеш. А вот значение в базе обновляем например из методом описным в первом пункте. Соответственно играясь с временем жизни записей в мемкеше и частотой парсинга логов будем регулировать возможные лаги в значениях счётчика.
3) парсинг логов можно заменить на очереди типа rabbitmq
4) в большинстве случаев можете не париться и вынести счётчики в redis.io

1) Забиваете на SQLite и SQL Server, первая не про то, вторая под windows.
2) как бы вы не спроектировали базу, и что бы вы не выбрали Mysql или Postgres, отличаться будет только запрос на индексацию для сфинкса, который в любом случае будет отдавать вам primary key нужного вам документа, а выборка по PK в любой базе элементарная операция. Кеширование не нужно, сфинкс ваши максимальные 5 000 000 слов даже не заметит.

на laravel вы видимо будете использовать ORM, поэтому для вас, что postrgers что mysql. Ну разве что mysql более коробочный.

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

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

N-граммы, потом статистика опечаток и ошибок, потом корректировка через анализ поведения пользователя в выдаче и на сайтах на которые он перешёл.

Тут надо понимать, что чем больше информации тем проще решить эту задачу. На не большом сайте можно решить проблему с неверной раскладной и небольшое количество вариантов ошибок и опечаток и всё,у поисковиков всё на порядок круче.

можно, гуглите словосочетания Adjacency list, Matherialized Path, Nested sets, Closure Table не забывая подставлять слово MySQL, найдёте кучу описаний и кучу готовых реализаций.

Всё названное это оптимизированные варианты хранения деревьев в реляционных БД.

В общем случае REDIS это то что доктор прописал.
Если задача и язык позволяет, то можно поиграться с judy array

Всё же sphinxsearch.

В нём достаточно инструментов для решения этой задачи. Словари нужны в любом случае для построения выборки результирующего документа/страницы или их множества. Так же обычно нужны небольшие словарики для разбора направления ограничения выборки, всякие "до/от, больше/меньше, старше/младше, дальше/ближе, в/из и т.п."

Неоднократно решал задачу поисковых запросов на естественном языке к различным структурированным данным. И замечу, что если источником запроса служит текстовое поле то пользователи тупят, не пользуются этим т.к. хотят видеть сложную форму с кучей селект боксов галочек и т.д. Ни кто не хочет писать: "чёрный лексус 2 литра не старше 2000 года". Зато если источник ввода это распознанная устная речь на мобильнике то это работает.

Вы не должны их хранить.
Пользователь даёт доступ вашему приложению с таким то id доступ к таким то своим данным. Всё, пока пользователь не отзовёт права у вашего приложения, он может использую access key приложения получать доступ к тому, что он вам разрешил.

Выше речь про приложения vk и fb, в standalone-приложениях немного иначе но суть также.

Общее для всех mysql:
Проверить какой запрос отрабатывает система если replace то плохо если update или insert on duplicate key update - то ок.
Если в action_history есть индексы кроме примари кей то их скорее всего стоит убрать т.к. вероятно запросы ( сортировки или агрегации) по этим полям будут приводить в любом случае к фулскану и индекс не будет иметь смысла, а при апдейте перестройка будет постоянно всё класть.

Для Maria: убедиться, что в Maria XtraDB не сломана построчная блокировка (всё таки опыт продакшена у марии неё вялый ). Потом вызывает опасение реализация внешнего индекса в таблицах разного типа, возможно это как то может ломать построчную блокировку. Если у вас лок всей таблицы разберитесь откуда, так не должно быть

В общем и целом, поразмышлять что INSERT DELAYED VALUES (1,2,3),(..,..,..),(N,N,N) для записи всех действий будет работать заметно веселее особенно без индексов и в одном потоке, а после некоторых шаманств с агрегацией по крону ещё не будет деградировать от распухания. Что можно про крону парсить access.log (понимать что он не рилтайм), в этом случае будет одна пачка апдейтов допустим раз в минуту и user_id будет заапдейчен только один раз, это будет пожалуй самой простой реализацией задачи упорядочивания и фильтрации потока апдейтов к mysql. Парсинг лога можно заменить на RabbitMQ, или написать своего демона который будет висеть на соке и рулить.

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