I added BBCode code tags to your 2nd post to make it readable.
The query formatted for readability:
SELECT
*, pictures.id AS picture_id,
aircraft.id AS aircraft_id,
aircraft.verified AS aircraft_verified,
aircraft.type AS aircraft_type,
operators.verified AS operator_verified,
airports.verified AS location_verified,
displayteams.verified AS displayteam_verified,
airshows.verified AS airshow_verified,
picture_categories.verified AS picturecategory_verified,
CONCAT(aircraft.type, " ", aircraft.model) AS aircraft_type_search
FROM `pictures`
LEFT JOIN `aircraft` ON `aircraft`.`id` = `pictures`.`aircraft_id`
LEFT JOIN `operators` ON `operators`.`id` = `pictures`.`operator_id`
LEFT JOIN `airports` ON `airports`.`id` = `pictures`.`location_id`
LEFT JOIN `operator_category` ON `operator_category`.`id` = `pictures`.`operator_category_id`
LEFT JOIN `displayteams` ON `displayteams`.`id` = `pictures`.`displayteam_id`
LEFT JOIN `airshows` ON `airshows`.`id` = `pictures`.`airshow_id`
LEFT JOIN `picture_categories` ON `picture_categories`.`id` = `pictures`.`picture_category_id`
WHERE `pictures`.`display_picture` = 'yes'
ORDER BY `date_taken_formatted` ASC
LIMIT 30;
SELECT * is a bad idea.
Left Joins don't optimise well, usually the engine has to do a table scan on the left-sided table.
The joins look to be following a straightforward data model. Why not inner joins?
For this query, each table should have an index on the column used for it in its Join.
The `display_picture` column is yes/no? That's uselessly minimal selectivity on 225k rows.
If these fixes don't make performance adequate, let's see SHOW CREATE TABLE results for the referenced tables, and the relevant my.cnf settings.