The slowness of your query could be due to a few reasons:
- The subquery in the SELECT clause is executed for each row in the
psEventsCalendartable. This can be very slow if the table has many rows. - The
ORDER BY RAND()clause can be very slow because it requires a full table scan and a sort. - There might be missing indexes on the columns used in the WHERE and ORDER BY clauses.
Here are a few suggestions to improve the performance:
-
Instead of using a subquery in the SELECT clause, you can use a JOIN to the
psUploadedFilestable. This will allow MySQL to execute the join operation more efficiently. -
Instead of using
ORDER BY RAND(), you can use a different method to randomize the results. One common method is to generate a random number in your application, and use it as an offset in the LIMIT clause. -
Make sure you have indexes on the columns used in the WHERE and ORDER BY clauses. In this case, you should have indexes on
psEventsCalendar.visible_on_the_front,psEventsCalendar.enable, andpsUploadedFiles.category.
Here is an example of how you can rewrite your query:
SELECT
A.id_events_calendar,
A.url_address_pl,
A.title AS title,
A.title AS description,
A.title AS keywords,
A.hour,
A.date,
CONCAT(B.file_path, '/', B.file_name) AS image
FROM
psEventsCalendar A
LEFT JOIN
(SELECT * FROM psUploadedFiles WHERE enum=14 ORDER BY number LIMIT 1) B
ON
A.id_events_calendar = B.category
WHERE
A.visible_on_the_front = '1'
AND A.enable = '1'
ORDER BY
A.id_events_calendar
LIMIT 10 OFFSET RAND()*(SELECT COUNT(*) FROM psEventsCalendar);
Please note that the RAND() function in the OFFSET clause is a placeholder. You should replace it with a random number generated in your application. Also, the subquery in the LEFT JOIN clause assumes that the psUploadedFiles table is not very large. If it is, you might need to find a different way to select the first image for each event.
