I did a category search for genre = Action/Adventure and score 7.9 or more. I then clicked on 'score' to sort the results in descending order.
The last entry on page 2 was:
20
ReadNo Flag
The Hawk and The Chipmunk
Action/Adventure
(premier members only)
The Scot
The first entry on page 3 was:
21
ReadNo Flag
The Hawk and The Chipmunk
Action/Adventure
(premier members only)
The Scot
Both stories pointed to s/45443/the-hawk-and-the-chipmunk.
AJ
Huh, that's a real puzzle.
It happens again on 3/4 move, but it has two duplicated stories from 3 to 4, but then it doesn't happen anymore.
True head scratcher.
There is an identical problem in all paginated results on the site. When a query result is modified (story added/deleted/voted upon) you can have some stories appearing on two consecutive page, or some story missing from the pages.
I reported that 12 years ago.
The problem lies either with the (15 minutes?) site cache or the sql query results cache. going back and forward or clearing the browser cache will not solve the issue, as the cache of paginated result is by design inconsistent.
Waiting 15 minutes should clear the problem.
A workaround is to change the pagination page size. and restart the query.
The problem lies either with the (15 minutes?) site cache or the sql query results cache.
The code for the category search page does not cache any results. If this is a caching issue for real, it must be sql query caching on the server, if any.
I checked the DB servers' config and there is no specific caching changes, unless I need to turn off caching if it's on by default.
Let's just say that I would be very hesitant to turn off any caching on the MySQL servers, unless I want cough up more money for bigger servers.
While this issue is puzzling and potentially annoying, its severity isn't high.
If there is no server cache, then it's a problem with sql OFFSET or LIMIT with identical ordered value (score here) in the result. You can add a secondary sort on a unique field like the id to fix the issue.
https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html
If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.
You are a GENIUS!
That actually was the problem. I added secondary sort by alphabetical order and now the story in question appears only in position 21.
You've solved more problems with my code than I have!
Better test your new query with EXPLAIN, or even better use MySQL Workbench Visual Explain. I doubt it's correctly optimized with the current indexes.
If Gauthier isn't already a premier member, then I think they have just deserved an upgrade! LOL ๐
Lazeez
Sorry, couldn't resist - just found this about an Indian all-girl group:
She describes their debut single, Lazeez (meaning "delicious" or "tasty" in Urdu), as a song that "celebrates the modern woman and encourages self-love".
AJ
Same meaning in Arabic :)
My nick name is written ูุฐูุฐ ุฌุฏุง in arabic and is basically is a translation of the Lebanese 'Kteer Lazeez', pronounced Lazeez Jiddan, and it literally means 'Very Delicious'.
When growing up in Lebanon and my last few years in Beirut in the 80s, I guess my friends found me pleasant enough to always describe like that, it was meant as 'Very Cool'. So when I started SOL, I translated the Lebanese wording to formal Arabic (jiddan instead of Kteer) and adopted it as my name on the site and it stuck since.
Thank you for sharing that.
My own pseudonym bears testimony to my lack of imagination :-(
AJ
I checked the DB servers' config and there is no specific caching changes, unless I need to turn off caching if it's on by default.
I was referring to a PHP cache framework often implemented with memcached by dev when facing SQL server overload.
One of MySQL cache, The Query Cache store query results. It is invalidated on dependant table changes, so it would be consistent and could not be the cause of the problem.
You should fine tune the MySQL query cache size to improve your SQL Server performance:
https://dev.mysql.com/doc/refman/5.7/en/query-cache-status-and-maintenance.html
In low memory situations, I add SQL_NO_CACHE or SQL_CACHE to the SELECT to help the SQL Cache determine which queries should be cached.
