List:General Discussion« Previous MessageNext Message »
From:Michael Stearne Date:May 6 2008 7:35pm
Subject:Re: Query/Key Optimization
View as plain text  
As a note.  The query itself may not be taking long but there are many
"Sorting result " and "Copying to tmp table " in myTop.

Thanks,
Michael



On Tue, May 6, 2008 at 3:26 PM, Michael Stearne <mstearne@stripped> wrote:
> Hi.
>
>  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!
>  Michael
>
>
>  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)
>  ) ENGINE=InnoDB AUTO_INCREMENT=907758 DEFAULT CHARSET=latin1
>  AUTO_INCREMENT=907758 ;
>
Thread
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