MySQL Forums
Forum List  »  Performance

Re: select query in from clause creates problem
Posted by: sunil divyeshvar
Date: April 06, 2010 11:51PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: select query in from clause creates problem
1495
April 06, 2010 11:51PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.