SQL LIMIT для получения последних записей

Прежде всего:

Кажется, это отношение N:M между items и categories: item может быть в нескольких categories. Я говорю это, потому что categories имеет item_id внешний ключ.

Если это не отношение N:M, вам следует подумать об изменении дизайна. Если это отношение 1:N, где категория имеет несколько элементов, то item должен содержать category_id внешний ключ.

Работа с Н:М:

Я переписал ваш запрос, чтобы сделать внутреннее соединение вместо перекрестного:

  SELECT e.item_id, e.item_value
  FROM 
     tbl_items AS e
  JOIN 
     tbl_categories AS cat 
        on e.item_id = cat.item_id
  WHERE  
     cat.cat_id = 6001
  ORDER BY 
     e.timestamp
  LIMIT 25

Для оптимизации производительности необходимы следующие индексы:

create index idx_1 on tbl_categories( cat_id, item_id)

это не обязательно индекс для элементов, потому что первичный ключ также индексируется. Индекс, который содержит отметку времени, не очень помогает. Чтобы быть уверенным, можно попробовать использовать индекс для элемента с item_id и timestamp, чтобы избежать доступа к таблице и брать значения из индекса:

create index idx_2 on tbl_items( item_id, timestamp)

Чтобы повысить производительность, вы можете изменить свой цикл по категориям одним запросом:

  select T.cat_id, T.item_id, T.item_value from 
  (SELECT cat.cat_id, e.item_id, e.item_value
   FROM 
     tbl_items AS e
   JOIN 
     tbl_categories AS cat 
        on e.item_id = cat.item_id
   ORDER BY 
     e.timestamp
   LIMIT 25
  ) T
  WHERE  
     T.cat_id between 6001 and 6012
  ORDER BY
     T.cat_id, T.item_id

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

Можете ли вы добавить индексы? Если вы добавите индекс в timestamp и другие соответствующие столбцы, ORDER BY не займет 10 минут.

Оставив в стороне все другие факторы, я могу сказать вам, что основная причина того, почему запрос такой медленный, заключается в том, что результат включает longtext столбцов.

Поля BLOB и TEXT в MySQL в основном предназначены для хранения полных файлов, текстовых или двоичных. Они хранятся отдельно от данных строк для таблиц InnoDB. Каждый раз, когда запрос включает сортировку (явно или для group by), MySQL обязательно использует диск для сортировки (поскольку он не может быть уверен заранее, насколько велик какой-либо файл).

И это, вероятно, эмпирическое правило: если вам нужно вернуть более одной строки столбца в запросе, тип поля почти никогда не должен быть TEXT или BLOB, вместо этого используйте VARCHAR или VARBINARY.

UPD

Если вы не можете обновить таблицу, запрос вряд ли будет быстрым с текущими индексами и типами столбцов. Но, так или иначе, вот похожий вопрос и популярное решение вашей проблемы: Как ВЫБРАТЬ последние четыре элемента в категории?