How to reduce query execution time?

Question

MySQL

Hello. I have a fairly simple query on a table that only has 50k entries:

SELECT count(p.`id`) cnt FROM `posts` p  WHERE p.`category_id` <> 4 AND p.`status` = 1

And it runs in 30ms.
innoDb table
Indexes category_id + status are set

I just have no idea how to speed it up, help plz.

UPD: The posts have a filter for many fields, for each filter and a combination of them, it is not possible to store the quantity value. Therefore, it is impossible to store the quantity value with recalculation by the INSERT trigger

Answer 1

You do the separate table, in which unique field and record store value of this kol-va.
Create triggers for the posts table for insert, update, delete operations.
In the trigger, use this query to count the quantity and insert/update this value in the quantity table.
With this trigger, you will transfer 30ms from the select operation, counting the number, to the insert, update, delete operations, during the insertion of a new value (updating the category, status, deleting a record), and in the trigger you can ignore the recalculation if the record is inserted of the wrong type ( not category 4 and not status 1).
The number of special records can always be obtained instantly from the same table.

Answer 2

If so

<IfModule mod_rewrite.c>
RewriteBase /
RewriteEngine On
RewriteCond %{HTTPS} off
RewriteCond %{HTTP:X-Forwarded-Proto} !https
RewriteCond %{REQUEST_URI} !api
RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]
RewriteCond %{HTTP_HOST} ^www.hookahlocator.ru$ [NC]
RewriteRule ^(.*)$ https://hookahlocator.ru/$1 [R=301,L]
RewriteCond $1 !^(index\.php|robots\.txt|images|css|js)
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php?/$1 [L,QSA]
</IfModule>

Just clean the cache tightly before checking!