ALTER table performance and bugs...

[Kevin A. Burton]

I just posted two detailed issues WRT the perfomance of ALTER table and 
what I think are pretty significant issues.

For the last few days I've been using MySQLs ALTER and REPAIR table 
functionality and its caused tons of countless problems and a great 
deal of lost sleep.

The first problem I noticed was that for large tables ALTER TABLE was 
taking hours! Lets say you have a 30G table. Good luck altering it as 
the default MySQL configuration will probably take 100 or more hours.

In MySQLs defense there are a number of variables you can use to 
increase the performance of an ALTER but the problem is that the two 
major ones (myisam_max_extra_sort_file_size, and 
myisam_max_sort_file_size) can't be set at runtime (during an ALTER). 
If these are set too low MySQL will revert to a /"Repair with 
keycache"/ strategy and this is 1000x slower than "Repair with sort". 
Once MySQL selects a ALTER strategy it can't use a faster one and you 
just have to ride it out.

http://www.peerfear.org/rss/permalink/2004/10/16/MySQLAndALTERTABLEGuiltyAsCharged
... and ...

It turns out that the performance of ALTER TABLE is somewhat of a 
black art and kept secret by the MySQL high priests 
 .


First off it seems there are two problems:
# In a replicated environment the ALTER TABLE is actually run on the 
master first and *then* the slave. This is not what you want because 
this will take twice as long. There needs to be a way to say /"don't 
run this on the slave"/ which would allow you to then ssh into your 
slave and run the ALTER TABLE directly. Of course if I could get the 
ALTER TABLE to run fast enough then this wouldn't be a problem. 

http://www.peerfear.org/rss/permalink/2004/10/15/MaxTableSizeInMySQL
Interested in feedback...
--
Use Rojo (RSS/Atom aggregator).  Visit http://rojo.com. Ask me for an 
invite!  Also see irc.freenode.net #rojo if you want to chat.

Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
If you're interested in RSS, Weblogs, Social Networking, etc... then you 
should work for Rojo!  If you recommend someone and we hire them you'll 
get a free iPod!
   
Kevin A. Burton, Location - San Francisco, CA
  AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412

Re: Problems with mysqldump

[Paul DuBois]

At 16:03 -0600 10/16/04, C.F. Scheidecker Antunes wrote:
Hello all,
I have a remote server that has a harddrive almost full. Therefore I 
need to dump a huge table to my other host.
OK, I've set the permissions so that I can log in to the server as 
root from my host. I've try to connect to it and it works.
I have to do the dump to the host because the remote server hard 
drive has almost no more space.

I've set the privileges so that I can log on from root from any host 
within my network like this:

GRANT ALL ON db_mydb.* TO 'root'@'192.168.0.%' IDENTIFIED by 'algo';
flush privileges;
Then, I try to run the following command to dump the database:
mysqldump -h 192.168.0.2 -u root -palgo --tab="/tmp" 
--fields-terminated-by=";" db_parts tbl_config > tbl_config.csv

What I've got in return is:
mysqldump: Got error: 1045: Access denied for user: 
'[EMAIL PROTECTED]' (Using password: YES) when executing 'SELECT INTO 
OUTFILE'
Apparently the account you're using doesn't have the FILE privilege,
which is a global privilege.  (Remember, GRANT ALL ON db_mydb.* grants
database-level privileges, not global privileges.)
If I run this command in the server it works just fine (when I had 
space), the problem is that I cannot run it on the other host as I 
cannot run it in the server due to its lack of
space to generate the outputfile.

Any ideas to where I am doing it wrong?
Thanks in advance,
C.F.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Show databases shows all even if no rights;

[Paul DuBois]

At 10:02 -0400 10/16/04, Michael J. Pawlowsky wrote:
I just noticed that a restricted user to only one database can still 
run "show databases;"
and see all the names of the databases in MySQL.

You would think that it would only return the databases that that 
user is allowed to connect to.

