Magento 2.4's performance issues are largely due to the use of the DISTINCT operator in its database queries and the 'num_results > 0' condition in its query design. The DISTINCT operator, which removes duplicates, significantly slows down queries and is made redundant by the unique constraints already present in the 'search_query' table. Its removal could boost performance. The 'num_results > 0' condition, while ensuring accurate search results, hinders speed when handling a high volume of search terms. Balancing optimization and accuracy is essential. Potential solutions include using Elasticsearch for search operations, inserting search terms into the table asynchronously in batches, or inserting only a portion of the search terms.
Full article here: https://medium.com/@lawsuithelpdesk/decoding-the-magento-2-4-23ace0c16df0