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
Refactorings
No refactoring yet !
i have to generate a list of locations with the following format -
asia (100002)
bangladesh (50000)
dhaka (3000)
which is consist of the following format -
<location name> (<total number of items from the same location>)
also all of these content are limited by specific category.
hope someone will come up with some refactoring suggestion :)
by the way, although i had indexing for location_id, item_id but the explain says the following report -
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | cm | ALL | NULL | NULL | NULL | NULL | 19 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | ilm | ALL | NULL | NULL | NULL | NULL | 38 | Using where |
| 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 8 | ads_development.ilm.location_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+----------------------------------------------+