Re: No caching - or brutal performance - for repeat queries 4.1.8nt

[Ed Tilley]

Well I've tried running under it under 4.0.23 now (believing there might be a 4.1 & Windows 2000 problem) and I've added the server name to hosts and DNS - but I'm still getting no caching - and rediculous (read 5 second +) query response.

Here's what I really don't get. If i run the query from inside the firewall, its fast, fast, fast - If I run outside, the CPU pegs at 100% for up to 5 seconds.

Here is more info and my query ... hope someone out there has a good suggestion...

Ed

My query...

select l.RealtorID1,RealtorID2, Name, Photo, r.Website, r.Email, r.Phone, CompanyID1,CompanyName, CompanyLogo, CompanyID2, c.Phone, c.Website, c.Email, MLSID, l.Description, l.LongDesc, l.Address, Neighbourhood, Town, City, Price,Detail,PictureURL,Type,Category,SubCategory,HouseStyle,Age,Bathrooms,Bedrooms, Fireplace,Air,Pool,Waterfront,Workshop,VirtualTour,TourGraphic from Listings as l, Realtors as r, Company as c where l.CompanyID1 = c.CompanyID and l.RealtorID1 = r.RealtorID and Price >= 425000 and Price <= 500000 and ( City like "%Oakville%" ) and ( Type like "%Single Family%" )

my.ini - note that my server and development environment are behind a firewall. I am packet forwarding port 80 requests through the firewall.

[client]
port=3306
[mysqld]
basedir=Y:/MySQL/
#bind-address=192.168.1.101
datadir=Y:/MySQL/Data
#slow query log#=
#tmpdir#=
port=3306
default-character-set=latin1

max_connections=100
query_cache_type=1
query_cache_size=64M
#buffer_size=64M
table_cache=256
tmp_table_size=8M
thread_cache=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=64M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=212K

#skip innodb

#default-storage-engine=INNODB
#innodb_data_file_path=ibdata1:10M:autoextend
#innodb_additional_mem_pool_size=2M
#innodb_flush_log_at_trx_commit=1
#innodb_log_buffer_size=1M
#innodb_buffer_pool_size=64M
#innodb_log_file_size=10M
#innodb_thread_concurrency=8
#innodb_data_home_dir=Y:/Program Files/MySQL/MySQL Server 4.1/Data

[WinMySQLadmin]

Server=Y:/mysql/bin/mysqld-nt.exe



Database: realestate Table: listings Rows: 2610
+---------------+--------------+------+-----+---------+-------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+---------------+--------------+------+-----+---------+-------+---------------------------------+
| CompanyID1 | int(11) | | | 0 | | select,insert,update,references |
| CompanyID2 | int(11) | | | 0 | | select,insert,update,references |
| RealtorID1 | int(11) | | | 0 | | select,insert,update,references |
| RealtorID2 | int(11) | | | 0 | | select,insert,update,references |
| Description | blob | | | | | select,insert,update,references |
| Confirmed | tinyint(4) | | | 0 | | select,insert,update,references |
| Town | varchar(15) | | | | | select,insert,update,references |
| City | varchar(22) | | | | | select,insert,update,references |
| Price | int(11) | | | 0 | | select,insert,update,references |
| PictureURL | varchar(120) | | | | | select,insert,update,references |
| Type | varchar(32) | | | | | select,insert,update,references |
| Category | varchar(32) | | | | | select,insert,update,references |
| SubCategory | varchar(32) | | | | | select,insert,update,references |
| HouseStyle | varchar(32) | | | | | select,insert,update,references |
| Age | varchar(4) | | | | | select,insert,update,references |
| Bathrooms | float(3,1) | | | 0.0 | | select,insert,update,references |
| Bedrooms | float(3,1) | | | 0.0 | | select,insert,update,references |
| Fireplace | char(3) | | | | | select,insert,update,references |
| Air | char(3) | | | | | select,insert,update,references |
| Pool | char(3) | | | | | select,insert,update,references |
| Waterfront | char(3) | | | | | select,insert,update,references |
| Workshop | char(3) | | | | | select,insert,update,references |
| VirtualTour | varchar(240) | | | | | select,insert,update,references |
| TourGraphic | varchar(240) | | | | | select,insert,update,references |
| MLSID | varchar(12) | | PRI | | | select,insert,update,references |
| Detail | blob | | | | | select,insert,update,references |
| Address | varchar(80) | | | | | select,insert,update,references |
| Neighbourhood | varchar(22) | | | | | select,insert,update,references |
| LongDesc | blob | | | | | select,insert,update,references |
| CondoFee | varchar(25) | | | | | select,insert,update,references |
| Land | varchar(25) | | | | | select,insert,update,references |
| Features | blob | | | | | select,insert,update,references |
| HouseSize | varchar(25) | | | | | select,insert,update,references |
| LotSize | varchar(70) | | | | | select,insert,update,references |
| Water | varchar(25) | | | | | select,insert,update,references |
| Structures | varchar(35) | | | | | select,insert,update,references |
+---------------+--------------+------+-----+---------+-------+---------------------------------+

Database: realestate Table: company Rows: 494
+-------------+--------------+------+-----+---------+----------------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-------------+--------------+------+-----+---------+----------------+---------------------------------+
| CompanyID | int(11) | | PRI | | auto_increment | select,insert,update,references |
| CompanyName | varchar(52) | | | | | select,insert,update,references |
| CompanyLogo | varchar(120) | | | | | select,insert,update,references |
| Address | varchar(120) | | | | | select,insert,update,references |
| Phone | varchar(15) | | | | | select,insert,update,references |
| Email | varchar(120) | | | | | select,insert,update,references |
| Website | varchar(120) | | | | | select,insert,update,references |
+-------------+--------------+------+-----+---------+----------------+---------------------------------+

Database: realestate Table: realtors Rows: 2890
+-----------+--------------+------+-----+---------+----------------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-----------+--------------+------+-----+---------+----------------+---------------------------------+
| RealtorID | int(11) | | PRI | | auto_increment | select,insert,update,references |
| CompanyID | int(11) | | | 0 | | select,insert,update,references |
| Name | varchar(42) | | | | | select,insert,update,references |
| Title | varchar(42) | | | | | select,insert,update,references |
| Phone | varchar(15) | | | | | select,insert,update,references |
| Website | varchar(120) | | | | | select,insert,update,references |
| Email | varchar(120) | | | | | select,insert,update,references |
| Photo | varchar(120) | | | | | select,insert,update,references |
+-----------+--------------+------+-----+---------+----------------+---------------------------------+