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 <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
>
>
--
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: [EMAIL PROTECTED]
