1

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
0

3 Answers 3

2

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.

0

You can move the subqueries to a single join. That ought to help.

SELECT  
    gallery.go_id AS id,
    sort_cache.value_text AS `sort_cache_sort_title_text`, 
    sort_cache.value AS `sort_cache_sort_title_int` 
FROM arGalleryOther gallery  
        LEFT JOIN 
            (
                SELECT MAX(value) value_text, 
                     MAX(CAST(value AS SIGNED)) value, 
                     model_id 
                FROM meta 
                WHERE meta.model = "Gallery_other" 
                        and meta.key = "cache_sort_title_text"
                GROUP BY model_id
            ) AS sort_cache ON sort_cache.model_id = gallery.go_id 
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 
0

I 100% appreciate you taking the time to rewrite the query for me. Yours managed to get it down to .2s. Wrapping a select * () around it and sorting by the sub-select columns outside of the primary query dropped it to .014s.

SELECT * FROM ( 
    SELECT  
        gallery.gl_id AS id,  
        (    SELECT  meta.value
            FROM  meta
            WHERE  meta.model = "Gallery"
              and  meta.model_id = gallery.gl_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"
              and  meta.model_id = gallery.gl_id
              and  meta.key = "cache_sort_title_int"
            LIMIT  1
        ) AS `sort_cache_sort_title_int` 
    FROM  
        arGalleries gallery  
    INNER JOIN   
        arGalleryPages gallery_page  
        ON  
            gallery_page.gp_id = gallery.gp_id  
    INNER JOIN  
        bgProducts product  
        ON  
            product.pr_id = gallery.pr_id  
    WHERE  
        gallery.gl_visible = 1  
        AND  
        gallery.gl_added <= "2017-11-10 00:05:35"  
        AND ( 
            gallery_page.gp_main = 7  
            OR  
            FIND_IN_SET(7, gallery_page.gp_other) 
        )        
ORDER BY 
    gallery.gl_added DESC, 
    gallery.mt_id ASC 
LIMIT 
    6) AS temp 
ORDER BY 
    sort_cache_sort_title_text ASC, 
    sort_cache_sort_title_int ASC 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.