Page 1 of 1
[SOLVED] Should I hire somebody to compile MySQL 4.1 for me?
Posted: Mon Jan 31, 2005 11:18 pm
by voltrader
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?
Posted: Mon Jan 31, 2005 11:24 pm
by feyd
out of curiosity, what's the query? Could you also explain why you require it?
Posted: Mon Jan 31, 2005 11:29 pm
by voltrader
Of course. Here's the query. I would be great if I didn't have to compile!
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 DESC
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.
Posted: Mon Jan 31, 2005 11:33 pm
by voltrader
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.
Posted: Mon Jan 31, 2005 11:35 pm
by feyd
this all can be performed without the union, although it would require storing results into php's memory and sorting it yourself.. would that be acceptable?
Posted: Mon Jan 31, 2005 11:38 pm
by voltrader
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...
Posted: Mon Jan 31, 2005 11:45 pm
by feyd
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:
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 DESC
Since the selection appears to be exactly the same...

Posted: Mon Jan 31, 2005 11:50 pm
by voltrader
Thanks. I just tried that... Not sure why I'm getting an error:
Unknown column 'score' in 'order clause'
Posted: Mon Jan 31, 2005 11:56 pm
by feyd
it may require switching both of those out to their match texts, as I'd imagine the union will process both queries, then do the order by, at least, that's how your query looks, I think.
Posted: Mon Jan 31, 2005 11:57 pm
by voltrader
I think you've definitely solved most of the problem... I removed the ORDER BY score+loc_score and it seems to work.
I also tried ORDER BY score and it works.
Why doesn't it recognize ORDER BY score+loc_score

Posted: Mon Jan 31, 2005 11:58 pm
by feyd
there's also a potential to add them together in the selection, then order by that alias..
Posted: Tue Feb 01, 2005 12:06 am
by voltrader
I tried:
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 DESC
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?
Posted: Tue Feb 01, 2005 12:13 am
by feyd
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
Posted: Tue Feb 01, 2005 12:23 am
by voltrader
Feyd -- really appreciate the help -- again. How many is that?!
6737 is probably the answer...
Seems to work fine now. Will test further tomorrow... tired...