【MySQL】テーブルを結合し条件を絞った後に重複を削除したい

MySQL5とPHP7で検索システムを製作しています。

info_table
+---+-----+---
| id2 | name |･･･
+---+-----+---
| 1 | AAA | ･･･
| 2 | BBB | ･･･
| 3 | CCC | ･･･
+---+-----+---

list_table
+--+------+-----+-----+
| id | name2 | year | text |
+--+------+-----+-----+
| 1 | AAA | 2017 | あああ |
| 2 | BBB | 2017 | いいい |
| 3 | CCC | 2017 | ううう |
| 4 | AAA | 2017 | えええ |
| 5 | BBB | 2017 | おおお |
| 6 | AAA | 2018 | かかか |
| 7 | CCC | 2018 | ききき |
+--+------+-----+-----+

上記のような2つのテーブルを結合した後、「yearが2017」のレコードを検索。
その時にname2が重複した場合、idが一番大きいレコード以外を削除したいです。
さらに、ソートもできれば最高です。

理想とする結果
+--+------+-----+-----+---+-----+---
| id | name2 | year | text | id2 | name |･･･
+--+------+-----+-----+---+-----+---
| 3 | CCC | 2017 | ううう | 3 | CCC | ･･･
| 4 | AAA | 2017 | えええ | 1 | AAA | ･･･
| 5 | BBB | 2017 | おおお | 2 | BBB | ･･･
+--+------+-----+-----+---+-----+---

結合し検索する所までは出来たのですが、重複したレコードの削除で躓いてしまいました。

$sql = 'SELECT * FROM list_table LEFT JOIN info_table ON name = name2 WHERE year = "2017" ORDER BY id';

GROUP byを試しましたが、理想の検索結果にはなりませんでした。


良い方法をご教示頂ければ幸いです。

No.3 回答者： yambejp 回答日時： 2018/08/09 18:59

> 検索結果の中から一番最新のレコードのみを表示したいので、削除で間違いありません。

DBに対する認識がちがうような・・・
削除はテーブルからデータを削除することですから、削除をすると
list_tableの中身のデータがなくなってしまうのですが大丈夫ですか？

ちなみにインデックスちゃんと貼っていますか？
info_tableはid2に主キーを設定してください
list_tableはidを主キーに、name2,yearをあわせて複合インデックスを設定してください

この回答へのお礼

返信ありがとうございます。

勉強不足で申し訳ありません。
削除ではないですね。
条件で抽出した後、idが一番大きいレコードを残すという事です。

インデックスは主キー、複合インデックス共に設定しています。

お礼日時：2018/08/09 19:48

No.2 回答者： yambejp 回答日時： 2018/08/09 10:43

とりあえずはこう

select * from list_table as t1
inner join info_table as t2 on t1.name2=t2.name
where exists(
select name2 from list_table
where year=2017
group by name2
having max(id)=t1.id
)
order by id

この回答へのお礼

返信ありがとうございます。

検索結果の中から一番最新のレコードのみを表示したいので、削除で間違いありません。

書き忘れてしまい申し訳ないのですが、実際のinfo_tableには1万件ほど、list_tableには2万件ほどのレコードがあります。

yambejp様から頂いたコードを試したところ、理想の検索結果を取得できましたが、終了まで数分かかってしまいました。
検索時間を短くしたいのですが、何か良い方法はありますでしょうか？
よろしくお願いいたします。

お礼日時：2018/08/09 18:24

No.1 回答者： yambejp 回答日時： 2018/08/09 10:33

ほんとに抽出(select)ではなく削除(delete)でよいの？