List:General Discussion« Previous MessageNext Message »
From:Krishna Chandra Prajapati Date:May 7 2008 5:15am
Subject:Re: Query/Key Optimization
View as plain text  
Hi
The query is not optimized as it is scanning 45048 rows.
Vertical partitioning can be used because there is a lot of column in single
table.

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 ;
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>


-- 
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 500003
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: prajapatikc@stripped

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