List:General Discussion« Previous MessageNext Message »
From:Michael Stearne Date:May 6 2008 7:26pm
Subject:Query/Key Optimization
View as plain text  

The main table for our site is called properties and it gets hit quite
often (several times per second) something like:

 Queries Total: 41,496         Avg/Sec: 6.89  Slow: 0
 Cache Hits   : 15,096         Avg/Sec: 2.51  Now/Sec: 0.00  Ratio: 36.38%
 Threads Total: 1         Active: 1     Cached: 76
 Key Efficiency: 94.41%  Bytes in: 114  Bytes out: 6,713

This properties table is very simple. (Pasted below) There is about
500,000 rows in the table and we are experiencing long queries like:

SELECT * FROM properties WHERE 1 =1 AND properties.Published <>0 AND
properties.Deleted <>1 AND properties.state = 'ca' AND TYPE =
'Residential' AND Image1 <> '' ORDER BY id DESC LIMIT 0 , 35

An explain on that yields:

| id | select_type | table      | type        | possible_keys
     | key             | key_len | ref  | rows  | Extra
|  1 | SIMPLE      | properties | index_merge |
Type,TypeSubType,StateIndex | Type,StateIndex | 1,67    | NULL | 45048
| Using intersect(Type,StateIndex); Using where; Using filesort |

Is there anything you can see with the table or key design that might
be causing this slowdown?  There are 5 databases: 1 master, 4 slaves
replicated.  The master is only used for INSERTs, UPDATEs and DELETEs.
 The properties table is INNODB.  Should it me MyISAM?

Thanks for any help!

CREATE TABLE properties (
  id int(11) unsigned NOT NULL auto_increment,
  UserID int(11) unsigned NOT NULL default '0',
  `Type` enum('Commercial','Residential') NOT NULL default 'Residential',
  Subtype varchar(64) NOT NULL default '0',
  Zip varchar(10) default '',
  Heading varchar(84) NOT NULL default '',
  Address1 varchar(128) NOT NULL default '',
  Address2 varchar(32) default NULL,
  Unit varchar(32) default NULL,
  Neighborhood varchar(64) default NULL,
  City varchar(64) NOT NULL default '0',
  State varchar(64) default '',
  Country varchar(4) default 'USA',
  ListingContactHTML varchar(255) default NULL,
  IsShare tinyint(1) default '0',
  IsSublet tinyint(1) default '0',
  PRIMARY KEY  (id),
  KEY `Type` (`Type`),
  KEY Subtype (Subtype),
  KEY TypeSubType (`Type`,Subtype),
  KEY CityHood (City,Neighborhood),
  KEY GoogleBase (GoogleBase),
  KEY Zip (Zip),
  KEY AddressSearch (Heading,Zip,City,Neighborhood,Address1,Unit),
  KEY StateIndex (State),
  KEY ListingContactRemoteCode (ListingContactRemoteCode),
  KEY LeaseType (LeaseType),
  KEY CreationDate (CreationDate),
  KEY LastMapLookup (LastMapLookup),
  KEY UserID (UserID),
  KEY Country (Country),
  KEY LatLon (lat,lon),
  KEY CityStateType (City,State,`Type`),
  KEY BatchUpdateRemoteListingID (BatchUpdateRemoteListingID),
  KEY CountryType (Country,`Type`),
  KEY Country_2 (Country,City,State)
Query/Key OptimizationMichael Stearne6 May
  • Re: Query/Key OptimizationMichael Stearne6 May
  • Re: Query/Key OptimizationKrishna Chandra Prajapati7 May
  • question about update/join queryAndy Wallace7 May
    • Re: question about update/join queryAndy Wallace7 May
    • Order ProblemNeil Tompkins7 May
Re: question about update/join queryAndy Wallace8 May
Re: question about update/join queryAndy Wallace8 May
Re: question about update/join queryAndy Wallace8 May
RE: Order ProblemNeil Tompkins8 May
  • Re: Order ProblemMoon's Father20 May
Re: question about update/join queryAndy Wallace8 May