RE: Full text score

IIRC, I think what you need may be in here somewhere: 12.7. Full-Text Search Functions 12.7.1. Boolean Full-Text Searches 12.7.2. Full-Text Searches with Query Expansion 12.7.3. Full-Text Stopwords 12.7.4. Full-Text Restrictions 12.7.5. Fine-Tuning MySQL Full-Text Search http://dev.mysql.com/doc

RE: Full text score

Cool, Any idea why the following would return 0 each time as the score? SELECT MATCH (resumeAsText) AGAINST ('coldfusion javascript') FROM db_au_com_exclaimit.tbl_resume The above returns 3 rows with 0 as score, the following only 2 rows, meaning it actually does match two rows, but does not di

Re: Full text score

In the last episode (Apr 12), Taco Fleur said: > is there such a thing as a score for full-text searches? Sure. The MATCH function returns one. http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list a

Full text score

Hi all, is there such a thing as a score for full-text searches? thanks in advance. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994

RE: counting keywords

Hello Steve, Your suggestion works like a charm, I am now trying to get my head around your following statement, I can't seem to get a grip on what you mean. Is there anyway you could elaborate a little on the following? It would be really nice if I could get the solution to work with full text an

RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)

I was using SQLYog 5.03 RC1. vmware ~ # mysql --version mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.1 But just to sanity check. I ssh'd in and tried this at the mysql command line utility: vmware ~ # mysql somedatabase Reading table information for completion of tab

RE: Student question answer schema

Mikhail, thanks for your insight on this. I received one other email that was very close to the same approach you are suggesting. Thanks for the time and effort that you spent on this detailed email. It makes sense to me and gets me on the right track. Thanks!! I think I have been doing a pretty go

Best practices

