Need help improving sql query performance

I have this table:

CREATE TABLE property_ads_history (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
property_ad_id int(10) unsigned NOT NULL,
advertiser_type varchar(255) COLLATE utf8_unicode_ci NOT NULL,
agency_reference_id varchar(255) COLLATE utf8_unicode_ci NOT NULL,
average_sale_price double(8,2) NOT NULL DEFAULT '-1.00',
bathrooms double(8,2) NOT NULL DEFAULT '-1.00',
bedrooms double(8,2) NOT NULL DEFAULT '-1.00',
carports double(8,2) NOT NULL DEFAULT '-1.00',
DELETE_country varchar(255) COLLATE utf8_unicode_ci NOT NULL,
created_reason enum('Geocoded','Sanitized Parking','Sanitized Representation','Sanitized Address','Scraped','URL Inserted','QA Sanitized Address','QA Sanitized Representation','QA Sanitized Parkings') COLLATE utf8_unicode_ci DEFAULT NULL,
description longtext COLLATE utf8_unicode_ci NOT NULL,
ensuite_bathrooms double(8,2) NOT NULL DEFAULT '-1.00',
DELETE_ad_expired_at datetime NOT NULL,
floor_area double(8,2) NOT NULL DEFAULT '-1.00',
formatted_address varchar(255) COLLATE utf8_unicode_ci NOT NULL,
garages double(8,2) NOT NULL DEFAULT '-1.00',
geocode_status varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
is_represented tinyint(1) DEFAULT NULL,
land_area double(8,2) NOT NULL DEFAULT '-1.00',
latitude double(10,6) NOT NULL,
location_id int(10) unsigned DEFAULT NULL,
longitude double(10,6) NOT NULL,
off_street_parkings double(8,2) NOT NULL DEFAULT '-1.00',
official_property_id varchar(255) COLLATE utf8_unicode_ci NOT NULL,
page_url varchar(255) COLLATE utf8_unicode_ci NOT NULL,
parking varchar(255) COLLATE utf8_unicode_ci NOT NULL,
posted_at datetime NOT NULL,
posted_at_string varchar(255) COLLATE utf8_unicode_ci NOT NULL,
postal_code varchar(255) COLLATE utf8_unicode_ci NOT NULL,
price double(10,2) NOT NULL DEFAULT '-1.00',
primary_image varchar(255) COLLATE utf8_unicode_ci NOT NULL,
DELETE_property_ad_created_at datetime NOT NULL,
`property_

ORDER BY in the sub query is what's making it slow.

ORDER BY makes query slow since you need to order (sort) all the objects in the query results. It is in order of N Square if you don't have an index, and N log N if you do.

And you don't even need to order by all of it.

What you need is only to filter out the minimum, so instead of:

ORDER BY  
  DATE(`t2`.`created_at`) DESC LIMIT 1

I would add another condition in the WHERE clause saying:

WHERE DATE(t2.'created at') <> (SELECT MAX (DATE ('created at') FROM 't2'))

Since it needs to look at all the dates in the query, but not necessarily to order them.

Google "Create Composite Index". MySQL is not my variant of SQL. It is always good practice to check subjects like indexes and Primary Keys outside of Q&A forums (like this one) since they are very broad and you need to read at least 5 articles in order to decide which composite index you need, on top of how to write it (but since you query that field quite often in that query, I'd suggest you should create one).

StackExchange Database Administrators Q#136799, answer score: 3