Does MySQL consume significantly more resources compared to other DBMS?

If you're only using "simple" queries, I don't think there's much of a difference regarding ressource usage between MySQL and e.g. Oracle. Those "professional" DBMS do a lot of "magic" regarding caching, prefetching and data maintanance. Of course MySQL does that as well, but it might not be as efficient for really complex databases and advanced queries.

Your choise of DBMS highly depends on what you're planning to do, especially if you're choosing between SQL/NoSQL/Key-Value/..., which are for completely different scenarios… that's not so much a question of memory and CPU usage.

CPU and Memory never are the reason, as they are cheap. The problem is with the I/O speed. NoSQL databases are used in write-intensive applications, as well as in applications which need schema-less database (because changing the table schema in MySQL involves rewriting the table, which may be extremely slow). So some trade-offs are made to optimize the disk operations, which often lead to consuming more CPU, memory or disk space.

Another reason could be pessimistic vs optimistic locks. Which is another topic.

But since the answer to the question "Is it true that mysql consumes significantly more resources (CPU and memory) comparing with other databases" is NO, it is pointless to discuss it further :)