[SOLVED] Should I hire somebody to compile MySQL 4.1 for me?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

[SOLVED] Should I hire somebody to compile MySQL 4.1 for me?

Post 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?
Last edited by voltrader on Tue Feb 01, 2005 2:32 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

out of curiosity, what's the query? Could you also explain why you require it?
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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.
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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?
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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...:?:
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post by voltrader »

Thanks. I just tried that... Not sure why I'm getting an error:

Unknown column 'score' in 'order clause'
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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

:?:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

there's also a potential to add them together in the selection, then order by that alias..
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post by voltrader »

Feyd -- really appreciate the help -- again. How many is that?! :lol:

6737 is probably the answer...


Seems to work fine now. Will test further tomorrow... tired...
Post Reply