我在索引中首先指定哪个字段是否重要？
将
CREATE SET TABLE my_table
(
validity_date DATE
, branch_id SMALLINT
, account_class_id BYTEINT
, catalog_id INTEGER
, No_events FLOAT
)
PRIMARY INDEX (validity_date, branch_id);
被服务器视为相同
CREATE SET TABLE my_table
(
validity_date DATE
, branch_id SMALLINT
, account_class_id BYTEINT
, catalog_id INTEGER
, No_events FLOAT
)
PRIMARY INDEX (branch_id, validity_date);
我使用 MySQL 已经有一段时间了，但仍在学习使用它的最佳方法。
为了练习，我编写了一个查询，以获取 sakila 示例数据库中每个演员的最有特色的电影类别，我们可以使用 MySQL 工作台获取该数据库。结果如下所示：
我通过以下查询获得结果。但是，我不得不重复同一个查询两次，一次在from外部查询部分，第二次在where子查询部分。
select `MCPA`.`first_name` ,`MCPA`.`last_name`,
group_concat(`MCPA`.`category`) as `most_featured_categories`
from
(
SELECT AC.first_name as `first_name` ,AC.last_name as `last_name` ,
CA.`name` as `category` ,
count( distinct FI.film_id ) as `number_of_movies` ,
count( distinct FI.film_id ) /
fNumber_Of_Films_Per_Actor (AC.first_name, AC.last_name) * 100
as `percentage_of_movies_done`
from actor as AC
inner join film_actor as FIAC ON AC.actor_id = FIAC.actor_id
inner join film as FI ON FIAC.film_id = FI.film_id
inner join film_category as FICA ON FI.film_id = FICA.film_id
inner join category as CA ON FICA.category_id = CA.category_id
group by AC.first_name, AC.last_name, CA.`name`
) as `MCPA`
where `MCPA`.`number_of_movies` =
(
SELECT max(`SubMCPA`.`number_of_movies`)
from
(
SELECT AC.first_name as `first_name`, AC.last_name as `last_name`,
CA.`name` as `category` ,
count( distinct FI.film_id ) as `number_of_movies` ,
count( distinct FI.film_id ) /
fNumber_Of_Films_Per_Actor (AC.first_name,
AC.last_name
) * 100
as `percentage_of_movies_done`
from actor as AC
inner join film_actor as FIAC ON AC.actor_id = FIAC.actor_id
inner join film as FI ON FIAC.film_id = FI.film_id
inner join film_category as FICA ON FI.film_id = FICA.film_id
inner join category as CA
ON FICA.category_id = CA.category_id
group by AC.first_name, AC.last_name, CA.`name`
) as `SubMCPA`
where 1=1
and `MCPA`.`first_name` = `SubMCPA`.`first_name`
and `MCPA`.`last_name` = `SubMCPA`.`last_name`
)
group by `MCPA`.`first_name`, `MCPA`.`last_name` ;
我曾尝试使用
select max(`SubMCPA`.`number_of_movies`)
from `MCPA` as `SubMCPA`
where 1=1
and `MCPA`.`first_name` = `SubMCPA`.`first_name`
and `MCPA`.`last_name` = `SubMCPA`.`last_name`
但它没有用；好像workbench不识别派生表，需要我重新定义它。
我有其他方法可以避免输入代码吗
(
select AC.first_name as `first_name`
,AC.last_name as `last_name`
,CA.`name` as `category`
,count( distinct FI.film_id ) as `number_of_movies`
,count( distinct FI.film_id ) /
fNumber_Of_Films_Per_Actor (AC.first_name, AC.last_name) * 100
as `percentage_of_movies_done`
from actor as AC
inner join film_actor as FIAC
on AC.actor_id = FIAC.actor_id
inner join film as FI
on FIAC.film_id = FI.film_id
inner join film_category as FICA
on FI.film_id = FICA.film_id
inner join category as CA
on FICA.category_id = CA.category_id
group by AC.first_name, AC.last_name, CA.`name`
) as
两次？
** 我试图通过先创建一个表然后引用它来避免它，但是也许还有其他方法吗？
此外，似乎在一个查询中完成所有操作会提高性能。
任何建议将不胜感激，希望我的问题很清楚，我没有描述所有的数据库结构以避免太乏味的问题。