Pastable version of optimize query

<pre class='prettyprint' language='ruby'>DDL
-------
CREATE TABLE `locations` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(255) character set latin1 NOT NULL,
  `parent_id` bigint(20) default '0',
  `zip_code` varchar(255) character set latin1 default NULL,
  `lat` float default '0',
  `lng` float default '0',
  `order` int(11) default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;

CREATE TABLE `item_location_mappings` (
  `id` bigint(20) NOT NULL auto_increment,
  `location_id` bigint(20) NOT NULL,
  `item_id` bigint(20) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `IDX_LocationID` (`location_id`),
  KEY `IDX_ItemID` (`item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=107 DEFAULT CHARSET=latin1;

CREATE TABLE `category_mappings` (
  `id` int(11) NOT NULL auto_increment,
  `item_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `IDX_CategoryID` (`category_id`),
  KEY `IDX_ItemID` (`item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=198 DEFAULT CHARSET=latin1;

my written query 
----------------------

SELECT 
        l.*, 
        count(*) as weight
FROM locations l
	JOIN item_location_mappings ilm 
		ON ilm.location_id = l.id
	JOIN category_mappings cm
		ON cm.item_id = ilm.item_id
WHERE
	cm.category_id = 45
GROUP BY l.id</pre>
<a href="http://www.refactormycode.com/codes/170-optimize-query" style="color:#fff" title="As seen on RefactorMyCode.com"><img alt="Small_logo" src="http://www.refactormycode.com/images/small_logo.gif" style="border:0" /></a>

Refactor :my => 'code'

Recent

Popular

Pastable version of optimize query