Mysql – Slow query with sub SELECTs

MySQLperformancesubquery

Unsure how to optimize this query but it and about 15 variations of it need to run for a dashboard page. Any and all suggestions are welcomed! Thanks.

Time: 0.466s

Time without sub-selects: 0.058s

SELECT  
    gallery.go_id AS id,
    (SELECT meta.value FROM meta WHERE meta.model = "Gallery_other" and meta.model_id = gallery.go_id and meta.key = "cache_sort_title_text" LIMIT 1) AS `sort_cache_sort_title_text`, 
    (SELECT CAST(meta.value AS SIGNED) FROM meta WHERE meta.model = "Gallery_other" and meta.model_id = gallery.go_id and meta.key = "cache_sort_title_int" LIMIT 1) AS `sort_cache_sort_title_int` 
FROM  
    arGalleryOther gallery  
WHERE  
    gallery.go_visible = 1  
    AND  
    gallery.go_added <= "2017-11-09 17:55:46" 
    AND ( 
        gallery.go_maingal = 9  
        OR  
        FIND_IN_SET(9, gallery.go_othergal) 
    ) 
ORDER BY 
    gallery.go_added DESC, 
    sort_cache_sort_title_text ASC, 
    sort_cache_sort_title_int ASC 
LIMIT 
    4 

MySQL Explain

CREATE TABLE `arGalleryOther` (
  `go_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `go_code` varchar(10) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `pr_id` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `go_name` varchar(75) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `go_name_noint` varchar(75) CHARACTER SET latin1 DEFAULT NULL,
  `go_name_int` int(5) unsigned DEFAULT NULL,
  `go_maingal` int(10) unsigned NOT NULL DEFAULT '0',
  `go_othergal` text CHARACTER SET latin1 NOT NULL,
  `go_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `go_visible` tinyint(1) NOT NULL DEFAULT '1',
  `go_horiz` varchar(15) CHARACTER SET latin1 NOT NULL DEFAULT 'h',
  `go_vert` varchar(15) CHARACTER SET latin1 NOT NULL DEFAULT '*',
  `go_override` tinyint(1) NOT NULL DEFAULT '0',
  `go_thumb` varchar(25) CHARACTER SET latin1 DEFAULT NULL,
  `go_keywords` text CHARACTER SET latin1 NOT NULL,
  `go_html_page` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `go_phototype` enum('H','V') CHARACTER SET latin1 NOT NULL DEFAULT 'H',
  `go_notes` text CHARACTER SET latin1,
  `go_nudity` enum('YES','NO') CHARACTER SET latin1 NOT NULL DEFAULT 'NO',
  PRIMARY KEY (`go_id`),
  KEY `idx_visible` (`go_visible`),
  KEY `idx_added` (`go_added`),
  KEY `idx_maingal` (`go_maingal`),
  FULLTEXT KEY `idx_othergal` (`go_othergal`)
) ENGINE=MyISAM AUTO_INCREMENT=4527 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

CREATE TABLE `meta` (
  `meta_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `model` varchar(50) NOT NULL,
  `model_id` int(11) unsigned NOT NULL,
  `key` varchar(50) NOT NULL,
  `value` text,
  `serialized` enum('false','true') NOT NULL DEFAULT 'false',
  `type` varchar(20) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`meta_id`),
  KEY `idx_model` (`model_id`,`model`) USING BTREE,
  KEY `idx_key` (`key`) USING BTREE,
  KEY `idx_model_id` (`model_id`),
  KEY `idx_model_key` (`model`,`key`,`value`(255)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=368839 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

Best Answer

Critique of indexes:

go:

  KEY `idx_visible` (`go_visible`),  -- DROP; indexing flags is rarely useful
  KEY `idx_maingal` (`go_visible`, go_added),   -- for WHERE 

gl:

INDEX(visible, added)  -- in this order

meta:

  PRIMARY KEY (`meta_id`),  -- see below
  KEY `idx_model` (`model_id`,`model`) USING BTREE,
  KEY `idx_model_id` (`model_id`), -- DROP since the above handles it
  KEY `idx_model_key` (`model`,`key`) USING BTREE  -- Prefix indexing useless here

For key-value, it is rarely necessary to have an AUTO_INCREMENT id (meta_id) since there is probably a combo of other columns that is unique. See my tips here.