MySQL Forums
Forum List  »  Performance

Re: No caching - or brutal performance - for repeat queries 4.1.8nt
Posted by: Ed Tilley
Date: December 23, 2004 12:19PM

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 |
+-----------+--------------+------+-----+---------+----------------+---------------------------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: No caching - or brutal performance - for repeat queries 4.1.8nt
2029
December 23, 2004 12:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.