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
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
:gl
:meta
: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.