Hi guys I need some help with two things... I have the following table: CREATE TABLE `telephones` ( `contact_id` int(20) unsigned NOT NULL default '0', `telephone_id` int(20) unsigned NOT NULL default '0', `telephone_country_code` char(5) NOT NULL default '', `telephone_area_code` char(5

RE: mySQL Backups

Would you not lock tables on the slave? The idea of catching it up implies this is way it is done. Catching up means once replication can proceed once the tables are unlocked (on the slave). At least that is the way I read it... Tim -Original Message- From: [EMAIL PROTECTED] [mailto:[E

mySQL Backups

On 4/11/06, Dana Diederich <[EMAIL PROTECTED]> wrote: > We use a dedicated replicated instance for backups. > > Every night, we lock all of the tables, and dump all of them to > compressed files, and unlock them afterwards. It takes a while to catch > up, but that doesn't hurt anything. I too use

Re: Select a value between dates.

- Original Message - From: "H L" <[EMAIL PROTECTED]> To: Sent: Tuesday, April 11, 2006 4:05 PM Subject: Select a value between dates. I am far from an mysql expert... but is there a way to select between dates in a table and check if a value exists in all fields between dates. If on

Re: FW: New to TRIGGER and CALL. Example gives errors. (repost)

--- Daevid Vincent <[EMAIL PROTECTED]> wrote: > This may have been lost, so I'm reposting hoping for a clue as to why > the > mySQL example onlie gives me errors... > -Original Message- > Sent: Sunday, April 09, 2006 7:41 PM > > I'm trying to follow the example in the manual to create a

Select a value between dates.

I am far from an mysql expert... but is there a way to select between dates in a table and check if a value exists in all fields between dates. If one day between those dates cannot be booked i dont want to get it in my SUM resultset. I want to calculate Sum between those dates and i one idea

FW: New to TRIGGER and CALL. Example gives errors. (repost)

This may have been lost, so I'm reposting hoping for a clue as to why the mySQL example onlie gives me errors... -Original Message- Sent: Sunday, April 09, 2006 7:41 PM I'm trying to follow the example in the manual to create a trigger: http://dev.mysql.com/doc/refman/5.0/en/using-triggers

Backup / rotate an ever-growing DB

Hello, I'm using what looks to be a fairly popular cron.daily method of backing up a (4.1, MyISAM) database here: - |#!/bin/bash| |#| |# a rudimentary stab at MySQL backups to a remote location.| |# Note:| |# The target username receiving the backup is using scponly:

Re: Restrict MySQL server 4/5 to single IP

On 11.04.2006 09:35 (+0100), Jorrit Kronjee wrote: > Hostnames resolve into IP adresses, which are used to connect to the > MySQL server. MySQL doesn't care if you connect via a hostname or via an > IP address. It's not virtual hosting like Apache does. I know that. But to access different MySQL s

Re: Results Rank

I tried this but I miss the "cName"... thanks a lot J.R On 4/11/06, J.R. Bullington <[EMAIL PROTECTED]> wrote: > > Select COUNT(names) 'cName' FROM tblname GROUP BY names ORDER BY cName > > http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html > > J.R. > > -Original Message- > Fro

RE: Results Rank

Select COUNT(names) 'cName' FROM tblname GROUP BY names ORDER BY cName http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html J.R. -Original Message- From: jakot05 [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 12:37 PM To: mysql@lists.mysql.com Subject: Results Rank

Results Rank

Hi! I have a table like this names ··· john peter mary peter john peter mike mary mary mary ··· I want to get a list ordered by the number of times the name appears in the table, in the example: mary peter john mike How I can do this?...thanks a lot for the

Re: what is this? --> errno=2006 errmsg=Server gone

It can also occur if the client connection was idle for longer than wait_timeout, and the client has therefore exited ( may be the connection was invoked from a script, and the script does not know how to connect again ) Kishore Jalleda http://kjalleda.googlepages.com/projects On 4/9/06, Martin

Re: Problem with 'or' in 'where' clause? (longish)

At 17:20 +0200 11/4/06, Barry wrote: Japanese say here: Ganbatte! (Do your best!) Oh, so true! DYB! DYB! DYB! Hmmm - now you've confused me a bit. Quoting from the section in the DuBois book: "...a LEFT JOIN forces the result set to contain a row for every row in the left side table, wheth

RE: Student question answer schema

Brian, Sound like you need to normalize your data. Quick review of what you sent to the list shows that you need 5 tables: * student table - each student description * module table - each module description * question table - each question description * module-to-question table - what questions b

Re: Looking for advice on how to store and query some data

Russell - This should basically work for selecting the most recently selected preference... SELECT DISTINCT personID, classification FROM results_table ORDER BY date DESC On 4/10/06, Russell Horn <[EMAIL PROTECTED]> wrote: > > Hi, > > I'm storing data against a bunch of people and want to track

Re: Problem with 'or' in 'where' clause? (longish)

Barry wrote: Chris Sansom wrote: Yeah - it certainly seems promising. Better do some more reading! Japanese say here: Ganbatte! (Do your best!) Hmmm - now you've confused me a bit. Quoting from the section in the DuBois book: "...a LEFT JOIN forces the result set to contain a row for eve

Re: Student question answer schema

On Mon, 10 Apr 2006, Brian Menke wrote: Does anyone happen to know where a basic schema for tracking questions and answers from tests that a student has completed. I don't know why I am having difficulty with this, but I can't seem to figure out how to set up the tables correctly to store this i

Re: Problem with 'or' in 'where' clause? (longish)

Chris Sansom wrote: Yeah - it certainly seems promising. Better do some more reading! Japanese say here: Ganbatte! (Do your best!) Hmmm - now you've confused me a bit. Quoting from the section in the DuBois book: "...a LEFT JOIN forces the result set to contain a row for every row in the

Re: Problem with 'or' in 'where' clause? (longish)

At 17:05 +0200 11/4/06, Barry wrote: Once you get a hang on JOINs you will love it =) Yeah - it certainly seems promising. Better do some more reading! Just remember: everytime you do something like this: WHERE table1.id = table2.id You will be safer and faster to use JOINs because that's wh

Re: Problem with 'or' in 'where' clause? (longish)

Chris Sansom wrote: At 16:37 +0200 11/4/06, Barry wrote: select [what you want] from t1 LEFT JOIN t2 ON t2.id = t1.id LEFT JOIN t3 ON t3.id = t1.id LEFT JOIN t4 ON t4.id = t1.id LEFT JOIN t5 ON t5.id = t1.id LEFT JOIN t6 ON t6.id = t1.id where t2.text like '%search_term%' OR t3.text

Re: Problem with 'or' in 'where' clause? (longish)

At 16:37 +0200 11/4/06, Barry wrote: select [what you want] from t1 LEFT JOIN t2 ON t2.id = t1.id LEFT JOIN t3 ON t3.id = t1.id LEFT JOIN t4 ON t4.id = t1.id LEFT JOIN t5 ON t5.id = t1.id LEFT JOIN t6 ON t6.id = t1.id where t2.text like '%search_term%' OR t3.text like '%search_term%' O

Re: Making result rows to one row

Peter, Peter Brawley said: SELECT ..., GROUP CONCAT(LPAD(strokes,3,' ') SEPARATOR '') AS ' 1 2 3 4 5 6 7 8 9' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id WHERE tour_player_id=175 GROUP BY tsh.id This worked, however, it gives me

Re: Problem with 'or' in 'where' clause? (longish)

At 9:28 -0500 11/4/06, mos wrote: If you keep it in 5 different tables, the search will be as slow as molasses in January because of the joins. I'd recommend using FullText search on the text field. Hi Mike Thanks for the rapid response! OK - I've set all those text fields as FullTex

Re: Problem with 'or' in 'where' clause? (longish)

Chris Sansom wrote: Ah forgot something. Make sure you have placed indizies on the 'id' fields. This will make it even faster. Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysq

Re: Problem with 'or' in 'where' clause? (longish)

Chris Sansom wrote: First I should say I'm using MySQL 3.23.x because that's what's currently available on our host's server. An upgrade to 5.x is promised any time now, but I'm not holding my breath! So, with that in mind... I'm trying to do quite a sophisticated search across several tables

Re: Problem with 'or' in 'where' clause? (longish)

Chris, If you keep it in 5 different tables, the search will be as slow as molasses in January because of the joins. I'd recommend using FullText search on the text field. You *may* be able to do a Merge table on the 5 tables so MySQL sees it as 1 table. I'm not sure which of these feat

Re: Problem running multi master replication

Hello again :) On 4/10/06, Shawn Green <[EMAIL PROTECTED]> wrote: > > First rule: Do NOT share data files between server processes. Nothing > should directly interact with a datafile other than the server to which > it belongs. This includes other server processes as well as direct user > actions

Problem with 'or' in 'where' clause? (longish)

First I should say I'm using MySQL 3.23.x because that's what's currently available on our host's server. An upgrade to 5.x is promised any time now, but I'm not holding my breath! So, with that in mind... I'm trying to do quite a sophisticated search across several tables and am running into

RE: mySQL Backups

Some people use MySQLAdministrator. Some people use mysqldump. Yet others use scripts (as was mentioned before). We use a combo of mysqldump and Cron (Linux)/Task Scheduler (Windows). Using a text editor, create the mysqldump file that will push all of your data to a SQL file. For example: sh

RE: mySQL Backups

We use a dedicated replicated instance for backups. Every night, we lock all of the tables, and dump all of them to compressed files, and unlock them afterwards. It takes a while to catch up, but that doesn't hurt anything. Cheers, -Dana -Original Message- From: Greg 'groggy' Lehey [mai

Re: A complex JOIN scenario

On 4/11/06, Santino <[EMAIL PROTECTED]> wrote: > I do not try it : > > select invoice_id, order_id, customer_id, invoice_timestamp, > invoice_total, null > from invoice > union all > select invoice_id, null, null, null, null, invoice_journal_id > from invoice_archive Works like a charm, thanks! -

Re: A complex JOIN scenario

I do not try it : select invoice_id, order_id, customer_id, invoice_timestamp, invoice_total, null from invoice union all select invoice_id, null, null, null, null, invoice_journal_id from invoice_archive Santino At 12:08 +0200 11-04-2006, Kim Christensen wrote: Hello everyone I have a pre

Re: A complex JOIN scenario

> If this needs to give you this result: > > > > ++--++---+-- > -+ > > | invoice_id | order_id | invoice_journal_id |invoice_timestamp | > invoice_total | > > > ++--++---+

Re: A complex JOIN scenario

> > > Note that my example is not a realy result set, it's only here to show > > > you what kind of query I'm trying to build! > > > > Yes, that I understand. But WHAT is your current data in both tables > > from which you can/should derive your wanted resultset? > > invoice_archive: > +

Re: A complex JOIN scenario

> > Note that my example is not a realy result set, it's only here to show > > you what kind of query I'm trying to build! > > Yes, that I understand. But WHAT is your current data in both tables > from which you can/should derive your wanted resultset? invoice_archive: ++--+--

Index merge optimization (with OR) and table joins

Hi, I have been having a hassle getting the index_merge to work as expected when I am joining 2 tables on MySQL 5.0.19. The following example should make it clear: Table A key1 (primary key) key2 some_data Table B key1 (indexed) key2 (indexed) more_data SELECT a.key1,a.key2,b.more_d

Re: A complex JOIN scenario

> > > I have a pretty complex (at least for me) scenario which I really > > > can't work out the logic of. I have two tables with the following > > > layout: > > > > > > table 1: invoice > > > -- > > > invoice_id > > > order_id > > > customer_id > > > invoice_timest

Re: A complex JOIN scenario

On 4/11/06, Martijn Tonies <[EMAIL PROTECTED]> wrote: > Kim, > > > I have a pretty complex (at least for me) scenario which I really > > can't work out the logic of. I have two tables with the following > > layout: > > > > table 1: invoice > > -- > > invoice_id > > o

Re: A complex JOIN scenario

Kim, > I have a pretty complex (at least for me) scenario which I really > can't work out the logic of. I have two tables with the following > layout: > > table 1: invoice > -- > invoice_id > order_id > customer_id > invoice_timestamp > invoice_total > ...etc (only

A complex JOIN scenario

Hello everyone I have a pretty complex (at least for me) scenario which I really can't work out the logic of. I have two tables with the following layout: table 1: invoice -- invoice_id order_id customer_id invoice_timestamp invoice_total ...etc (only unique names)

Re: writing to a database on a read-only medium

sheeri kritzer wrote: > Frank, > > 1) Check out the FEDERATED storage engine, that might help. > > 2) You cannot specify a different location for a read database and a > write database. > > 3) Another solution is to use multiple databases -- each database is > just a directory, so you could

