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 <[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]
