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]
