Help: (and apologies if this is posted to the wrong list..)(pls let me know
where to post if so.. Thx ;-)
I have *inherited* an App that uses PHP / MySQL. THe internal search function
within the application
that I am supporting uses the following DB Table structure and runs
the Query below to return a set of results ordered by the REGEXP match results
(The "score").
The Query looks in each field, for a REGEXP match, if the REGEXP returns true,
the score is incremented by 10 * number of matches (I think)
I cannot use FULLTEXT indexes. What I would like to do is refactor the query
to improve the speed.
Can someone please advise as to why my queries ALWAYS use filesort, where and
temporary (as found out by EXPLAIN...)
Can someone recommend a new query to deliver the same results??
Thanks in advance
## START TABLE STRUCTURE ##
CREATE TABLE article_related_communities (
comm_id int(6) default NULL,
article_id varchar(12) default NULL,
KEY article_id (article_id),
KEY comm_id (comm_id)
) TYPE=MyISAM;
CREATE TABLE articles (
id int(4) NOT NULL auto_increment,
c_type int(4) NOT NULL default '1',
author int(6) NOT NULL default '0',
comm_id int(6) default '0',
comm_type int(6) default '0',
keywords longtext NOT NULL,
title varchar(128) NOT NULL default '',
synopsis text NOT NULL,
release_date varchar(14) default NULL,
expiry_date varchar(14) default NULL,
start_date varchar(14) NOT NULL default '',
closing_date varchar(14) NOT NULL default '',
location varchar(255) NOT NULL default '',
cost varchar(255) NOT NULL default '',
times text NOT NULL,
overnight_details text NOT NULL,
remuneration varchar(255) NOT NULL default '',
body text NOT NULL,
status int(6) default NULL,
expired int(2) NOT NULL default '0',
deleted int(2) NOT NULL default '0',
ctime varchar(14) NOT NULL default '',
mtime varchar(14) NOT NULL default '',
mod_author int(6) default '0',
event_type int(2) default '0',
cs_org int(8) NOT NULL default '0',
expiry_reason varchar(128) NOT NULL default '',
accumulated_rating int(6) NOT NULL default '0',
num_ratings int(6) NOT NULL default '0',
average_rating float NOT NULL default '0',
event_duration varchar(128) NOT NULL default '0',
event_organiser varchar(128) NOT NULL default '0',
event_organiser_email varchar(128) NOT NULL default '0',
PRIMARY KEY (id),
KEY c_type (c_type),
KEY status (status),
KEY comm_id (comm_id),
KEY author (author),
KEY expired (expired),
KEY deleted (deleted),
KEY expiry_date (expiry_date),
KEY release_date (release_date),
FULLTEXT KEY idx_tit_syn_body (title,synopsis,body)
) TYPE=MyISAM;
## END TABLE STRUCTURE ##
## START QUERY ##
SELECT distinct articles.* , ( ( (title REGEXP "[[:<:]]MArket[[:>:]]")*10+(body
REGEXP "[[:<:]]MArket[[:>:]]")*10+(synopsis REGEXP
"[[:<:]]MArket[[:>:]]")*10+(closing_date REGEXP
"[[:<:]]MArket[[:>:]]")*10+(location REGEXP "[[:<:]]MArket[[:>:]]")*10+(cost
REGEXP "[[:<:]]MArket[[:>:]]")*10+(times REGEXP
"[[:<:]]MArket[[:>:]]")*10+(overnight_details REGEXP
"[[:<:]]MArket[[:>:]]")*10+(remuneration REGEXP
"[[:<:]]MArket[[:>:]]")*10+(keywords REGEXP "[[:<:]]MArket[[:>:]]")*10 ) + (
(title REGEXP "[[:<:]]Segmentation[[:>:]]")*10+(body REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(synopsis REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(closing_date REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(location REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(cost REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(times REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(overnight_details REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(remuneration REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(keywords REGEXP
"[[:<:]]Segmentation[[:>:]]")*10 ) ) AS score FROM articles LEFT JOIN
article_related_communities on articles.id =
article_related_communities.article_id WHERE deleted=0 AND ( ( (title REGEXP
"[[:<:]]MArket[[:>:]]")*10+(body REGEXP "[[:<:]]MArket[[:>:]]")*10+(synopsis
REGEXP "[[:<:]]MArket[[:>:]]")*10+(closing_date REGEXP
"[[:<:]]MArket[[:>:]]")*10+(location REGEXP "[[:<:]]MArket[[:>:]]")*10+(cost
REGEXP "[[:<:]]MArket[[:>:]]")*10+(times REGEXP
"[[:<:]]MArket[[:>:]]")*10+(overnight_details REGEXP
"[[:<:]]MArket[[:>:]]")*10+(remuneration REGEXP
"[[:<:]]MArket[[:>:]]")*10+(keywords REGEXP "[[:<:]]MArket[[:>:]]")*10 ) + (
(title REGEXP "[[:<:]]Segmentation[[:>:]]")*10+(body REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(synopsis REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(closing_date REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(location REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(cost REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(times REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(overnight_details REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(remuneration REGEXP
"[[:<:]]Segmentation[[:>:]]")*10+(keywords REGEXP
"[[:<:]]Segmentation[[:>:]]")*10 ) )>0
AND expiry_date > '20041018'
AND (status='2'
OR (status='1' AND (author = 161 OR (articles.comm_id IN ('')))))
AND release_date<='20041018'
AND (articles.comm_id IN
('559','560','9','286','425','484','103','324','323','18','1','1','2','4','5')
OR article_related_communities.comm_id IN
('559','560','9','286','425','484','103','324','323','18','1','1','2','4','5'))
ORDER BY score DESC LIMIT 0,10;
## END QUERY ##
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