Is there a way I can show only those databases that he has rights to 
without giving him rights to the mysql  database to use the db table?
Make sure that the user doesn't have any global privileges that apply
to databases.  If the user has such a privilege, SHOW DATABASES will
display all databases.  (To check this, look at the privilege columns
in the mysql.user table for the user's account record.)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

query cache derived table

[Mike McMahon]

Based on may observations, derived tables (subselect) are not cacheable:
select column1, column2 from (select SQL_CACHE * from MyTable) as b
The second part is not inserted to QueryCache nor is it retrieved if 
already in
cache.
Also the file comments in sql_cache.cpp  - Make derived tables cachable.

Can anybody help steer me to the general steps required to add this 
functionality?
Thanks

Sorry if I have duplicated my post, not sure if first attempt was 
delivered due to
spam controls.

--
Mike McMahon [EMAIL PROTECTED]
ActiveWire   410-547-8277 x610
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: date time functions don't return not null rows

[Eric Bergen]

Your tables aren't setup very well. You should google for
normalization and 'boyce codd normal form'

-Eric


On Sat, 16 Oct 2004 22:27:51 +0200, owca <[EMAIL PROTECTED]> wrote:
> I'm trying get current week, starting from monday to sunday:
> 
> select UNIX_TIMESTAMP(day), g15, g16, g17, g18, g19, g20, g21, g22, id
> from tydzien where to_days(day) between
>  to_days(now())- mod(to_days(now()),7)+2 /*some parameter setting a starting day*/ 
> and
> (to_days(now())- mod(to_days(now()),7))+8 /*ending day ^*/
> order by dzien;
> 
> it works!
> the table looks like this:
> ++--+--+--+--+--+--+--+--++
> | day| g15  | g16  | g17  | g18  | g19  | g20  | g21  | g22  | id |
> ++--+--+--+--+--+--+--+--++
> | 1098128298 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |  5 |
> | 1098473898 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |  9 |
> | 1098560298 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 10 |
> | 1098646698 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 11 |
> ++--+--+--+--+--+--+--+--++
> 
> but it won't return the rows where any of columns g15, g16 etc has a value. eg
> 
> ++--+--+--+--+--+--+--+--++
> | day| g15  | g16  | g17  | g18  | g19  | g20  | g21  | g22  | id |
> ++--+--+--+--+--+--+--+--++
> | 1098128298 | NULL | 5561 | NULL | NULL | NULL | NULL | NULL | NULL |  9 |
> | 1098473898 | NULL | NULL | NULL |  | NULL | NULL | NULL | NULL | 10 |
> | 1098560298 | 2355 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 11 |
> ++--+--+--+--+--+--+--+--++
> 
> did i something wrong?
> 


-- 
Eric Bergen
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can MySQL do this?

[leegold]

...snip
> Is there a database generation system that would be able to handle this
> task and generate a webpage as output?

Try, AMP [Apache-MYSQL-PHP]

See the tutorial,
http://www.devarticles.com/c/a/Apache/Installing-PHP-under-Windows/

This tutorial is helpful. It's geared to MS Windows, so it's easy to
begin playing with AMP on what's probably on your home PC or at-work
client. 

> 
> Thanks in advance for any information,
> Skip Taylor
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can MySQL do this?

[Rhino]

- Original Message - 
From: "Skip Taylor" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, October 16, 2004 5:08 PM
Subject: Can MySQL do this?


> Hello,
>
> I'm new to MySQL, SQL in general and even PHP which may be needed to do
this.
> My intention is to implement this on an Internet webpage.
>
> I want to set up a database for listing people in cities for various
specialties with a
> time limit on their availability (date limited).
>
> I envision the tables as follows:
>
> US_States
> (index, state name)
>
> US_Cities
> (index, stateindex, city name)
>
> Persons_Specialty
> (index, specialty name)
>
> TimeLimitedSpecialtyInfo
> (index, personinfo_index, persons_specialty_index, city_index,
state_index, ending
> date)
>
> person_info
> (index, person's name, address, city_index, state_index, phone, fax,
email, url)
>
> I envision it working like this:
>
> From a dropdown list, the state would be selected.
>
> The selection would change to the cities in that state from the us_cities
table for
> selection in a drop down list .
>
> Then select the specialty you are looking for from a drop down list.
>
> Click Search
>
> At this point it should search the TimeLimitedSpecialtyinfo table, and
return the links
> to the appropriate entries in the personinfo table for display.
>
> I am at odds for the best way to do this. Previous experience in databases
is not on
> the web and I'm unsure as to time required to learn all of this.
>
You are using two common words in non-standard ways and that is causing me a
bit of confusion. Those two words are "index" and "link". I *think* you mean
to say "key" instead of "index" and "join" (or maybe "lookup") instead of
"link"; I'll explain that in a minute. IF those are reasonable substitutions
on my part, then you should have no trouble getting MySQL to accomodate your
database design.

In your table descriptions, you have an "index" in each of your tables. I
think you should use the term "key". It might sound nitpicky but an index is
always built on a table column but it is not a table column itself.

For example, given your first table, I would probably create it as follows:

create table US_States
(state_code char(2) not null,
 state_name char(20) not null,
 primary key(state_code));

The contents of the table would then be:
state_code state_name
  -
AL Alabama
AK Alaska
NH New Hampshire


The state_code column is serving as a key. Your queries to find the
description for a given state will seach on the state_code column. For
example:

select state_name
from US_States
where state_code = 'AL';

should return 'Alabama'.

An index, properly speaking, is a structure that is separate from the table
itself, i.e. a separate file, which you can choose to construct to improve
the performance of queries like the one I just gave in my example. The index
is created with a separate statement like this:

create unique index state_ix on US_States(state_code);

You are not required to build indexes but they are frequently a good idea if
you don't overdo them.

The other term that you are misusing is "link". I think you mean "join" or
maybe "lookup" where you said "link". For example, if you want to know the
full name of a state that is referenced in the US_Cities table, you will use
a join to do a lookup of that information in the US_States table. Therefore,
if the US_Cities table looks like this:

state_codecity_name

ALSpringfield
AK   Anchorage
NH   Springfield

This query will determine the full name of each state that contains a city
named Springfield.

select city_name, state_name
from US_States s inner join US_Cities c on s.state_code = c.state_code
where c.city_name = 'Springfield'

Which should give this result:

city_name state_name
-   --
SpringfieldAlabama
SpringfieldNew Hampshire

The process of finding something like the full state name that corresponds
to a state code is usually called "doing a lookup". The technique for doing
a lookup in SQL is a "join": the 'from US_States s inner join US_Cities c on
s.state_code = c.state_code' line is the part of the statement that combines
the two tables where the state codes match; this is called a join.

Again, I don't say these things to be pedantic, I'm just trying to put this
information in terms that most experienced database people will understand.

As I said before, if this is what you mean, you should have no great
difficulty creating a MySQL database to do what you want.

How long that will take is another matter. I've been doing database stuff
like this for 20 years so I could build what you are describing in a few
hours. If you're new to SQL and database, it might take you days or weeks,
depending on if you are a quick study or not and how much help you get. The
first time through anything can be quite painful due to t

Problems with mysqldump

[C.F. Scheidecker Antunes]

Hello all,
I have a remote server that has a harddrive almost full. Therefore I 
need to dump a huge table to my other host.
OK, I've set the permissions so that I can log in to the server as root 
from my host. I've try to connect to it and it works.
I have to do the dump to the host because the remote server hard drive 
has almost no more space.

I've set the privileges so that I can log on from root from any host 
within my network like this:

GRANT ALL ON db_mydb.* TO 'root'@'192.168.0.%' IDENTIFIED by 'algo';
flush privileges;
Then, I try to run the following command to dump the database:
mysqldump -h 192.168.0.2 -u root -palgo --tab="/tmp" 
--fields-terminated-by=";" db_parts tbl_config > tbl_config.csv

What I've got in return is:
mysqldump: Got error: 1045: Access denied for user: '[EMAIL PROTECTED]' 
(Using password: YES) when executing 'SELECT INTO OUTFILE'

If I run this command in the server it works just fine (when I had 
space), the problem is that I cannot run it on the other host as I 
cannot run it in the server due to its lack of
space to generate the outputfile.

Any ideas to where I am doing it wrong?
Thanks in advance,
C.F.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

date time functions don't return not null rows

[owca]

I'm trying get current week, starting from monday to sunday:

select UNIX_TIMESTAMP(day), g15, g16, g17, g18, g19, g20, g21, g22, id
from tydzien where to_days(day) between
 to_days(now())- mod(to_days(now()),7)+2 /*some parameter setting a starting 
day*/ and
(to_days(now())- mod(to_days(now()),7))+8 /*ending day ^*/
order by dzien;

it works!
the table looks like this:
++--+--+--+--+--+--+--+--++
| day| g15  | g16  | g17  | g18  | g19  | g20  | g21  | g22  | id |
++--+--+--+--+--+--+--+--++
| 1098128298 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |  5 |
| 1098473898 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |  9 |
| 1098560298 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 10 |
| 1098646698 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 11 |
++--+--+--+--+--+--+--+--++

but it won't return the rows where any of columns g15, g16 etc has a value. eg

++--+--+--+--+--+--+--+--++
| day| g15  | g16  | g17  | g18  | g19  | g20  | g21  | g22  | id |
++--+--+--+--+--+--+--+--++
| 1098128298 | NULL | 5561 | NULL | NULL | NULL | NULL | NULL | NULL |  9 |
| 1098473898 | NULL | NULL | NULL |  | NULL | NULL | NULL | NULL | 10 |
| 1098560298 | 2355 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 11 |
++--+--+--+--+--+--+--+--++

did i something wrong?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Fulltext Search help

[leegold]

On Fri, 15 Oct 2004 20:05:57 -0400, "leegold" <[EMAIL PROTECTED]>
said:
> 
> On Fri, 15 Oct 2004 15:00:10 -0700, "Chris W. Parker"
... But maybe there's a better way? I
> wish I could do *searchstring* in Fulltext even if the speed was slow as
> molasass it's the spec the user wants.

Of course "*searchstring*" would invalidate the indexing.

Thanks.
Lee G.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Transactions - working but unsure about steps

[Stuart Felenstein]

Thank you Osvaldo, That is what I was thinking. 
Sometimes when you try to learn from example or manual
it's a bit unclear because they generally show a
"simple" transaction (1 insert into 1 table)

Stuart
--- Osvaldo Sommer <[EMAIL PROTECTED]> wrote:

> You have a problem, what if the first insert give a
> error, then you
> don't know. You have to check each statement for
> error and if no error
> if found in all the statements then issue a commit
> if not a rollback
> 
> Osvaldo Sommer
> 
> -Original Message-
> From: Stuart Felenstein [mailto:[EMAIL PROTECTED] 
> Sent: Saturday, October 16, 2004 7:19 AM
> To: [EMAIL PROTECTED]
> Subject: Transactions - working but unsure about
> steps
> 
> My statements are all working but I'm not sure if
> things are set up correctly.  I say this because at
> one point the first $query failed, yet the rest of
> inserts wre committed.  Now I believe I need to set
> autocommit to 0 , yet the query failed due to a
> syntax
> error. Hence the rule about 0 records effected
> wouldn't be the case here.
> 
> 
> Here is what I have:
> 
> //Create these functions -
> function begin()
> {
> mysql_query("BEGIN");
> }
> function commit()
> {
> mysql_query("COMMIT");
> }
> function rollback()
> {
> mysql_query("ROLLBACK");
> }
> 
> connection statement with error checking...
> 
> begin(); // transaction begins
> $query = "INSERT INTO firsttable.//first query
> $result = mysql_query($query); // process first
> query
> $query = "INSERT INTO secondtable...//second query
> $result = mysql_query($query); // process second
> query
> $query = "INSERT INTO thirdtable//third query
> $result = mysql_query($query); // process third
> query
> 
> then: 
> 
> if(!$result)
> {
> echo mysql_errno($link) . ": " . mysql_error($link).
> "\n";
> rollback(); // transaction rolls back
> 
> exit;
> }
> else
> {
> commit(); // transaction is committed
> echo "your insertion was successful";
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system
> (http://www.grisoft.com).
> Version: 6.0.778 / Virus Database: 525 - Release
> Date: 10/15/2004
>  
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system
> (http://www.grisoft.com).
> Version: 6.0.778 / Virus Database: 525 - Release
> Date: 10/15/2004
>  
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Transactions - working but unsure about steps

[Osvaldo Sommer]

You have a problem, what if the first insert give a error, then you
don't know. You have to check each statement for error and if no error
if found in all the statements then issue a commit if not a rollback

Osvaldo Sommer

-Original Message-
From: Stuart Felenstein [mailto:[EMAIL PROTECTED] 
Sent: Saturday, October 16, 2004 7:19 AM
To: [EMAIL PROTECTED]
Subject: Transactions - working but unsure about steps

My statements are all working but I'm not sure if
things are set up correctly.  I say this because at
one point the first $query failed, yet the rest of
inserts wre committed.  Now I believe I need to set
autocommit to 0 , yet the query failed due to a syntax
error. Hence the rule about 0 records effected
wouldn't be the case here.


Here is what I have:

//Create these functions -
function begin()
{
mysql_query("BEGIN");
}
function commit()
{
mysql_query("COMMIT");
}
function rollback()
{
mysql_query("ROLLBACK");
}

connection statement with error checking...

begin(); // transaction begins
$query = "INSERT INTO firsttable.//first query
$result = mysql_query($query); // process first query
$query = "INSERT INTO secondtable...//second query
$result = mysql_query($query); // process second query
$query = "INSERT INTO thirdtable//third query
$result = mysql_query($query); // process third query

then: 

if(!$result)
{
echo mysql_errno($link) . ": " . mysql_error($link).
"\n";
rollback(); // transaction rolls back

exit;
}
else
{
commit(); // transaction is committed
echo "your insertion was successful";

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 10/15/2004
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 10/15/2004
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Show databases shows all even if no rights;

[Michael J. Pawlowsky]

I just noticed that a restricted user to only one database can still run 
"show databases;"
and see all the names of the databases in MySQL.

You would think that it would only return the databases that that user 
is allowed to connect to.

Is there a way I can show only those databases that he has rights to 
without giving him rights to the mysql  database to use the db table?

Thanks,
Mike

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Transactions - working but unsure about steps

[Stuart Felenstein]

My statements are all working but I'm not sure if
things are set up correctly.  I say this because at
one point the first $query failed, yet the rest of
inserts wre committed.  Now I believe I need to set
autocommit to 0 , yet the query failed due to a syntax
error. Hence the rule about 0 records effected
wouldn't be the case here.


Here is what I have:

//Create these functions -
function begin()
{
mysql_query("BEGIN");
}
function commit()
{
mysql_query("COMMIT");
}
function rollback()
{
mysql_query("ROLLBACK");
}

connection statement with error checking...

begin(); // transaction begins
$query = "INSERT INTO firsttable.//first query
$result = mysql_query($query); // process first query
$query = "INSERT INTO secondtable...//second query
$result = mysql_query($query); // process second query
$query = "INSERT INTO thirdtable//third query
$result = mysql_query($query); // process third query

then: 

if(!$result)
{
echo mysql_errno($link) . ": " . mysql_error($link).
"\n";
rollback(); // transaction rolls back

exit;
}
else
{
commit(); // transaction is committed
echo "your insertion was successful";

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: INSERT .. ON DUPLICATE KEY UPDATE behaviour

[Sergei Golubchik]

Hi!

On Oct 15, Jason McManus wrote:
> Good afternoon,
> 
> I have had reason to use the new (as of 4.1.1) INSERT .. ON DUPLICATE KEY
> UPDATE syntax in MySQL.  However, I am a bit confused as to the return
> value.  Issuing the INSERT .. ON DUP KEY UP statement, upon finding a
> duplicate key and updating that record, mysql-client returns "2 rows
> affected"; upon inserting a unique row, it returns the expected "1 row
> affected".

> I have theorized that the 1st row is referring to a row that temporarily
> existed during the insert, then was deleted, and the 2nd row is the row that
> was updated (or vice versa), but this is fairly dependent upon how this
> statement was implemented; normally a key constraint shouldn't actually
> succeed and should return an error before modifying the table (i.e. 0 rows
> affected).  Thus I would like to address the developers and inquire about
> this particular behaviour.

See http://bugs.mysql.com/bug.php?id=2709.

The reasoning is that the current behaviour is more useful, as it gives
more information about what the statement did (see the url above for the
example).
 
Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Fulltext Search help

[Santino]

Try to search in keyword table:
select * from keywords WHERE MATCH(keyword_txt) AGAINST 
('$radio_keyword' IN BOOLEAN MODE);

If it works the problem is in the join.
Santino
$query = "SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST
('$radio_keyword' IN BOOLEAN MODE)";
At 20:05 -0400 15-10-2004, leegold wrote:
On Fri, 15 Oct 2004 15:00:10 -0700, "Chris W. Parker"
<[EMAIL PROTECTED]> said:
 leegold 
 on Friday, October 15, 2004 2:32 PM said:
 > I do fulltext search on "work". And AFAIK the search will not find
 > "work". For that matter the seach will not find "ingm". How do I
 > implement in MYSQL/PHP a search that will have this action?
 please share the current query you are trying to use and we can go from
 there.
Well, my current query is below, it searchs a text type field full of
keywords with each record. The eventual user now said she wants a search
"action" like I described above, kinda like a "find this string" that a
text editor does. I would love to keep using Fulltext cause it has
awesome features, but the wildcard can not be prepended *and* appended
to a search term ie. *work*, no can do AFAIK. He's my current query in
php,
$query = "SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST
('$radio_keyword' IN BOOLEAN MODE)";
So I guess I could remove the fulltext index and reindex and do
"...WHERE field LIKE '%string%';". But maybe there's a better way? I
wish I could do *searchstring* in Fulltext even if the speed was slow as
molasass it's the spec the user wants.
Lee G.

 however, something simple is the following:
 SELECT *
 FROM table
 WHERE field LIKE '%string%';
 hth,
 chris.
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]