0

I have created a php project based on laminas and mezzio with a MySQL database to stora media files. At only 1400 media files, the search is really slow (multiple seconds for search results).

My tables are

user

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL,
  `username` varchar(255) CHARACTER SET utf16 COLLATE utf16_unicode_ci NOT NULL,
  `password` varchar(255) CHARACTER SET utf16 COLLATE utf16_unicode_ci NOT NULL,
  `email` varchar(255) CHARACTER SET utf16 COLLATE utf16_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
);

role

CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `access` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

media

CREATE TABLE `media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `order` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `is_private` int(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);

media_keyword

CREATE TABLE `media_keyword` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `media_id` int(11) NOT NULL,
  `keyword` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

media_role

CREATE TABLE `media_role` (
  `media_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`media_id`,`role_id`)
);

I am using doctrine to create the queries and the follogwing query is used to search the databse for a given search query:

SELECT DISTINCT m.*, u.username 
FROM media AS m 
LEFT JOIN user AS u ON u.id = m.user_id 
LEFT JOIN media_keyword AS k ON k.media_id = m.id
LEFT JOIN media_role AS r ON m.id = r.media_id
WHERE (m.is_private = 0 OR (m.is_private = 1 AND m.user_id = :user_id))
AND (r.role_id = :role_id OR r.role_id IS NULL) 
AND (m.filename LIKE :search OR m.title LIKE :search OR k.keyword LIKE :search)
GROUP BY m.id
LIMIT :offset, :page_size

When I query the database directly via MySQL Workbench, a search query takes about 7-14 seconds. It seems that it takes even longer if the search is executed in the PHP / Lamins / Mezzio application.

On my local machine it works pretty fast. The live machine has never more then 70% usage of CPU / RAM on a search, so I don't think it is depending on hardware.

Can someone help me please and tell me what I could do to improve performance?

hapablap
  • 379
  • 6
  • 23
  • I think you need to create an 'index' for every field you use in 'JOIN'. Why use 'LEFT JOIN' and not 'INNER JOIN'. – Ehab Nov 13 '22 at 22:36
  • Is the user required to enter all of user_id, role, and search? If not, what are the default values? – Rick James Nov 14 '22 at 00:09
  • If `media_role.role_id` is not nullable then remove `AND (r.role_id = :role_id OR r.role_id IS NULL)` but add `AND r.role_id = :role_id` to according ON clause. – Akina Nov 14 '22 at 05:24
  • *When I query the database directly via MySQL Workbench, a search query takes about 7-14 seconds. .. On my local machine it works pretty fast.* Compare execution plans. Compare structures, especially indices. Renew statistic. – Akina Nov 14 '22 at 05:26
  • Please post TEXT results of EXPLAIN SELECT no_sql_cache DISTINCT (rest of your query); for analysis of missing indexes. – Wilson Hauck Nov 14 '22 at 13:59
  • @Ehab I am using left join so that I still get medias in case the joined data does not exist (e.g. keywords). – hapablap Nov 15 '22 at 08:11

1 Answers1

0

The solution was to add Foreign Keys so that Indexes are created by the database. It was possible to add the Foreign Keys even with data already existing in the table. Since then the performance was extremely improved.

To explain why I didn't use Foreign Keys before: I wanted to use code first and handle all relations via code (delete constraints etc.). It turned out that this was not a good idea.

hapablap
  • 379
  • 6
  • 23