Re: select query in from clause creates problem
Thanks for fast reply,
Should i make btree or fulltext index on varchar columns ?
we have index on zip column , but i think NOT IN claues created problem and SELECT-SUBQUERY creates probelm.
Sorry i am not getting your sentence .. " surround them with [ code ] and [ / code ] "
Explain plan takes so much time to execute so i m not able to take it,I am sending you other information, might it is very large data information.
-- Show create table for property_ptnf
CREATE TABLE `property_ptnf` (
`TERABITZ_ID` bigint(20) unsigned NOT NULL auto_increment,
`ID` varchar(30) default NULL,
`DATASOURCE` varchar(50) NOT NULL,
`UNIT_NUMBER` varchar(10) default NULL,
`ADDRESS` varchar(60) default NULL,
`CITY` varchar(40) default NULL,
`STATE` varchar(2) default NULL,
`ZIP` varchar(10) default NULL,
`COUNTY` varchar(30) default NULL,
`latitude` double NOT NULL default '0',
`longitude` double NOT NULL default '0',
`TYPE` varchar(100) default NULL,
`SUBTYPE` varchar(150) default NULL,
`TITLE` varchar(100) default NULL,
`DESCRIPTION` text,
`PARCEL_NUMBER` varchar(50) default NULL,
`BEDS` float default NULL,
`BATHS` float default NULL,
`FULLBATHS` float default NULL,
`HALFBATHS` float default NULL,
`AREASQFT` varchar(45) default NULL,
`LOTSQFT` varchar(45) default NULL,
`YEAR_BUILT` varchar(4) default NULL,
`DISPLAY_ADDRESS` tinyint(4) default NULL,
`DISPLAY_LISTING` tinyint(4) default NULL,
`MLS_NUMBER` varchar(45) default NULL,
`STATUS` varchar(30) default NULL,
`SALEDATE` date default NULL,
`SALEPRICE` double default NULL,
`LISTDATE` date default NULL,
`ORIG_LISTPRICE` double default NULL,
`CURR_LISTPRICE` double default NULL,
`DAYS_ON_MARKET` int(10) unsigned default NULL,
`DATE_PRICE_ADJUST` date default NULL,
`LISTING_URL` text,
`VTOUR_URL` text,
`MODIF_TIMESTAMP` datetime default NULL,
`EXPIRY_DATE` date default NULL,
`MISC` text,
`PHOTO_COUNT` int(10) unsigned default NULL,
`VIDEO_COUNT` int(10) unsigned default NULL,
`PHOTO_MODIF_DATE` datetime default NULL,
`VIDEO_MODIF_DATE` datetime default NULL,
`DIST_SCHOOL` varchar(60) default NULL,
`ELEM_SCHOOL` varchar(60) default NULL,
`MIDL_SCHOOL` varchar(60) default NULL,
`HIGH_SCHOOL` varchar(60) default NULL,
`NABRHD_NAME` varchar(150) default NULL,
`NABRHD_DESC` text,
`NEARBY_URL` text,
`IDENTIFIER` varchar(255) default NULL,
`POSTINGUSER_ID` bigint(20) unsigned default NULL,
PRIMARY KEY (`TERABITZ_ID`),
KEY `idx_zip` (`ZIP`(5)),
KEY `idx_city` USING BTREE (`CITY`(10)),
KEY `idx_propertyid` (`ID`),
KEY `idx_mls` (`MLS_NUMBER`),
KEY `idx_lat` (`latitude`),
KEY `idx_long` (`longitude`),
KEY `idx_listdate` (`LISTDATE`),
KEY `idx_curr_listprice` (`CURR_LISTPRICE`)
) ENGINE=MyISAM AUTO_INCREMENT=106159917486 DEFAULT CHARSET=latin1
-- Show create table for features_ptnf
CREATE TABLE `features_ptnf` (
`ID` bigint(20) NOT NULL auto_increment,
`TERABITZ_ID` bigint(20) NOT NULL,
`APPLIANCE` text,
`ARCHITECTURESTYLE` text,
`ATTIC` text,
`BARBECUEAREA` text,
`BASEMENT` text,
`BUILDINGUNITCOUNT` text,
`CABLEREADY` text,
`CELLINGFAN` text,
`CONDOFLOORNUMBER` text,
`COOLINGSYSTEM` text,
`DECK` text,
`DISABLEDACCESS` text,
`DOCK` text,
`DOORMAN` text,
`DOUBLEPANELWINDOWS` text,
`ELEVATOR` text,
`EXTERIORTYPE` text,
`FENCING` text,
`FIREPLACE` text,
`FLOORCOVERING` text,
`GARDEN` text,
`GATEDENTRY` text,
`GREENHOUSE` text,
`HEATINGFUEL` text,
`HEATINGSYSTEM` text,
`HORSEPROPERTY` text,
`HOTTUBSPA` text,
`INTERCOM` text,
`INTERIOR` text,
`JETTEDBATHTUB` text,
`LAWN` text,
`LAUNDARY` text,
`LEGALDESCRIPTION` text,
`MOTHERINLAW` text,
`NEWCONTRUCTION` text,
`NUMFLOORS` text,
`NUMPARKINGSPACES` text,
`PARKINGTYPE` text,
`PATIO` text,
`POND` text,
`POOL` text,
`PORCH` text,
`ROOFTYPE` text,
`ROOMCOUNT` text,
`ROOMS` text,
`RVPARKING` text,
`SAUNA` text,
`SECURITYSYSTEM` text,
`SKYLIGHT` text,
`SPORTSCOURT` text,
`SPRINKLERSYSTEM` text,
`VAULTEDCELLING` text,
`VIEWTYPE` text,
`WATER` text,
`WATERFRONT` text,
`WETBAR` text,
`WIRED` text,
`YEARREMODELED` text,
`ZONING` text,
`TAXYEAR` text,
`TAXAMOUNT` text,
`ADDL_FEATURES` text,
PRIMARY KEY (`ID`),
KEY `idx_teraid` (`TERABITZ_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=407847 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
mysql> show table status like 'property_ptnf'\G;
*************************** 1. row ***************************
Name: property_ptnf
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 407846
Avg_row_length: 829
Data_length: 338356076
Max_data_length: 281474976710655
Index_length: 34375680
Data_free: 0
Auto_increment: 106159917486
Create_time: 2010-04-06 10:55:11
Update_time: 2010-04-06 10:57:14
Check_time: 2010-04-06 10:57:41
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> show table status like 'features_ptnf'\G;
*************************** 1. row ***************************
Name: features_ptnf
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 407846
Avg_row_length: 1968
Data_length: 802874104
Max_data_length: 281474976710655
Index_length: 11714560
Data_free: 0
Auto_increment: 407847
Create_time: 2010-04-06 10:47:58
Update_time: 2010-04-06 10:52:53
Check_time: 2010-04-06 10:53:19
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: row_format=DYNAMIC
Comment:
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------+------------+
| Variable_name | Value |
+-------------------------------+------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 16777216 |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 1073741824 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 1048576 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2093056 |
| read_rnd_buffer_size | 5238784 |
| sort_buffer_size | 5242872 |
+-------------------------------+------------+
Thanks For Help.