Hi all,
I wonder if anyone could help me speed up the following query (it works fine
and produces the result I want but it's very slow...)
Here is a typical query (will be executed quite often on a reasonably busy
system (about 50% idle doing other stuff that is not using the below
tables..)):
select count(*) as num,publicinfo.*,if
(local_classes.cat='hot','checked','') as checked,local_classes.* from
publicinfo, local_classes where local_classes.class_code =
publicinfo.class_code and x>507000 and x<547000 and y>164500 and y<204500
group by cat;
This is running very slowly on Redhat 7.2, MySQL 3.23 binary install, a twin
1GHz Xeon compaq ML530, 1GB Ram, SCSI disk etc... (it's not swapping and
the disk servicetime is OK)
Can anyone come up with a touch of magic to make this query more efficient?
(table definitions below...)
There are 2 tables:
------------------------------------------------
1. 'publicinfo' (Just under 800,000 records):
CREATE TABLE publicinfo (
id mediumint(9) NOT NULL auto_increment,
seq int(11) NOT NULL default '0',
name varchar(50) NOT NULL default '',
pc varchar(10) NOT NULL default '',
host varchar(120) NOT NULL default '',
path varchar(120) NOT NULL default '',
url varchar(120) NOT NULL default '',
class_code
enum('h','rp','rs','mv','www','pub','gc','res','cin','ind','hot','des','loc'
,'tube','cam') default NULL,
x mediumint(9) NOT NULL default '0',
y mediumint(9) NOT NULL default '0',
supplier varchar(10) NOT NULL default 'mm',
PRIMARY KEY (id),
KEY cat_idx (class_code),
KEY geo_idx (x,y)
) TYPE=MyISAM;
2. 'local_classes' (about 1000 records)
CREATE TABLE local_classes (
class_code varchar(40) NOT NULL default '',
name varchar(30) NOT NULL default '',
cat varchar(20) NOT NULL default '',
label varchar(25) NOT NULL default '',
KEY class_idx (class_code),
KEY cat_idx (cat),
KEY idx1 (class_code,cat)
) TYPE=MyISAM;
-------------------------------------------------
Thanks to all who try....
Kindest Regards,
Andrew
Sql, query
Andrew Braithwaite
Implementation Manager
multimap.com
e: [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
