How can I make a complex/long running SQL query more efficient?-CodePudding

I have created a query that retrieves data from multiple tables. When I run it, it takes ~200 seconds to execute. I am now trying to make the query more efficient, but I am reaching my limits and cannot find a solution. Do you maybe have an idea how I could improve the following query?

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;

The query should deliver a list of the latest listing prices of the otherdeed_opensea_listings table (16k entries) and connect it with the tables otherdeed_index (100k entries) and kodas (10k entries) to get some relevant data to analyze.

I grouped the otherdeed_opensea_listings table to get only one result for each otherdeedid, moreover it was also relevant to get only the latest results with the highest timestamp_start value.

Below you can see my sql structure:

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

The output of my sql query: (explain select)

CodePudding user response：

You definitely need some indexes. Try adding the following:

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);

That would probably give you quicker results. Here's a fiddle test with explain.