子查询显着降低查询速度(Subquery Slows Query down significantly)

子查询显着降低查询速度(Subquery Slows Query down significantly)

我正在尝试执行一个替换多个查询以生成结果集的查询。 我有一个相当直接的1到1连接集，但是我试图从一个表中获得一个列，每个主记录保留多个记录。 基本上它是这样的：

带有主键id列和一堆其他列的meet_entries，meet_entries_statuses，带有autoincrement id列和meet_entry的id列的外键

meet_entries中每行的meet_entry_statuses中可能有多行。 在我的查询中，我正在尝试获取与meet_entry主键ID关联的最近添加的（如此最高的id号）行。

这是我的查询：

SELECT meet_entries.id, member.firstname, member.surname, member.gender, member.dob, clubs.code, clubs.clubname, meet_entries.meals, meet_entries.massages, meet_entries.cost, meet_entries.cancelled, (SELECT meet_entry_status_codes.description FROM meet_entry_statuses, meet_entry_status_codes WHERE meet_entry_statuses.code = meet_entry_status_codes.id AND meet_entry_statuses.id = meet_entries.id ORDER BY meet_entry_statuses.id DESC LIMIT 1) as status, COUNT(DISTINCT meet_events_entries.id) as entries FROM meet_entries, meet_events_entries, member, clubs, meet_entry_statuses, meet_entry_status_codes WHERE meet_entries.meet_id = ? AND meet_entries.id = meet_events_entries.meet_entry_id AND meet_entries.member_id = member.id AND meet_entries.club_id = clubs.id GROUP BY meet_entries.id ;

添加位：

(SELECT meet_entry_status_codes.description FROM meet_entry_statuses, meet_entry_status_codes WHERE meet_entry_statuses.code = meet_entry_status_codes.id AND meet_entry_statuses.id = meet_entries.id ORDER BY meet_entry_statuses.id DESC LIMIT 1) as status,

将查询从.2秒开始，到永不完成。 meet_entries中只有10个匹配的行。 meet_entries_statuses中约有50行适用于meet_entries中的10行，但正如我所说，我只想为每个meet_entries行获取1行。 有关更好的方法的任何建议吗？

I'm trying to do a query that replaces multiple queries to produce a result set. I've got a fairly straight forward set of 1 to 1 joins, but one column I'm trying to get from a table that keeps multiple records per primary record. Basically it's like this:

meet_entries with a primary key id column and a bunch of other columns, meet_entries_statuses, with an autoincrement id column and a foreign key to the id column of meet_entry

There may be multiple rows in meet_entry_statuses for each row in meet_entries. In my query, I'm trying to get the most recently added(so highest id number) row associated with the meet_entry primary key id.

Here's my query:

SELECT meet_entries.id, member.firstname, member.surname, member.gender, member.dob, clubs.code, clubs.clubname, meet_entries.meals, meet_entries.massages, meet_entries.cost, meet_entries.cancelled, (SELECT meet_entry_status_codes.description FROM meet_entry_statuses, meet_entry_status_codes WHERE meet_entry_statuses.code = meet_entry_status_codes.id AND meet_entry_statuses.id = meet_entries.id ORDER BY meet_entry_statuses.id DESC LIMIT 1) as status, COUNT(DISTINCT meet_events_entries.id) as entries FROM meet_entries, meet_events_entries, member, clubs, meet_entry_statuses, meet_entry_status_codes WHERE meet_entries.meet_id = ? AND meet_entries.id = meet_events_entries.meet_entry_id AND meet_entries.member_id = member.id AND meet_entries.club_id = clubs.id GROUP BY meet_entries.id ;

Adding the bit:

(SELECT meet_entry_status_codes.description FROM meet_entry_statuses, meet_entry_status_codes WHERE meet_entry_statuses.code = meet_entry_status_codes.id AND meet_entry_statuses.id = meet_entries.id ORDER BY meet_entry_statuses.id DESC LIMIT 1) as status,

takes the query from being .2 of a second, to never finishing. There are only 10 matching rows in meet_entries. There are about 50 rows in the meet_entries_statuses appropriate to the 10 rows in meet_entries, but as I say I'm trying to only get 1 row for each meet_entries row. Any suggestions on a better way to do this?

最满意答案

我不太清楚为什么你会加入meet_entry_statuses和meet_entry_status_codes而没有任何连接条件。 这样做意味着您正在进行交叉连接。 因此，如果meet_entries有N行，并且上面两个表中分别有M和P行，则最终生成N x M x P行。

你能尝试取出两个连接吗？ 我在以下重写的查询中将它们标记出来：

SELECT meet_entries.id, member.firstname, member.surname, member.gender, member.dob, clubs.code, clubs.clubname, meet_entries.meals, meet_entries.massages, meet_entries.cost, meet_entries.cancelled, (SELECT meet_entry_status_codes.description FROM meet_entry_statuses as mes , meet_entry_status_codes as mesc WHERE meet_entry_statuses.code = meet_entry_status_codes.id AND meet_entry_statuses.id = meet_entries.id ORDER BY meet_entry_statuses.id DESC LIMIT 1 ) as status, COUNT(DISTINCT meet_events_entries.id) as entries FROM meet_entries as me , meet_events_entries as mee , member as m , clubs as c , meet_entry_statuses as mes -- try taking this out , meet_entry_status_codes as mesc -- try taking this out WHERE meet_entries.meet_id = ? AND meet_entries.id = meet_events_entries.meet_entry_id AND meet_entries.member_id = member.id AND meet_entries.club_id = clubs.id GROUP BY meet_entries.id ;

I am not very sure why you would join with meet_entry_statuses and meet_entry_status_codes without any join conditions. Doing so means that you are doing a cross join. So if there are N rows in meet_entries, and M and P rows in the above two tables respectively, you end up generating N x M x P rows.

Can you try taking out the two joins. I marked them out in the following rewritten query:

SELECT meet_entries.id, member.firstname, member.surname, member.gender, member.dob, clubs.code, clubs.clubname, meet_entries.meals, meet_entries.massages, meet_entries.cost, meet_entries.cancelled, (SELECT meet_entry_status_codes.description FROM meet_entry_statuses as mes , meet_entry_status_codes as mesc WHERE meet_entry_statuses.code = meet_entry_status_codes.id AND meet_entry_statuses.id = meet_entries.id ORDER BY meet_entry_statuses.id DESC LIMIT 1 ) as status, COUNT(DISTINCT meet_events_entries.id) as entries FROM meet_entries as me , meet_events_entries as mee , member as m , clubs as c , meet_entry_statuses as mes -- try taking this out , meet_entry_status_codes as mesc -- try taking this out WHERE meet_entries.meet_id = ? AND meet_entries.id = meet_events_entries.meet_entry_id AND meet_entries.member_id = member.id AND meet_entries.club_id = clubs.id GROUP BY meet_entries.id ;