· 8 years ago · Jul 29, 2018, 07:56 PM
1EXPLAIN SELECT L.listing_id, L.category, L.property_name, L.listing_news, L.island, L.location, L.district, L.bedrooms, L.bathrooms, L.living_area, L.price
2FROM listings AS L
3LEFT JOIN list_views AS LV ON LV.listing_id = L.listing_id
4LEFT JOIN categories AS C ON C.id = LV.view_type
5WHERE cms_status = 'complete'
6AND hide = 'no'
7AND expiry > CURDATE( )
8GROUP BY listing_id
9ORDER BY price DESC
10
11
12table type possible_keys key key_len ref rows Extra
13L ref properpublic properpublic 32 const,const 821 Using where; Using temporary; Using filesort
14LV ref listing_id listing_id 4 L.listing_id 3
15C eq_ref PRIMARY PRIMARY 4 LV.view_type 1 Using index
16
17
18CREATE TABLE IF NOT EXISTS `list_views` (
19 `id` int(11) NOT NULL AUTO_INCREMENT,
20 `listing_id` int(11) NOT NULL DEFAULT '0',
21 `view_type` int(11) NOT NULL DEFAULT '0',
22 PRIMARY KEY (`id`),
23 KEY `view_list_id` (`view_type`,`listing_id`,`id`),
24 KEY `listing_id` (`listing_id`),
25 KEY `view_type` (`view_type`)
26) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='matches view type id from cat table to listing' AUTO_INCREMENT=32729 ;
27
28
29CREATE TABLE IF NOT EXISTS `categories` (
30 `id` int(6) NOT NULL AUTO_INCREMENT,
31 `parent_id` int(2) NOT NULL DEFAULT '0',
32 `section` varchar(32) NOT NULL DEFAULT '',
33 `category` varchar(64) NOT NULL DEFAULT '',
34 `description` text NOT NULL,
35 `is_community` varchar(9) NOT NULL DEFAULT 'no',
36 PRIMARY KEY (`id`),
37 KEY `sectionTOcategory` (`section`,`category`)
38) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1040 ;
39
40
41CREATE TABLE IF NOT EXISTS `listings` (
42 `listing_id` int(11) NOT NULL AUTO_INCREMENT,
43 `legacy_id` varchar(64) NOT NULL DEFAULT '0',
44 `mls_id` varchar(16) NOT NULL,
45 `category` varchar(32) NOT NULL DEFAULT '',
46 `entered_by` int(11) NOT NULL DEFAULT '0',
47 `entered_when` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
48 `last_edit_by` int(11) NOT NULL DEFAULT '0',
49 `last_edit` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
50 `cms_status` varchar(16) NOT NULL DEFAULT 'incomplete',
51 `view_counter` int(9) NOT NULL DEFAULT '0',
52 `client_id` int(11) NOT NULL DEFAULT '0',
53 `agent_id` int(11) NOT NULL DEFAULT '0',
54 `featured_newsletter` char(3) NOT NULL,
55 `onlyfeature` varchar(5) NOT NULL DEFAULT 'no',
56 `hide` varchar(5) NOT NULL DEFAULT 'yes',
57 `exclusive` varchar(16) NOT NULL DEFAULT '',
58 `listed_with` varchar(64) NOT NULL DEFAULT '',
59 `cobroke_id` varchar(32) NOT NULL DEFAULT '',
60 `listing_news` varchar(32) NOT NULL DEFAULT '',
61 `property_name` varchar(64) NOT NULL DEFAULT '',
62 `property_name_2` varchar(32) NOT NULL DEFAULT '',
63 `price` int(11) NOT NULL DEFAULT '0',
64 `netprice` int(11) NOT NULL DEFAULT '0',
65 `rent_price_alts` varchar(32) NOT NULL DEFAULT '',
66 `display_price` int(11) NOT NULL,
67 `display_price_label` varchar(16) NOT NULL,
68 `island` varchar(32) NOT NULL DEFAULT '',
69 `location` varchar(32) NOT NULL DEFAULT '',
70 `district` varchar(32) NOT NULL DEFAULT '',
71 `streetaddr` varchar(80) NOT NULL,
72 `block` varchar(64) NOT NULL,
73 `lot` varchar(64) NOT NULL,
74 `unit` varchar(64) NOT NULL,
75 `displayblocklot` tinyint(1) NOT NULL,
76 `description` text NOT NULL,
77 `notes` text NOT NULL,
78 `client_registration` text NOT NULL,
79 `condition` varchar(32) NOT NULL DEFAULT '',
80 `list_date` date NOT NULL DEFAULT '0000-00-00',
81 `verified` date NOT NULL DEFAULT '0000-00-00',
82 `expiry` date NOT NULL DEFAULT '0000-00-00',
83 `key_with` varchar(32) NOT NULL DEFAULT '',
84 `key_number` varchar(32) NOT NULL DEFAULT '',
85 `commission` varchar(32) NOT NULL DEFAULT '',
86 `classified_include` varchar(32) NOT NULL DEFAULT '',
87 `classified_ad_caption` varchar(96) NOT NULL DEFAULT '',
88 `classified_ad_copy` text NOT NULL,
89 `rental_type` varchar(32) NOT NULL DEFAULT '',
90 `property_type` text NOT NULL,
91 `building_style` varchar(32) NOT NULL DEFAULT '',
92 `view_type` varchar(32) NOT NULL DEFAULT '',
93 `bedrooms` int(9) NOT NULL DEFAULT '0',
94 `bathrooms` decimal(3,1) NOT NULL DEFAULT '0.0',
95 `school_1` varchar(90) NOT NULL DEFAULT '',
96 `school_2` varchar(90) NOT NULL DEFAULT '',
97 `school_3` varchar(90) NOT NULL DEFAULT '',
98 `rent_contact` varchar(32) NOT NULL DEFAULT '',
99 `rent_rented` varchar(32) NOT NULL DEFAULT '',
100 `rent_tenant_name` varchar(32) NOT NULL DEFAULT '',
101 `rent_tenant_PhHo` varchar(32) NOT NULL DEFAULT '',
102 `rent_tenant_PhWk` varchar(32) NOT NULL DEFAULT '',
103 `security_deposit` varchar(32) NOT NULL DEFAULT '',
104 `phone_deposit` varchar(32) NOT NULL DEFAULT '',
105 `water_deposit` varchar(32) NOT NULL DEFAULT '',
106 `bec_deposit` varchar(32) NOT NULL DEFAULT '',
107 `RPTax` varchar(32) NOT NULL DEFAULT '',
108 `terms` varchar(32) NOT NULL DEFAULT '',
109 `property_code` varchar(32) NOT NULL DEFAULT '',
110 `available_when` date NOT NULL DEFAULT '0000-00-00',
111 `business_class` varchar(32) NOT NULL DEFAULT '',
112 `business_type` varchar(32) NOT NULL DEFAULT '',
113 `net_rentable` varchar(32) NOT NULL DEFAULT '',
114 `sq_ft_rate` varchar(32) NOT NULL DEFAULT '',
115 `roof_type` varchar(32) NOT NULL DEFAULT '',
116 `num_of_elevators` varchar(32) NOT NULL DEFAULT '',
117 `com_status` varchar(32) NOT NULL DEFAULT '',
118 `total_expenses` varchar(32) NOT NULL DEFAULT '',
119 `vacancy_rate` varchar(32) NOT NULL DEFAULT '',
120 `price_sq_ft` varchar(32) NOT NULL DEFAULT '',
121 `num_of_units` varchar(32) NOT NULL DEFAULT '',
122 `gross_rentable_area` varchar(32) NOT NULL DEFAULT '',
123 `gross_building` varchar(32) NOT NULL DEFAULT '',
124 `zoning` varchar(32) NOT NULL DEFAULT '',
125 `goi` varchar(32) NOT NULL DEFAULT '',
126 `noi` varchar(32) NOT NULL DEFAULT '',
127 `deposit` int(9) NOT NULL DEFAULT '0',
128 `lot_size` int(11) DEFAULT NULL,
129 `living_area` varchar(32) NOT NULL DEFAULT '',
130 `floors` varchar(32) NOT NULL DEFAULT '',
131 `floor_level` varchar(23) NOT NULL DEFAULT '',
132 `maint_fees` varchar(32) NOT NULL DEFAULT '0',
133 `lease_term` varchar(32) NOT NULL DEFAULT '',
134 `year_built` int(4) NOT NULL DEFAULT '0',
135 PRIMARY KEY (`listing_id`),
136 KEY `properpublic` (`cms_status`,`hide`,`expiry`),
137 KEY `island` (`island`),
138 KEY `location` (`location`),
139 KEY `district` (`district`),
140 KEY `view_type` (`view_type`),
141 KEY `category` (`category`),
142 FULLTEXT KEY `legacy_id` (`legacy_id`),
143 FULLTEXT KEY `description` (`description`)
144) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2863 ;