if(strpos($_SERVER['REQUEST_URI'],'comments/all')&&$this->cms_user->id){$this->model->limit($perpage)->join.=("JOIN (SELECT id FROM cms_comments WHERE is_deleted IS NULL ORDER BY id DESC LIMIT ".($page-1)*$perpage.", $perpage) c ON c.id = i.id ");}
if(!$this->cms_user->id){$this->model->limit($perpage)->join.=("JOIN (SELECT id FROM cms_comments WHERE is_private = '0' AND is_deleted IS NULL AND is_approved = '1' ORDER BY id DESC LIMIT ".($page-1)*$perpage.", $perpage) c ON c.id = i.id ");}
$items=$this->model->getComments();
на mariadb10.3 раньше всё работало. Выборка полторы секунды.
Хотя… изменил индекс в cms_comments_rating