Can anyone help please?
Query taking 18 seconds!?!
Brief:
I'm searching for profiles within x mile radius of the logged in user.
users : 15,000
profiles: 24,000
postcode distances: 860,000
TEST ENVIRONMENT
Running:
XP
Apache 1.3.24
MySQL 4.04
PHP 4.2
To be ported to remote unix server later
TABLE postcode_matrix
FROMPCODE varchar(4)
TOPCODE varchar(4)
MILES decimal(3,2) RANGE 0->100
KEY FROMPCODE (FROMPCODE),
KEY TOPCODE (TOPCODE),
rows: 860,000
TABLE users (
USERID int(15) NOT NULL auto_increment,
PROFILEID int(10) NOT NULL default '0',
SEX varchar(15) NOT NULL default '0',
USERNAME varchar(35) NOT NULL default '',
PASSWORD varchar(35) NOT NULL default '',
EMAIL varchar(255) NOT NULL default '',
EMAILTYPE smallint(6) NOT NULL default '1',
PCODE1 varchar(4) NOT NULL default '0',
PCODE2 varchar(4) NOT NULL default '0',
CONFIRMHASH varchar(10) NOT NULL default '',
CONFIRMED tinyint(4) NOT NULL default '0',
STARTDATE datetime NOT NULL default '0000-00-00 00:00:00',
LASTACCESS datetime NOT NULL default '0000-00-00 00:00:00',
INFO tinyint(2) NOT NULL default '1',
LOGINCOUNT int(10) NOT NULL default '0',
ACTIVE smallint(4) NOT NULL default '0',
PRIMARY KEY (USERID),
KEY IBILLID (IBILLID),
KEY PROFILEID (PROFILEID),
KEY ACTIVE (ACTIVE)
)
rows: 24,000
TABLE profiles (
UID int(11) NOT NULL auto_increment,
USERID mediumint(8) NOT NULL default '0',
USERNAME varchar(35) NOT NULL default '0',
P_EMAIL varchar(255) NOT NULL default '0',
P_DOB date NOT NULL default '0000-00-00',
P_SEX varchar(20) NOT NULL default '0',
P_HEIGHT decimal(3,2) NOT NULL default '0.00',
P_WEIGHT decimal(3,2) NOT NULL default '0.00',
P_BUILD varchar(20) NOT NULL default '',
P_EYECOL varchar(10) NOT NULL default '0',
P_MARITAL varchar(20) NOT NULL default '0',
P_COUNTY varchar(35) NOT NULL default '0',
P_COUNTRY varchar(35) NOT NULL default '0',
P_POST1 varchar(4) default NULL,
P_POST2 varchar(4) default NULL,
VALIDPCODE tinyint(2) NOT NULL default '0',
P_ICQ int(20) default NULL,
P_WEBSITE varchar(255) default NULL,
P_SMOKER varchar(4) NOT NULL default '0',
P_DRINKER varchar(4) NOT NULL default '0',
A_IMAGE tinyint(4) NOT NULL default '0',
CREATED datetime NOT NULL default '0000-00-00 00:00:00',
INFO varchar(4) NOT NULL default '0',
INACTIVEWHY mediumtext NOT NULL,
ACTIVE int(4) NOT NULL default '0',
PRIMARY KEY (UID),
KEY ACTIVE (ACTIVE),
KEY P_COUNTY (P_COUNTY),
KEY P_SEX (P_SEX),
KEY USERNAME (USERNAME),
KEY P_POST1 (P_POST1)
) TYPE=MyISAM;
rows: 15,000
MY QUERY:
SELECT DISTINCT profiles.UID, profiles.USERNAME, profiles.P_SEX, profiles.P_POST1,profiles.VALIDPCODE, postcode_matrix2.MILES, CURDATE(), (YEAR(CURDATE())-YEAR(profiles.P_DO😎)-(RIGHT(CURDATE(),5) < RIGHT(profiles.P_DOB,5)) AS AGE, P_AGEHY from profiles, users, postcode_matrix2 WHERE profiles.VALIDPCODE=1 AND profiles.A_IMAGE=1 AND (profiles.P_POST1=postcode_matrix2.TOPCODE AND postcode_matrix2.FROMPCODE='$USER_PCODE' AND postcode_matrix2.MILES <75) AND (profiles.P_SEX='Female' OR P_SEX='Couple') ORDER BY MILES ASC LIMIT 5
QUESTIONS:
Is there anything I can do to increase the speed of this query?
How much quicker might this be on a dedicated linux webserver rather than my own PC?
THANKS!
