我創建了一個從多個表中檢索資料的查詢。當我運行它時，執行大約需要 200 秒。我現在正試圖提高查詢效率，但我已達到極限，無法找到解決方案。您是否知道如何改進以下查詢？
SELECT s1.otherdeed_id, s1.timestamp_start, s1.listingprice, s1.listing_currency, otherdeed_index.category, otherdeed_index.koda_id, kodas.mega, otherdeed_index.artifact_name, otherdeed_index.sediment
FROM otherdeed_opensea_listings s1
JOIN (
SELECT otherdeed_id, listingprice, MAX(timestamp_start) AS timestamp_start
FROM otherdeed_opensea_listings
GROUP BY otherdeed_id) AS s2
ON s1.otherdeed_id = s2.otherdeed_id AND s1.timestamp_start = s2.timestamp_start
INNER JOIN otherdeed_index ON otherdeed_index.id_otherdeed = s1.otherdeed_id
INNER JOIN kodas ON otherdeed_index.koda_id = kodas.kodaname
ORDER BY otherdeed_id;
該查詢應提供otherdeed_opensea_listings表（16k 個條目）的最新上市價格串列，并將其與表otherdeed_index（100k 個條目）和kodas（10k 個條目）連接以獲取一些相關資料以進行分析。
我對otherdeed_opensea_listings表進行分組，只為每個otherdeedid獲取一個結果，此外，只獲取具有最高timestamp_start值的最新結果也很重要。
下面你可以看到我的sql結構：
CREATE TABLE `kodas` (
`kodaid` int(11) NOT NULL AUTO_INCREMENT,
`kodaname` int(11) NOT NULL,
`image` varchar(255) NOT NULL,
`mega` tinyint(1) NOT NULL,
`clothing` varchar(100) NOT NULL,
`core` varchar(100) NOT NULL,
`eyes` varchar(100) NOT NULL,
`head` varchar(100) NOT NULL,
`weapon` varchar(100) NOT NULL,
PRIMARY KEY (`kodaid`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8
CREATE TABLE `otherdeed_index` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_otherdeed` int(11) NOT NULL,
`plot` int(11) NOT NULL,
`image` varchar(200) NOT NULL,
`category` varchar(20) NOT NULL,
`sediment` varchar(40) NOT NULL,
`sediment_tier` tinyint(1) NOT NULL,
`environment` varchar(50) NOT NULL,
`environment_tier` tinyint(1) NOT NULL,
`artifact_name` varchar(80) NOT NULL,
`r_east` varchar(20) NOT NULL,
`r_west` varchar(20) NOT NULL,
`r_south` varchar(20) NOT NULL,
`r_north` varchar(20) NOT NULL,
`r_east_tier` tinyint(1) NOT NULL,
`r_west_tier` tinyint(1) NOT NULL,
`r_south_tier` tinyint(1) NOT NULL,
`r_north_tier` tinyint(1) NOT NULL,
`koda_id` int(6) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=115001 DEFAULT CHARSET=utf8
CREATE TABLE `otherdeed_opensea_listings` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`eventid` bigint(20) NOT NULL,
`event_type` varchar(10) NOT NULL,
`auction_type` varchar(10) NOT NULL,
`otherdeed_id` int(9) NOT NULL,
`timestamp_created` int(11) NOT NULL,
`timestamp_start` int(11) NOT NULL,
`timestamp_end` int(11) NOT NULL,
`duration_active` int(11) NOT NULL,
`listingprice` float NOT NULL,
`listing_currency` varchar(20) NOT NULL,
`status` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=418927 DEFAULT CHARSET=utf8
我的 sql 查詢的輸出：（解釋選擇）
uj5u.com熱心網友回復：
你肯定需要一些索引。嘗試添加以下內容：
ALTER TABLE otherdeed_opensea_listings
ADD INDEX timestamp_start (timestamp_start),
ADD INDEX otherdeed_id (otherdeed_id);
ALTER TABLE otherdeed_index
ADD INDEX id_otherdeed (id_otherdeed),
ADD INDEX koda_id (koda_id);
ALTER TABLE kodas
ADD INDEX kodaname (kodaname);
這可能會給你更快的結果。這是一個帶有 explain 的小提琴測驗。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/484588.html