这个 MySQL 查询可以更有效吗? [英] Can this MySQL query be more efficient?

问题描述

更新 这是一个 sqlfiddle http://sqlfiddle.com/#!2/e0822/1/0

UPDATE Here's a sqlfiddle http://sqlfiddle.com/#!2/e0822/1/0

我有一个应用程序的 MySQL 数据库 (itunes_id)，每个应用程序 ID 都有一个注释字段.为了保留历史记录，每次更改评论时，都会添加一行新数据.在下面的查询中，我只想要每个应用 (itunes_id) 的最新条目(最高 id)的列表.

I have a MySQL database of apps (itunes_id), each app id has a comments field. To preserve a history, every time a comment is changed, a new row of data is added. In the query below, I just want a list of the latest entry (highest id) of every app (itunes_id).

这是我的数据库的标题:

Here are the headers of my db:

id  (key and auto increment)
itunes_id
comments 
date

此查询正在获取给定 itunes_id 的最新条目.我怎样才能使这个查询更有效?

This query is getting the latest entry for a given itunes_id. How can I make this query more efficient?

SELECT * FROM (
    SELECT * FROM (
        SELECT * FROM Apps
        ORDER BY id DESC
    ) AS apps1
    GROUP BY itunes_id
) AS apps2
LIMIT 0 , 25

推荐答案

这个查询使用一个子查询，它分别获取每个 itunes_ID 的最大 ID.如果子查询的结果匹配两列:itunes_ID 和 ID.

This query uses a subquery which separately gets the maximum ID for every itunes_ID. The result of the subquery is then join back on the original table provided that it matches on two columns: itunes_ID and ID.

SELECT  a.*
FROM    Apps a
        INNER JOIN
        (
            SELECT  itunes_id, MAX(ID) max_id
            FROM    Apps
            GROUP   BY itunes_id
        ) b ON a.itunes_id = b.itunes_id AND
                a.ID = b.max_ID
LIMIT   0, 25

为提高性能，请在 itunes_ID 和 ID 列上创建复合列 INDEX.EG，

For faster performance, create a compound column INDEX on columns itunes_ID and ID. EG,

ALTER TABLE Apps ADD INDEX (itunes_ID, ID)

这篇关于这个 MySQL 查询可以更有效吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！