使用like和join的缓慢数据库查询

Question

我已经创建了一个基于laminas和mezzio的php项目，其中包含一个MySQL数据库，用于stora媒体文件。只有1400个媒体文件时，搜索速度非常慢(搜索结果需要几秒钟)。

我的桌子是

用户

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`)
);

角色

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`)
);

媒体

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`)
);

我使用理论来创建查询，而follogwing查询用于搜索数据库中给定的搜索查询：

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

当我通过MySQL工作台直接查询数据库时，搜索查询大约需要7-14秒。如果在PHP / Lamins / Mezzio应用程序中执行搜索，似乎需要更长的时间。

在我的本地机器上工作得很快。在一次搜索中，活动机器的CPU / RAM使用率从未超过70%，所以我不认为它依赖于硬件。

有人能帮我吗，告诉我我能做些什么来提高我的表现？

Answer 1

解决方案是添加Foreign，以便由数据库创建索引。即使表中已有数据，也可以添加Foreign。从那以后，性能得到了极大的提高。

为了解释为什么我以前没有使用Foreign :我想先使用代码，然后通过代码处理所有关系(删除约束等)。结果发现这不是个好主意。