Help: "not equals !=" is very slow

THANKS A LOT! I made my indexes composites like you suggested and everything appears to work like a well-oiled machine so far, just phenomenal!

I will keep an eye on this over the next few days just to confirm everything really is working this great.

Just as a side question, how can this slow behavior be improved? I don’t use this on my random items script, but I use this type of LIMIT x,y very often with my Pagination, and the load speed does slow down quite a bit when clicking the last page numbers.

Thanks
Kind regards

No need to watch it, as it should work. You want to come back when the rows in the table has increased drastically though. There is no rules to this, but I keep seeing that reviewing all the queries as the table sizes reach 100k, million, 5 million etc. seems to be a good idea. (and of course if a customer report a slow page)

You could also base this on the “slow query” report functionality in MySQL, but by manually checking you normally find issues with queries that dont show up on this report as well, i.e. you are able to tune them a little to increase speed.

When you write queries, try to think on how you can write it so it require less “search”, i.e. start with the column that has the least variables/results and go on. This together with good indexes normally will get you good query speed.

NOTE: When I say “least variables/results” I consider columns that contain a real value. Avoid starting with a value that contain a few options, like “enabled” etc. columns, since while they have few variables, they normally have a lot of records of each type so your not able to limit the search properly.

For pagination, it is actually quite easy to solve but it will require some code changes on your side.

What you will need is that for each “page” link, you actually need to attach the page id instead of “page reference”.

Then in the queries you do like this:

 "SELECT * FROM table WHERE table_id=:table_id"

Of course this is simplified, but you get the idea.

You should also read this article, it is a good explanation on the problem, and also have suggestions on how to fix it.
http://mysql.rjweb.org/doc.php/pagination