I see what you mean now, even 400 results would take a long time doing
like matching on this many text fields without fulltext indexing. In
this case, you want to investigate the use of PHP's serialize() and
unserialize() functions. Check out this article on Zend Developer Zone
for information:
http://devzone.zend.com/node/view/id/1258
If your requirement is user-specific then you'll need to determine how
you want to accomplish that but that shouldn't be too difficult. The
above page uses MySQL as it's example but since you're doing the caching
via PHP then it shouldn't be an issue. HTH. Cheers.
Armando
.
Harpreet wrote:
SQL looks as below:
select top 400 * from na_item_view_new where ((story_name like
'%Midlothian%' ) or (title like '%Midlothian%' ) or (script like
'%Midlothian%' ) or (item_text like '%Midlothian%' ) or (keyword like
'%Midlothian%' ) or (notes like '%Midlothian%' ) or (description like
'%Midlothian%' ) or (clipname like '%Midlothian%' ) or (creator like
'%Midlothian%' )) and Convert(datetime,rundown_date) >= '05/02/2006' and
Convert(datetime,rundown_date) <= '02/02/2007' and type=1 and
content_status=1 order by convert(datetime, rundown_date) asc
View is as below:
CREATE view na_item_view_new as
select s.story_id,
u.title as title,
s.title as story_name,
u.state,
[rundown_date]=convert(char(10),u.air_date,101),
'' as video,
'' as cg_text,
SUBSTRING(s.text, 1, 500) AS script,
SUBSTRING(i.text, 1, 500) as item_text ,
i.type,
i.content_status,
k.keyword,
i.editorial_description as description ,
d.description as notes,
s.editor as creator,
i.original_material_id as clipname,
i.ar_material_id as material_id
from
(select null as state,null as type, rundown_id, ncs_rundown_id,
edit_duration, title,
[air_date]=convert(char(10),air_date, 101),
edit_start_time=substring(convert(varchar(10),
edit_start_time, 114),1,8) from na_rundown_tbl where rundown_id not in
(select ref1 from req_state_tbl where type=(select type_id from
req_type_tbl where type='NA_ST_RUNDOWN'))
union
select state, type, rundown_id, ncs_rundown_id, edit_duration, title,
[air_date]=convert(char(10),air_date, 101),
edit_start_time=substring(convert(varchar(10),
edit_start_time, 114),1,8) from na_rundown_tbl r left outer join
req_state_tbl s on r.rundown_id=s.ref1
where (state=5 AND type=(select type_id from req_type_tbl where
type='NA_ST_RUNDOWN'))) u
inner join na_story_tbl s on s.rundown_id=u.rundown_id
left outer join na_item_tbl i on s.story_id=i.story_id
left outer join na_itemkeyword_tbl k on i.item_id=k.item_id
left outer join na_itemdesc_tbl d on i.item_id=d.item_id
Regards,
Harpreet Kaur
Crispin Corporation
(919) 367-7911
(919) 791-9886 (Cell)
-----Original Message-----
From: Gustav Wiberg [mailto:gustav@xxxxxx]
Sent: Friday, February 02, 2007 1:47 AM
To: Harpreet; 'php windows'
Subject: RE: Search results
Hi!
I agree to Armando about the optimasation about the search-query. If you
show some bit of code (it may be useful only with the actual query used
to search), we maybe can help you!
Best regards
/Gustav Wiberg
HMN Konsult (Sweden)
http://www.hmn.se/
-----Original Message-----
From: Harpreet [mailto:harpreet@xxxxxxxxxxxxxxx]
Sent: Thursday, February 01, 2007 10:22 PM
To: php windows
Subject: Search results
I have a search page on my web application that searches a database with
1000's of rows. The search results are returned in like 45-50 seconds.
If the user goes to another page and then returns to the same page again
it takes the same amount of time.
Is there a way to use session variables to remember the search results
and if user comes back to the page with the same criteria it shows the
static results and not take as long.
Help is needed urgent. Thanks
Regards,
Harpreet Kaur
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