Re: mySQL Backups

On Tuesday, 11 April 2006 at 18:40:23 +1000, Jim wrote: > > What is the standard procedure for mySQL backups. > We need to backup at least once a day on a windows OS. > Is there a SQL Server Job Agent type solution? At the moment it's a mess. We're working on cleaning up the mess, and you should

Re: mySQL Backups

Jim wrote: Hi All, What is the standard procedure for mySQL backups. We need to backup at least once a day on a windows OS. Is there a SQL Server Job Agent type solution? mysql has an implemented mysqldumper. You could use that. Ther are also some php dumper scripts and perl scripts

RE: Making result rows to one row

Peter Brawley said: SELECT ..., GROUP CONCAT(LPAD(strokes,3,' ') SEPARATOR '') AS ' 1 2 3 4 5 6 7 8 9' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id WHERE tour_player_id=175 GROUP BY tsh.id

Re: Restrict MySQL server 4/5 to single IP

On 4/10/2006 8:50 PM, Yves Goergen wrote: > On 10.04.2006 18:32 (+0100), Jorrit Kronjee wrote: >> I'm not entirely sure what you mean. Are `mysql4.mydomain' and >> `mysql5.mydomain' hostnames? > > Yes, as I have explained earlier in this thread. > Hostnames resolve into IP adresses, which are

RE: counting keywords

At 1:35 PM +1000 4/11/06, Taco Fleur wrote: Thanks Steve, Much appreciated, I was hoping there was something a little simpler, but I will have a go at it. Anyway of doing this with RegEx, would that simplify things? Hi, and you're welcome - Unfortunately, I don't think this can be done with