4bb774de244da2d6e7f39a189b905077

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 | |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+----------------------------------------------+ 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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 !

Your refactoring





Format Copy from initial code

or Cancel