About doctrine odm questions

In my article detail page require a random 3 records similar to this article.

Before I use mysql as database, and query by “order by rand() limit 3”, however my application crashed because mysql exausted the vps memery when a rogue search-engine scrapying my website by more than 4 concurrency. I am so disappoint!!

First of all, the vps I used the aliyun’s ecs, the ability is very weak. Some colleague suggest me to create the html file to solve the problem. I don’t want to do that. I want a dynamic application rather than a static one.

So I turn to mongodb instead of mysql.

I use https://www.doctrine-project.org/projects/doctrine-mongodb-odm/en/stable/reference/aggregation-builder.html#aggregation-builder
It is called odm. I am not familar to this.

I just wanna 3 random recoards which has the same class with the current detail.
Any big gun can help on this?

I followed and changed a little according to the demo as the above linke. However what I got is not the record data, I don’t know what I got, it seems a mess of codes, and when I print_r it, it keep outputting for a long time and almost cause a web browser crash.

thank you in advance.

ps: getPrev and getNext work well, just the getSimilar works not as I expected.
I run db.article.aggregate([{ $sample: { size: 3 }}]) in local (I mean on my work computer of windows 10 with a mongodb server) envirment, it takes several micro second. However when I run it in the online vps(very cheap one, ubuntu 16.04 server with mongod) it takes almost 6 seconds!! no matter the time it takes, first of all I wanna to make the code correct and get it run the right way.

ORDER BY RAND() is potentially O(N!) (factorial!!!) in complexity: do not use!

This has nothing to do with ORM/ODM, but solely with the sorting of your records, since sorting is computed based on a routine +RAND()) evaluated per-scanned-row in your resultset.

Depending on the SQL engine, the RAND() routine may be called only once (scanning your entire dataset) or once per sorting comparison during quicksort/merge-sort operations.

Consider that I managed to bring down a production system due to the fact that I did not use array_shuffle(), but instead something similar to usort(..., function () : int { return random_int(-1, 1);}) - that was with an array with 6 elements. Yes, you read it right: shuffling an array of 6 elements brought down a production system.

The correct way to deal with picking a random record is:

  1. SELECT COUNT(1) FROM ... WHERE <criteria>

  2. 'SELECT * FROM ... WHERE <criteria> OFFSET' . random_int(0, $theCountYouGotAbove - 1);

That will give you one random record, incurring in a much lower computational complexity.

4 Likes

Hi @ocramius, thanks so much for your reply!

I have got more than 120000 records in my database table. I got your suggestion, that’s good. I will following your suggestion. And offset 3 times for each time to fetch one record.

And also I wanna to learning something about mongodb doctrine odm stuff. Can you teach me about this? use aggrate for select the same class and sample 3 records?

I don’t really use MongoDB, sorry, and tbh still fail to see a reason to (personally) ever use it :woman_shrugging:

ok, thank you again.