[SOLVED] Should I hire somebody to compile MySQL 4.1 for me?
Moderator: General Moderators
[SOLVED] Should I hire somebody to compile MySQL 4.1 for me?
I have a dedicated server at a hosting company which is running Mysql 3.23.xx. I require the use of the UNION command, which 3.x doesn't provide, and the hosting company says upgrading is not their priority. I can either get-up-to-speed and compile and install 4.1 myself or hire somebody to do it.
Given that I'm a Linux newb, should I try to do it myself? Or are there lots of pitfalls ahead?
Given that I'm a Linux newb, should I try to do it myself? Or are there lots of pitfalls ahead?
Last edited by voltrader on Tue Feb 01, 2005 2:32 pm, edited 1 time in total.
Of course. Here's the query. I would be great if I didn't have to compile!
I finally figured out what I needed to do, which worked fine on my shared-server, but when I moved it over to my dedicated server I discovered it was running 3.xx.
Argh.
Code: Select all
(SELECT search_detail.*,
MATCH (search_index.name,search_index.type,search_index.cat_1) AGAINST
('$keywords') AS score,
MATCH (search_index.address_1,search_index.address_2,search_index.city,search_index.state,search_index.zip,search_index.country) AGAINST
('$xtra_keyword') AS loc_score
FROM search_index, search_detail WHERE MATCH (search_index.name,search_index.type,search_index.cat_1) AGAINST
('$keywords')
AND (search_index.ref_number=search_detail.ref_number))
UNION
(SELECT search_detail.*,
MATCH (search_index.name,search_index.type,search_index.cat_1) AGAINST
('$keywords') AS score,
MATCH (search_index.address_1,search_index.address_2,search_index.city,search_index.state,search_index.zip,search_index.country) AGAINST ('$xtra_keyword') AS loc_score
FROM search_index, search_detail
WHERE MATCH (search_index.address_1,search_index.address_2,search_index.city,search_index.state,search_index.zip,search_index.country) AGAINST
('$xtra_keyword')
AND (search_index.ref_number=search_detail.ref_number))
ORDER BY score+loc_score DESCI finally figured out what I needed to do, which worked fine on my shared-server, but when I moved it over to my dedicated server I discovered it was running 3.xx.
Argh.
Forgot to add the explanation, although I'm sure you can see from the code.
In any case, what I have are two search terms, one a location, the other a name.
I created an index for the look-up.
I use fulltext search on the index in each SELECT to return relevance scores of results, given AS score and AS loc_score.
I then use UNION to merge the two SELECTs and then order the remaining results by total score, i.e. score+loc_score.
In any case, what I have are two search terms, one a location, the other a name.
I created an index for the look-up.
I use fulltext search on the index in each SELECT to return relevance scores of results, given AS score and AS loc_score.
I then use UNION to merge the two SELECTs and then order the remaining results by total score, i.e. score+loc_score.
I used to use PHP to remove the dupes, but I'm trying to be more efficient, as the table is getting larger.
I figured that fulltext would be the way to go...
I was reading about emulating UNIONs with a dummy table, but I haven't read-up on JOINs. I might have to sleep on that article and let it sink in, as it didn't seem to be directly applicable...
I figured that fulltext would be the way to go...
I was reading about emulating UNIONs with a dummy table, but I haven't read-up on JOINs. I might have to sleep on that article and let it sink in, as it didn't seem to be directly applicable...
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
true, I forgot about using temporary tables.
it also appears that the queries could be combined, now that I look at the query in more detail..
I think it'd just involve adding an OR and moving the where clause of the second up to the first.. like so:Since the selection appears to be exactly the same...
it also appears that the queries could be combined, now that I look at the query in more detail..
I think it'd just involve adding an OR and moving the where clause of the second up to the first.. like so:
Code: Select all
SELECT
search_detail.*,
MATCH (search_index.name,search_index.type,search_index.cat_1) AGAINST ('$keywords') AS score,
MATCH (search_index.address_1,search_index.address_2,search_index.city,search_index.state,search_index.zip,search_index.country) AGAINST ('$xtra_keyword') AS loc_score
FROM
search_index, search_detail
WHERE
(MATCH (search_index.name,search_index.type,search_index.cat_1) AGAINST ('$keywords')
OR
MATCH (search_index.address_1,search_index.address_2,search_index.city,search_index.state,search_index.zip,search_index.country) AGAINST ('$xtra_keyword'))
AND (search_index.ref_number=search_detail.ref_number)
ORDER BY
score+loc_score DESCI tried:
But the ORDER BY doesn't seem to like tot_score from the SELECT.
What I don't see is why it doesn't mind ORDER BY score
or ORDER BY loc_score
but it hates score+loc_score?
Code: Select all
SELECT
search_detail.*,
MATCH (search_index.name,search_index.type,search_index.cat_1) AGAINST ('$keywords') AS score,
MATCH (search_index.address_1,search_index.address_2,search_index.city,search_index.state,search_index.zip,search_index.country) AGAINST ('$xtra_keyword') AS loc_score, tot_score=score+loc_score
FROM
search_index, search_detail
WHERE
(MATCH (search_index.name,search_index.type,search_index.cat_1) AGAINST ('$keywords')
OR
MATCH (search_index.address_1,search_index.address_2,search_index.city,search_index.state,search_index.zip,search_index.country) AGAINST ('$xtra_keyword'))
AND (search_index.ref_number=search_detail.ref_number)
ORDER BY
tot_score DESCWhat I don't see is why it doesn't mind ORDER BY score
or ORDER BY loc_score
but it hates score+loc_score?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
I was thinking more like
Code: Select all
SELECT
search_detail.*,
((MATCH (search_index.name,search_index.type,search_index.cat_1) AGAINST ('$keywords')) +
(MATCH (search_index.address_1,search_index.address_2,search_index.city,search_index.state,search_index.zip,search_index.country) AGAINST ('$xtra_keyword'))) AS tot_score
FROM
search_index, search_detail
WHERE
(MATCH (search_index.name,search_index.type,search_index.cat_1) AGAINST ('$keywords')
OR
MATCH (search_index.address_1,search_index.address_2,search_index.city,search_index.state,search_index.zip,search_index.country) AGAINST ('$xtra_keyword'))
AND (search_index.ref_number=search_detail.ref_number)
ORDER BY
tot_score DESC