mysqld-nt vs. mysql-max-nt

2003-10-24 Thread Allen Weeks
Hi All,

I can't seem to find any documentation or opinions that would help me decide
if it is better to run mysqld-max-nt over the non-max server.

Opinions and pointers to supporting docs would be very welcome (especially
pointer to docs).

Thanks to All in advance.

Allen


Update from other table

2003-10-24 Thread William IT
Does MySQL support update from other table?

I run this query and get error:

UPDATE TableA SET Division=TableB.Division
FROM TableB 
WHERE TableA.Code=TableB.Code

Thanks.


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



Re: selecting from two different severs

2003-10-24 Thread Matt W
Hi Mike,

Simple; you don't. :-) Someone please correct me if I'm wrong.

BTW, this isn't a Windows specific question, so I'm sending it to the
General list too.


Regards,
Matt


- Original Message -
From: "Mike Karplus"
Sent: Friday, October 24, 2003 5:04 PM
Subject: selecting from two different severs


How do I join two tables in two different servers ? ( btw, in different
locations, thousands of km away...)

Thank you in advance, and nice weekend,

Mike Karplus


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



Enquiry on rhe use of MySQL

2003-10-24 Thread Lay Hoon Tan
Hi,

I've just installed MySQL on my PC but do not know which program to access
in order to use it. Does anyone knows ?

Regards



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



Re[2]: XML support for MySQL

2003-10-24 Thread Stefan Hinz
Jeremy, Prasad,

> On Mon, Oct 13, 2003 at 11:45:33PM -0600, Prasad Budim Ram wrote:
>> Is it possible to direct xml support for queryiing a SQL statement and
>> get the result in also in xml?

> Nope.

At least, you can start the MySQL client program with an option so
that it outputs XML:

shell> mysql --xml

mysql> SHOW TABLES FROM test;


  
booltest
  
  
daten
  
...

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3

[filter fodder: sql, mysql, query]


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



memory consumed by mysql to process a query

2003-10-24 Thread chuang liu
Hi:
I am current measuring the performance of mysql. Is there a way of getting
memory consumed by mysql to process a query? Any suggestion is
highly aprreciated.

Best Regards

Chuang


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



Re: Case statement

2003-10-24 Thread Mojtaba Faridzad
oops, I found what was my mistake. I copied and paste a line to write the
command for you but I forgot to remove CASE :)   so write your query like
this:

SELECT
CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
THEN
'binding'
WHEN status IN('NA', 'NG', 'RA')
END as action
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
( status IN ( 'NA', 'NG', 'RA' )));


- Original Message - 
From: "Mojtaba Faridzad" <[EMAIL PROTECTED]>
To: "Chris Boget" <[EMAIL PROTECTED]>; "MySQL" <[EMAIL PROTECTED]>
Sent: Friday, October 24, 2003 3:43 PM
Subject: Re: Case statement


> I didn't have your table so I tried to write the same command by one of my
> tables. my query which worked, is:
>
> SELECT  CASE  WHEN left( workorder, 1  )
> IN (  'C',  'T' )
> THEN  'group1'
> WHEN left( workorder, 1  )
> IN (  '3',  'R' )
> THEN  'gorup2'
> ELSE
> 'group3'
> END  FROM workfile;
>
>
> - Original Message - 
> From: "Chris Boget" <[EMAIL PROTECTED]>
> To: "Mojtaba Faridzad" <[EMAIL PROTECTED]>; "MySQL"
> <[EMAIL PROTECTED]>
> Sent: Friday, October 24, 2003 3:34 PM
> Subject: Re: Case statement
>
>
> > > SELECT
> > > CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
> > > THEN
> > > 'binding'
> > > CASE WHEN status IN('NA', 'NG', 'RA')
> > > END as action
> > > FROM master_info WHERE
> > > (( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
> > > ( status IN ( 'NA', 'NG', 'RA' )));
> >
> > Sadly, that didn't work:
> >
> > Your MySQL connection id is 208517 to server version: 4.0.14-log
> >
> > Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> >
> > mysql> SELECT
> > -> CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
> > -> THEN
> > -> 'binding'
> > -> CASE WHEN status IN('NA', 'NG', 'RA')
> > -> END as action
> > -> FROM master_info WHERE
> > -> (( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
> > -> ( status IN ( 'NA', 'NG', 'RA' )));
> > ERROR 1064: You have an error in your SQL syntax.  Check the manual that
> > corresponds to your MySQL server version for the right syntax to use
near
> 'CASE
> > WHEN status IN('NA', 'NG', 'RA') END as action FROM master
> >
> > Do you have something similar that works?  What does it look like?
> >
> > Chris
> >
>


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



Re: Case statement [SOLVED]

2003-10-24 Thread Chris Boget
> I didn't have your table so I tried to write the same command by one of my
> tables. my query which worked, is:
> SELECT  CASE  WHEN left( workorder, 1  )
> IN (  'C',  'T' )
> THEN  'group1'
> WHEN left( workorder, 1  )
> IN (  '3',  'R' )
> THEN  'gorup2'
> ELSE
> 'group3'
> END  FROM workfile;

which is different from what you wrote in your previous email:

> SELECT
> CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
> THEN
> 'binding'
> CASE WHEN status IN('NA', 'NG', 'RA')
> END as action
> FROM master_info WHERE
> (( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
> ( status IN ( 'NA', 'NG', 'RA' )));

The difference being the CASE preceding the second WHEN in your
first suggestion.

The query that I got to work is as follows:

SELECT 
CASE 
WHEN
status IN( 'BR', 'VQ', 'RQ', 'NG' )
THEN
'binding' 
WHEN
status IN( 'NA', 'NG', 'RA' )
THEN
'approving'
END as action, status
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR 
 ( status IN ( 'NA', 'NG', 'RA' )));

Thank you very much for your help, Mojtaba!

Chris
-mysql,query,blah


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



Re: Case statement

2003-10-24 Thread Mojtaba Faridzad
I didn't have your table so I tried to write the same command by one of my
tables. my query which worked, is:

SELECT  CASE  WHEN left( workorder, 1  )
IN (  'C',  'T' )
THEN  'group1'
WHEN left( workorder, 1  )
IN (  '3',  'R' )
THEN  'gorup2'
ELSE
'group3'
END  FROM workfile;


- Original Message - 
From: "Chris Boget" <[EMAIL PROTECTED]>
To: "Mojtaba Faridzad" <[EMAIL PROTECTED]>; "MySQL"
<[EMAIL PROTECTED]>
Sent: Friday, October 24, 2003 3:34 PM
Subject: Re: Case statement


> > SELECT
> > CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
> > THEN
> > 'binding'
> > CASE WHEN status IN('NA', 'NG', 'RA')
> > END as action
> > FROM master_info WHERE
> > (( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
> > ( status IN ( 'NA', 'NG', 'RA' )));
>
> Sadly, that didn't work:
>
> Your MySQL connection id is 208517 to server version: 4.0.14-log
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> SELECT
> -> CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
> -> THEN
> -> 'binding'
> -> CASE WHEN status IN('NA', 'NG', 'RA')
> -> END as action
> -> FROM master_info WHERE
> -> (( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
> -> ( status IN ( 'NA', 'NG', 'RA' )));
> ERROR 1064: You have an error in your SQL syntax.  Check the manual that
> corresponds to your MySQL server version for the right syntax to use near
'CASE
> WHEN status IN('NA', 'NG', 'RA') END as action FROM master
>
> Do you have something similar that works?  What does it look like?
>
> Chris
>


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



RE: MERGE table limitations

2003-10-24 Thread Dathan Vance Pattishall
OS/FileSystem INODE limitation.

How many files can fit into a directory? 64K on Redhat if you modify
file-max




- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
-->Sent: Friday, October 24, 2003 12:26 PM
-->To: [EMAIL PROTECTED]
-->Subject: Q: MERGE table limitations
-->
-->Does anyone know of any limitations on the number of underlying
tables in
-->a MERGE table?  Seems to be a topic of scant coverage in the books
and
-->online docs.
-->
-->ANY help or ideas would be appreciated.
-->
-->/Tony
-->
-->--
-->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: Case statement

2003-10-24 Thread Chris Boget
> SELECT
> CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
> THEN
> 'binding'
> CASE WHEN status IN('NA', 'NG', 'RA')
> END as action
> FROM master_info WHERE
> (( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
> ( status IN ( 'NA', 'NG', 'RA' )));

Sadly, that didn't work:

Your MySQL connection id is 208517 to server version: 4.0.14-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT
-> CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
-> THEN
-> 'binding'
-> CASE WHEN status IN('NA', 'NG', 'RA')
-> END as action
-> FROM master_info WHERE
-> (( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
-> ( status IN ( 'NA', 'NG', 'RA' )));
ERROR 1064: You have an error in your SQL syntax.  Check the manual that 
corresponds to your MySQL server version for the right syntax to use near 'CASE 
WHEN status IN('NA', 'NG', 'RA') END as action FROM master

Do you have something similar that works?  What does it look like?

Chris


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



Q: MERGE table limitations

2003-10-24 Thread Tbird67ForSale
Does anyone know of any limitations on the number of underlying tables in a MERGE 
table?  Seems to be a topic of scant coverage in the books and online docs.

ANY help or ideas would be appreciated.

/Tony

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



RE: reproducible error 17

2003-10-24 Thread Dathan Vance Pattishall
*** 1. row ***
  Master_Host: 10.2.12.224
  Master_User: rep
  Master_Port: 3306
Connect_retry: 60
  Master_Log_File: ef224-bin.020
  Read_Master_Log_Pos: 409223612
   Relay_Log_File: ef242-relay-bin.016
Relay_Log_Pos: 330063332
Relay_Master_Log_File: ef224-bin.020
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_do_db: 
  Replicate_ignore_db: 
   Last_errno: 25
   Last_error: Error 'Can't create symlink
'./ff_recent/#sql-17f7_c.MYI' pointing at
'/var/lib/mysql/ff_recent/#sql-17f7_c.MYI' (Error 17)' on query 'ALTER
TABLE recentmembers_20 add have_children tinyint unsigned not NULL
default 0'. Default database: 'ff_recent'
 Skip_counter: 0
  Exec_master_log_pos: 348632736
  Relay_log_space: 390689457

- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED]
-->Sent: Friday, October 24, 2003 11:27 AM
-->To: [EMAIL PROTECTED]
-->Cc: [EMAIL PROTECTED]
-->Subject: reproducible error 17
-->
-->Error 'Can't create symlink './ffadult_recent/#sql-44b_e87.MYI'
pointing
-->at '/var/lib/mysql/ff_recent/#sql-44b_e87.MYI' (Error 17)' on query
-->'ALTER TABLE recentmembers_8 add have_children tinyint unsigned not
NULL
-->default 0'. Default database: 'ff_recent' | 0| 346296822
-->| 369493795
-->
-->
-->Perror says
-->Error code  17:  File exists
-->
-->I have 2 alters submitted on the master in sequential order. On the
-->slave it executes in the same order but produces error 17. Somehow
the
-->tempfile already exists, but when I go to the datadir this temp file
is
-->not displayed in the directory.
-->
-->Performing a slave stop slave start does not work.
-->Restarting the mysql server on the slave is the only method I found
to
-->recover from the error.
-->
-->
-->Version of mySQL: 4.0.15
-->Kernel: Linux ef242.friendfinderinc.com 2.4.18-4smp #1 SMP Sun Apr 14
-->13:50:35 PDT 2002 i686 unknown
-->*PATCHED*
-->
-->
-->- Dathan Vance Pattishall
-->  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
-->  - http://friendfinder.com/go/p40688
-->



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



Re: Really slow query (compared with Visual FoxPro)

2003-10-24 Thread Mojtaba Faridzad
Hector,

I migrated from Visual FoxPro too. VFP has different style to work with a
table. MySQL gives you some advantage which you don't have it in VFP. for
example, try to connect to the work server from home and run this query. it
will take 5 days to finish it, but still MySQL takes 3 min.s  :)  believe me
because I did this test and our company decided to stop using VFP and
rewrite everything in VB and MySQL.

BUT my experience: try to change the logic of your report not to retrieve
large number of records. user LIMIT to create the reports page by page. this
is the best and even better for the user.

good luck

- Original Message - 
From: "Matt W" <[EMAIL PROTECTED]>
To: "Héctor Villafuerte D." <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Friday, October 24, 2003 3:00 PM
Subject: Re: Really slow query (compared with Visual FoxPro)


> Hi Hector,
>
> Umm, it looks like you're simply doing a SELECT in the first query and
> *populating a new table* in the second. Of course inserting 3.3 million
> rows is going to take extra time! How can you even compare the 2 when
> they're doing different things?
>
>
> Matt
>
>
> - Original Message -
> From: "Héctor Villafuerte D."
> Sent: Friday, October 24, 2003 12:10 PM
> Subject: Really slow query (compared with Visual FoxPro)
>
>
> > Hi all,
> > I'm migrating from Visual FoxPro (I'm a newbie).
> > On a daily basis I need to run this query on a table with a little
> more
> > than
> > 5 million records.
> >
> > + MS Visual FoxPro 6.0
> > select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos
> from
> > traf_oper
> > group by 1, 2, 3, 4 into table oper_grp
> > This took about 173 seconds (let's say 3 minutes)
> >
> > + MySQL 4.1.0-alpha-max-nt
> > mysql> create table oper_grp select tel, telefb, rutaentran,
> rutasalien,
> > -> sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4;
> > Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --->>> 516
> > minutes!
> > Records: 3301017  Duplicates: 0  Warnings: 0
> >
> > Why is there such a big difference?! Any optimization tips I could
> follow?
> > Thanks in advance,
> > Hector
>
>
> -- 
> 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: Case statement

2003-10-24 Thread Mojtaba Faridzad
SELECT
CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
THEN
'binding'
CASE WHEN status IN('NA', 'NG', 'RA')
END as action
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
( status IN ( 'NA', 'NG', 'RA' )));


- Original Message - 
From: "Chris Boget" <[EMAIL PROTECTED]>
To: "MySQL" <[EMAIL PROTECTED]>
Sent: Friday, October 24, 2003 2:19 PM
Subject: Case statement


> I'm trying to do something like the following:
>
> SELECT
> CASE status
> WHEN
> IN ( 'BR', 'VQ', 'RQ', 'NG' )
> THEN
> 'binding'
> WHEN
> IN ( 'NA', 'NG', 'RA' )
> THEN
> 'approving'
> END as action
> FROM master_info WHERE
> (( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
>  ( status IN ( 'NA', 'NG', 'RA' )));
>
> But this query is obviously going to fail.  But there _has_ to be
> a more elegant way than doing:
>
> WHEN
> 'BR'
> THEN
> 'binding'
> WHEN
> 'VQ'
> THEN
> 'binding'
> ...
>
> Is there?  If so, I can't figure out what it would be.  Any suggestions?
>
> thnx,
> Chris
> -mysql,query,blah
>
>
>
> -- 
> 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: Really slow query (compared with Visual FoxPro)

2003-10-24 Thread Matt W
Hi Hector,

Umm, it looks like you're simply doing a SELECT in the first query and
*populating a new table* in the second. Of course inserting 3.3 million
rows is going to take extra time! How can you even compare the 2 when
they're doing different things?


Matt


- Original Message -
From: "Héctor Villafuerte D."
Sent: Friday, October 24, 2003 12:10 PM
Subject: Really slow query (compared with Visual FoxPro)


> Hi all,
> I'm migrating from Visual FoxPro (I'm a newbie).
> On a daily basis I need to run this query on a table with a little
more
> than
> 5 million records.
>
> + MS Visual FoxPro 6.0
> select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos
from
> traf_oper
> group by 1, 2, 3, 4 into table oper_grp
> This took about 173 seconds (let's say 3 minutes)
>
> + MySQL 4.1.0-alpha-max-nt
> mysql> create table oper_grp select tel, telefb, rutaentran,
rutasalien,
> -> sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4;
> Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --->>> 516
> minutes!
> Records: 3301017  Duplicates: 0  Warnings: 0
>
> Why is there such a big difference?! Any optimization tips I could
follow?
> Thanks in advance,
> Hector


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



RE: Really slow query (compared with Visual FoxPro)

2003-10-24 Thread Dan Greene
As you are selecting all records (no where clause), it will scan the whole table every 
time, I believe...

does anyone know if he added the other columns to his index, or had 4 seperate indexes 
(one per column) would they be used in this operation?


> 
> Ok, I've found the optimization chapter in the manual and I'm still 
> reading it.
> Here's what I've done so far:
> 
> mysql> alter table traf_oper add index (tel), add index (telefb);
> 
> mysql> explain select tel, telefb, rutaentran, rutasalien, 
> sum(minutos) 
> as minutos from traf_oper group by 1, 2, 3, 4;
> ++-+---+--+---+--+
> -+--+-+-+
> | id | select_type | table | type | possible_keys | key  
> | key_len | 
> ref  | rows| Extra   |
> ++-+---+--+---+--+
> -+--+-+-+
> |  1 | SIMPLE  | traf_oper | ALL  | NULL  | NULL 
> |NULL | 
> NULL | 5014313 | Using temporary; Using filesort |
> ++-+---+--+---+--+
> -+--+-+-+
> 1 row in set (0.50 sec)
> 
> mysql> describe traf_oper;
> ++--+---+--+-+
> -+---+
> | Field  | Type | Collation | Null | Key | 
> Default | Extra |
> ++--+---+--+-+
> -+---+
> | tel| char(8)  | latin1_swedish_ci | YES  | MUL | 
> NULL|   |
> | fecha  | char(8)  | latin1_swedish_ci | YES  | | 
> NULL|   |
> | hora   | char(6)  | latin1_swedish_ci | YES  | | 
> NULL|   |
> | telefb | char(14) | latin1_swedish_ci | YES  | MUL | 
> NULL|   |
> | tiempotasa | char(6)  | latin1_swedish_ci | YES  | | 
> NULL|   |
> | rutasalien | char(7)  | latin1_swedish_ci | YES  | | 
> NULL|   |
> | rutaentran | char(7)  | latin1_swedish_ci | YES  | | 
> NULL|   |
> | serie  | char(3)  | latin1_swedish_ci | YES  | | 
> NULL|   |
> | tipotraf   | int(1)   | binary| YES  | | 
> NULL|   |
> | minutos| int(4)   | binary| YES  | | 
> NULL|   |
> ++--+---+--+-+
> -+---+
> 10 rows in set (0.00 sec)
> 
> So, why isn't SELECT using indexes (key = NULL, key_len = NULL)?
> Where else do I need to add indexes?
> Thanks for your help!
> 
> 
> -- 
> 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: Really slow query (compared with Visual FoxPro)

2003-10-24 Thread "Héctor Villafuerte D."
Dan Greene wrote:

Indexes are your friend.  Perhaps your best friend (as far as databases go)
---
To give light on your question... you can index any field... if the entries are 
distinct, it's called a 'unique index' which are the best kind to use.  Otherwise you 
have a 'non-unique index', which can also be handy
Think of indexes, like... well... book indexes... to prevent you from looking all over the book for an answer, you can find the exact page the topic is on (unique index), or if it's covered in multiple pages, you reduce your searching to just that list of pages (non-unique index).

Then there's compound indexes, which is an index on the value of two seperate fields (which can be unique, or non-unique).  

As a rule of thumb, you want indexes on your unique key (primary key) (call record id) 
Most db's make indexes automatically on keys you say are the primary key. Also on your 
most commonly searched fields (usually non-unique) such as foreign keys (call record 
id in your record notes table for example)
--
Ok, I've found the optimization chapter in the manual and I'm still 
reading it.
Here's what I've done so far:

mysql> alter table traf_oper add index (tel), add index (telefb);

mysql> explain select tel, telefb, rutaentran, rutasalien, sum(minutos) 
as minutos from traf_oper group by 1, 2, 3, 4;
++-+---+--+---+--+-+--+-+-+
| id | select_type | table | type | possible_keys | key  | key_len | 
ref  | rows| Extra   |
++-+---+--+---+--+-+--+-+-+
|  1 | SIMPLE  | traf_oper | ALL  | NULL  | NULL |NULL | 
NULL | 5014313 | Using temporary; Using filesort |
++-+---+--+---+--+-+--+-+-+
1 row in set (0.50 sec)

mysql> describe traf_oper;
++--+---+--+-+-+---+
| Field  | Type | Collation | Null | Key | Default | Extra |
++--+---+--+-+-+---+
| tel| char(8)  | latin1_swedish_ci | YES  | MUL | NULL|   |
| fecha  | char(8)  | latin1_swedish_ci | YES  | | NULL|   |
| hora   | char(6)  | latin1_swedish_ci | YES  | | NULL|   |
| telefb | char(14) | latin1_swedish_ci | YES  | MUL | NULL|   |
| tiempotasa | char(6)  | latin1_swedish_ci | YES  | | NULL|   |
| rutasalien | char(7)  | latin1_swedish_ci | YES  | | NULL|   |
| rutaentran | char(7)  | latin1_swedish_ci | YES  | | NULL|   |
| serie  | char(3)  | latin1_swedish_ci | YES  | | NULL|   |
| tipotraf   | int(1)   | binary| YES  | | NULL|   |
| minutos| int(4)   | binary| YES  | | NULL|   |
++--+---+--+-+-+---+
10 rows in set (0.00 sec)
So, why isn't SELECT using indexes (key = NULL, key_len = NULL)?
Where else do I need to add indexes?
Thanks for your help!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


Rekall V2.1.0 stable has be released

2003-10-24 Thread minky
Hi
We are pleased to announce the release of Rekall V2.1.0 stable for Linux and
Windows

Please note, the stable release of Rekall V2.1.0 is only available from
either http://www.totalrekall.co.uk and http://www.rygannon.com

What is Rekall?

Rekall is a programmable (using Python), database independant, GUI
database client. Rekall, includes Form, Report and Query Designers, a Python
Script editor and debugger, Macros, Wizards, re-useable components, and lots
more. Included with the standard edition are drivers for MySQL, PostgreSQL
and xBase. Drivers for DB2, Oracle, and ODBC are available separately

Regards
John



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



reproducible error 17

2003-10-24 Thread Dathan Vance Pattishall
Error 'Can't create symlink './ffadult_recent/#sql-44b_e87.MYI' pointing
at '/var/lib/mysql/ff_recent/#sql-44b_e87.MYI' (Error 17)' on query
'ALTER TABLE recentmembers_8 add have_children tinyint unsigned not NULL
default 0'. Default database: 'ff_recent' | 0| 346296822
| 369493795
 
 
Perror says 
Error code  17:  File exists
 
I have 2 alters submitted on the master in sequential order. On the
slave it executes in the same order but produces error 17. Somehow the
tempfile already exists, but when I go to the datadir this temp file is
not displayed in the directory. 
 
Performing a slave stop slave start does not work.
Restarting the mysql server on the slave is the only method I found to
recover from the error.
 
 
Version of mySQL: 4.0.15
Kernel: Linux ef242.friendfinderinc.com 2.4.18-4smp #1 SMP Sun Apr 14
13:50:35 PDT 2002 i686 unknown 
*PATCHED*
 
 
- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688
 


InnoDB errors

2003-10-24 Thread Shane Allen
I've searched the archives and was unable to find anything that seemed 
pertinent.

Earlier today I had to stop and start mysql. When I issued mysqladmin 
shutdown, however, the process did not die cleanly (reason unknown), and I 
ended up having to kill -9 mysqld and mysqld_safe.

When the DB came back up, I now have the following in my log:

031024 11:06:04  mysqld started
031024 11:06:05  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 697473956
InnoDB: Doing recovery: scanned up to log sequence number 0 697473956
031024 11:06:06  InnoDB: Flushing modified pages from the buffer pool...
031024 11:06:06  InnoDB: Started
031024 11:06:06  Found an entry in the 'db' table with empty database name; 
Skipped
031024 11:06:06  Found an entry in the 'db' table with empty database name; 
Skipped

...

031024 11:07:53  mysqld started
031024 11:07:54  InnoDB: Started
031024 11:07:54  Found an entry in the 'db' table with empty database name; 
Skipped
031024 11:07:54  Found an entry in the 'db' table with empty database name; 
Skipped

as you can see, on the second restart, I did not get warnings about having 
to recover the tables, but I still get the 'empty database name' errors. 
How can I determine what the cause of this error is an get it fixed?

Thanks in advance.

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


Case statement

2003-10-24 Thread Chris Boget
I'm trying to do something like the following:

SELECT 
CASE status 
WHEN
IN ( 'BR', 'VQ', 'RQ', 'NG' )
THEN
'binding' 
WHEN
IN ( 'NA', 'NG', 'RA' )
THEN
'approving'
END as action
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR 
 ( status IN ( 'NA', 'NG', 'RA' )));

But this query is obviously going to fail.  But there _has_ to be
a more elegant way than doing:

WHEN
'BR'
THEN
'binding' 
WHEN
'VQ'
THEN
'binding' 
...

Is there?  If so, I can't figure out what it would be.  Any suggestions?

thnx,
Chris
-mysql,query,blah



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



Re: SUBSELECT QUESTION?

2003-10-24 Thread Mojtaba Faridzad
First of all, you query is not correct and this is not a subselect query.
you can try this:

SELECT Salary, Level, Department FROM SalaryInfo ORDER BY Salary DESC LIMIT
1;

second of all, for subselect MySQL 4.1 can help you.

Mojtaba

- Original Message - 
From: "Prashant Pai" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, October 24, 2003 12:00 PM
Subject: SUBSELECT QUESTION?


> Hi,
>
> I have a table SalaryInfo as below
>
> Salary | Department | Level
> 50 | R&D| Director
> 3  | Maintenance| Groundsman
> ...
>
> I want to know what level in each department makes the highest salary
> and how much that salary is? Something like:
>
> SELECT Salary, Level, Department FROM SalaryInfo WHERE
> Salary=Max(Salary)
>
> Would using MySQL 4.1 that has support for nested select help my case?
>
> Thanks in advance
> prashant
>
> -- 
> 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 Support Contract

2003-10-24 Thread Alvaro Avello
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi guys , I'm  sorry for this  very OT post , but i guess that someone
in the list can help me . I've made contact with a sales representant of
mysql to purchase a mysql support contract , but the communication seems
to be a little slow ( my company is in Chile - South America ). In fact
, I already know what kind of support i want to pay, but , i still can't
contact him to finish the transaction ( this is a kind of non OT thing
in a SQL list  :-) ) There's any chance that someone could give me a
name or a mail where i can send my info and finish this ?
Thanks in Advance ( And sorry for my poor english )

Saludos / Regards,

Alvaro Avello.

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Netscape - http://enigmail.mozdev.org
iD8DBQE/mWRgR9NZaw5tbc0RAoc8AJ992nQRYLnVpnsuQBi7h+EGKE7pjQCfXFtG
nxo+nX+l55JREqcLaPl9Lbk=
=Zyd8
-END PGP SIGNATURE-


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


Space efficiency for values repeated across rows

2003-10-24 Thread Greg Kuperberg
I am working on a mysql-based search facility.  In order to search for
connected phrases, it would be very convenient to have a "subtable"
structure to express a set of rows in which only one small column changes.

If I didn't have to worry about space, I might have a table like this:

create table windex (
wordnum int unsigned not null,
docnum int unsigned not null,
pos smallint unsigned not null,
[various keys]);

Then to search for a phrase like "pork and beans", I would have a
query like:

select docnum from windex as a,windex as b,windex as c
where a.wordnum = 7 and b.wordnum = 10 and c.wordnum = 20
and a.docnum = b.docnum and b.docnum = c.docnum
and c.pos = a.pos+2 and c.pos = b.pos+1

(This is assuming that "pork" = 7, "and" = 10, and "beans" = 20.) Note
that this select statement illustrates a nuisance of the syntax for
multiple joins.  AFAIK, I have to say x = y and y = z, when I would like
to say x = y = z.  Or if it is not possible to interpret such a phrase
Python-style, I would like to have a function like equal(x,y,z).

Anyway, the syntax of this query is not the main point.  The real
problem is that the above table requires ten bytes for each occurrence
of each word in each document, plus significant extra B-tree structure.
Not only would it require more disk space than I will probably have,
it would also make searches highly disk-intensive. So instead I plan to
have a table like this:

create table windex (
wordnum int unsigned not null,
docnum int unsigned not null,
positions blob not null,
[various keys]);

Now I can encode the positions of each word in each document as two
bytes per occurrence.  The drawback is that to search for "pork and
beans", I have to retrieve all occurrences of pork AND and AND beans,
and then inspect each document for a position match.  This is not ideal,
but it looks like the best option at the moment.

The ideal solution would be an intelligent record structure that omits
wordnum and docnum when they repeat.  I realize that this looks a lot
like a MyISAM compressed table.  But I can't use that because my search
index will change incrementally every day.

I also see that MySQL has its own full-text search function. Well, my
project has some special requirements in regard to both search semantics
and scalability. I don't think that I could make do with just a simple
wrapper for MySQL's full-text search. Maybe I could exploit the full-text
search function for the narrow problem of finding a connected phrase.
It would be nice to know the data structures and algorithms for
the full-text search facility.

-- 
  /\  Greg Kuperberg (UC Davis)
 /  \
 \  / Visit the Math ArXiv Front at http://front.math.ucdavis.edu/
  \/  * All the math that's fit to e-print *

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



RE: Auditing MySQL (EAI)

2003-10-24 Thread Dathan Vance Pattishall
I am working on a Veritas backup solution where the data can exist on a
remote file system and the snapshot exist elsewhere. This could be used
in theory to attain your goal, with some additional code.



- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: Stephan Stapel [mailto:[EMAIL PROTECTED]
-->Sent: Friday, October 24, 2003 2:11 AM
-->To: [EMAIL PROTECTED]
-->Subject: Auditing MySQL (EAI)
-->
-->Dear list,
-->
-->I currently have the task to write an adapter for MySQL (for SapDB as
-->well
-->btw) that should track changes on certain tables.
-->I know that this question has been asked several times before but
-->unfortunately didn't find no suitable answer:
-->Is it possible to monitor/ audit changes in certain tables (create,
-->update,
-->delete) with MySQL and e.g. raise external programs?
-->If it's not possible now, does anyone know of plans to integrate such
a
-->feature in one of the future versions of MySQL?
-->
-->Kind regards,
-->
-->Stephan
-->
-->--
-->NEU FÜR ALLE - GMX MediaCenter - für Fotos, Musik, Dateien...
-->Fotoalbum, File Sharing, MMS, Multimedia-Gruß, GMX FotoService
-->
-->Jetzt kostenlos anmelden unter http://www.gmx.net
-->
-->+++ GMX - die erste Adresse für Mail, Message, More! +++
-->
-->
-->--
-->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: XML support for MySQL

2003-10-24 Thread Dathan Vance Pattishall
There is a project that was started to do this though. Do a google
search, on master thesis XML and MYSQL. Maybe they have some code that
can be contributed or you can use for a private branch.

In the mean time it's easy to generate XML on a simple DTD in side your
code.

- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
-->Sent: Friday, October 24, 2003 1:17 AM
-->To: Prasad Budim Ram
-->Cc: [EMAIL PROTECTED]
-->Subject: Re: XML support for MySQL
-->
-->On Mon, Oct 13, 2003 at 11:45:33PM -0600, Prasad Budim Ram wrote:
-->> Is it possible to direct xml support for queryiing a SQL statement
and
-->> get the result in also in xml?
-->
-->Nope.
-->--
-->Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
--><[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
-->
-->MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,533,760,097 queries
-->(440/sec. avg)
-->
-->--
-->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: Help obtaining DBI binaries

2003-10-24 Thread Dathan Vance Pattishall
www.cpan.org

or 

www.rpmfind.net comes in a package.

Most perl distributions have this installed by default.


- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: Daniel [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, October 23, 2003 7:20 PM
-->To: '[EMAIL PROTECTED]'
-->Subject: Help obtaining DBI binaries
-->
-->Please help, where do I download DBI module binaries
-->thanks
-->Daniel
-->
-->--
-->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: question.

2003-10-24 Thread Dan Greene
You probably want to restructure your tables (if that's an option)

 if it's a heirachal menu system (one parent only), then you'll need to put a 
parent_id column, and then map each child to the parent.

then once you have your parent menu (158), you select * from menus where parent_id = 
158


If it's a cross-menu system (multiple parents per item), you'll need another table, 
say menu_relations (source_id, item_id) and put a row in there for each relationship.  
then you select * from menus, menu_relations where id = item_id and source_id = 158)

Dunno if this helps

> -Original Message-
> From: Craig Harding [mailto:[EMAIL PROTECTED]
> Sent: Friday, October 24, 2003 12:36 PM
> To: [EMAIL PROTECTED]
> Subject: question.
> 
> 
> I have a table (menus) with the following fields:
> 
> TABLE: MENUS
> 
> idint(11)
> url   varchar(100)
> items varchar(35)
> 
> An example row:
> 
> IDURL ITEMS
> 158   programs/graduate   21,22,23,24,25,26,27,28,160
> 
> I want to select the ITEMS and then do another subselect that 
> can uses 
> each ITEM as the ID in the subselect.
> 
> 'select items from menus where id = 158' returns:
> 
> 21,22,23,24,25,26,27,28,160
> 
> Each of these ITEMS is an id in the table also. I want to get 
> each row 
> that corresponds to id = 21, 22, 23, 24, 25 ...
> 
> I know how to do this in php, but I need to do this on the 
> command line. 
> Remember that ITEMS is a varchar type, so I'm not sure if 
> there's a way 
> to do some kind of loop within this to select the items or create the 
> long query string with OR id = 21 OR id = 22 OR id = 23 ... which I 
> started to do:
> 
> select CONCAT('id = ', substring(items,1,LOCATE(',', items, 
> 1)-1), ' OR 
> id = ', substring(items, 4,locate(',', items,2)-1)) AS STR from menus 
> where items != '' and id = 158;
> 
> but this can be pretty long since it only grabs the first two digits 
> from ITEMS!
> 
> The ITEMS always has NO spaces between commas and numbers and some 
> numbers may be single digit, two digits or even three in the row.
> 
> thanks in advance,
> 
> craig.
> 
> 
> -- 
> 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]



4.1 in production

2003-10-24 Thread sean peters
Hey all,
are people using mysql 4.1.0 of 4.1.1 on production servers yet, or should we 
wait for a more stable release?

i know this is somewhat subjective to the use.

I have a major upgrade to my database system coming soon, and i want to 
coordinate it with my server upgrade, so im trying to figure out if it is 
wise to upgrade mysql yet.

thanks much,
sean peters
[EMAIL PROTECTED]


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



Re: replication redirector

2003-10-24 Thread Benjamin KRIEF
hi Jeremy , thanks (again, tell me if it's to much=) for helping me.

i've got to admit here that i'm reaching the maximum of my linux skills.

i've used top,free,iostat and sar , without finding the answers i need.

for instance , on the master (dual P3 1Ghz , 1G ram, running only mysql,
with 353Mo of data):


uptime :
--
18:46:44 up 123 days, 23:07,  1 user,  load average: 21.20, 14.22, 13.93

free   :
 totalusedfree  shared buffers  cached
Mem:901156  890080   11076   05860  731076
-/+ buffers/cache:  153144  748012
Swap:  1951888  226356 1725532

top :
--
 19:02:26 up 24 days, 17:54,  1 user,  load average: 2.12, 3.19, 2.84
418 processes: 416 sleeping, 2 running, 0 zombie, 0 stopped
CPU states:  58.1% user,   7.0% system,   0.0% nice,  34.9% idle
Mem:900084K total,   884928K used,15156K free,70076K buffers
Swap:   979956K total,   243256K used,   736700K free,   341272K cached

mytop  (thanks again =) :
--
MySQL on localhost (3.23.49-log)  up 29+10:33:22 [18:48:43]
 Queries Total: 13,593,786 Avg/Sec: 5.34  Now/Sec: 8.54  Slow: 189,308
 Threads Total: 356   Active: 27Cached: 0
 Key Efficiency: 99.93%  Bytes in: 1,328,945,210  Bytes out: 940,236,299

my.cnf extract:
--
set-variable= thread_stack=128K
set-variable= key_buffer=256M
set-variable= max_allowed_packet=1M
set-variable= table_cache=128
set-variable= sort_buffer=1M
set-variable= net_buffer_length=8K
set-variable= myisam_sort_buffer_size=8M
set-variable= max_connections=500

thanks to top , i can presume that the cpu's are ok :34.9% idle
(there's a "bug" in the debian procps , top show only one proc , but cat
/proc/cpuinfo show me the 2 cpu , so they are both effectively used by the
system)

i'm asking myself : why is the kernel using so much cache (730M).
shouldn't he (AFAIK) gives this space to mysql instead of swapping (226M) ?

then i think maybe he uses the cache to manage the swap space. but heh ,
730M for cache , and only 153 for the applications !

where am i wrong? (let's stay humble : i'm wrong , not the linux kernel :)

do you think there is some simple things i could tune to improve the
system performance ?

(the slave is now ok , thanks to the horrible regexps system i use to
redirect part of the queries to the master =)

bye,
Ben.


*
Benjamin KRIEF * Directeur Technique *  IGUANE Studio
Tel:01.56.55.54.20 *  5-7-9 passage des Cloys
Fax:01.56.55.54.24*   75018 PARIS
Gsm:06.12.56.50.41 *  mailto:[EMAIL PROTECTED]
*

> On Fri, Oct 24, 2003 at 04:00:07AM +0200, Benjamin KRIEF wrote:
>> >
>> > Is your bottleneck CPU, I/O, or lock contention?
>> >
>> > Is your slave running on worse hardware than your master?
>>
>> my bottleneck is CPU , definitely. the load can go up to 35 in peak
>> hours.
>> no swapping occurs , my key efficiency is at 99,89 .
>
> The load average often has little relationship to the actual CPU
> utilization.  Is your CPU at 100% most of the time?  Or are your procs
> blocked on disk I/O.
>
> Jeremy
> --
> Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
> <[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
>
> MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,530,693,767 queries
> (439/sec. avg)
>
>




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



question.

2003-10-24 Thread Craig Harding
I have a table (menus) with the following fields:

TABLE: MENUS

id  int(11)
url varchar(100)
items   varchar(35)
An example row:

ID  URL ITEMS
158 programs/graduate   21,22,23,24,25,26,27,28,160
I want to select the ITEMS and then do another subselect that can uses 
each ITEM as the ID in the subselect.

'select items from menus where id = 158' returns:

21,22,23,24,25,26,27,28,160

Each of these ITEMS is an id in the table also. I want to get each row 
that corresponds to id = 21, 22, 23, 24, 25 ...

I know how to do this in php, but I need to do this on the command line. 
Remember that ITEMS is a varchar type, so I'm not sure if there's a way 
to do some kind of loop within this to select the items or create the 
long query string with OR id = 21 OR id = 22 OR id = 23 ... which I 
started to do:

select CONCAT('id = ', substring(items,1,LOCATE(',', items, 1)-1), ' OR 
id = ', substring(items, 4,locate(',', items,2)-1)) AS STR from menus 
where items != '' and id = 158;

but this can be pretty long since it only grabs the first two digits 
from ITEMS!

The ITEMS always has NO spaces between commas and numbers and some 
numbers may be single digit, two digits or even three in the row.

thanks in advance,

craig.

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


SUBSELECT QUESTION?

2003-10-24 Thread Prashant Pai
Hi,

I have a table SalaryInfo as below

Salary | Department | Level
50 | R&D| Director
3  | Maintenance| Groundsman
...

I want to know what level in each department makes the highest salary
and how much that salary is? Something like:

SELECT Salary, Level, Department FROM SalaryInfo WHERE
Salary=Max(Salary)

Would using MySQL 4.1 that has support for nested select help my case?

Thanks in advance
prashant

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



RE: Really slow query (compared with Visual FoxPro)

2003-10-24 Thread Dan Greene
Indexes are your friend.  Perhaps your best friend (as far as databases go)

---
To give light on your question... you can index any field... if the entries are 
distinct, it's called a 'unique index' which are the best kind to use.  Otherwise you 
have a 'non-unique index', which can also be handy

Think of indexes, like... well... book indexes... to prevent you from looking all over 
the book for an answer, you can find the exact page the topic is on (unique index), or 
if it's covered in multiple pages, you reduce your searching to just that list of 
pages (non-unique index).

Then there's compound indexes, which is an index on the value of two seperate fields 
(which can be unique, or non-unique).  

As a rule of thumb, you want indexes on your unique key (primary key) (call record id) 
Most db's make indexes automatically on keys you say are the primary key. Also on your 
most commonly searched fields (usually non-unique) such as foreign keys (call record 
id in your record notes table for example)

--


If you're that new to db design, I would recommend going out and looking for some 
books/sites on the subject... There have been a few mentioned on this mailing list 
recently (Michael Kofler and Paul Dubois), http://www.databaseanswers.com/, and 
"Database Design for Mere Mortals"

and finally Mike ([EMAIL PROTECTED]) mentioned the following online tutorials:

Try one of these MySQL tutorials:
http://www.mysql.com/doc/en/Tutorial.html
http://www.analysisandsolutions.com/code/mybasic.htm
http://www.devshed.com/Server_Side/MySQL
http://www.sqlcourse.com/
http://www.w3schools.com/sql/default.asp
http://www.juicystudio.com/tutorial/mysql/
http://www.justphukit.com/mysql/mysql-tutorials-1.php
http://sqlzoo.net/
http://www.troobloo.com/tech/mysql.shtml
http://perl.about.com/cs/mysql/index.htm
http://www.anu.edu.au/web/authors/mysql/manual_Tutorial.html

Reference:
http://www.mysql.com/doc/en/  (the MySQL manual is quite good too)
http://tiger.la.asu.edu/Quick_Ref/MySQL_QuickRef.pdf  (MySQL Quick 
Reference Card)

Related links
http://dmoz.org/Computers/Software/Databases/MySQL/Tutorials/



> -Original Message-
> From: "Héctor Villafuerte D." [mailto:[EMAIL PROTECTED]
> Sent: Friday, October 24, 2003 1:40 PM
> To: [EMAIL PROTECTED]
> Subject: Re: Really slow query (compared with Visual FoxPro)
> 
> 
> Chris Nolan wrote:
> 
> > Hi!
> >
> > Given VFP's internals (I have to support FoxPro 2.6 apps, 
> oh the pain 
> > of it all!),
> > MySQL should be capable of much better performance, considering the
> > only thing FoxPro has in terms of a possible advantage is Rushmore.
> >
> > Which indexes do you have on the tables in the query?
> >
> > Regards,
> >
> > Chris
> >
> 
> Indexes? hmmm... I knew those were useful for something :)
> In Visual FoxPro I don't use indexes for this table... so I didn't 
> considered them
> necessary in MySQL (now I think I need to get to the basics of RDBMS).
> This table I'm talking about is a CDR (call detail record), 
> so a record 
> represents
> a call from a given telephone. I'm surely wrong but, ain't the index 
> suppossed to be
> on a field with distinct entries?
> Does VFP automagically creates indexes depending on the query?
> As you can see, I would greatly appreciate any pointers to 
> some database 
> theory.
> Thanks again!
> Hector
> 
> 
> -- 
> 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: Really slow query (compared with Visual FoxPro)

2003-10-24 Thread Chris Nolan
Hi!

One thing that you have to remember is that FoxPro will always index 
some fields, while MySQL
will only ever index the primary key field automatically.

Indexes can be on fields that are distinct or not distinct. The only 
difference that exists is
the nature of the index, which is one of those details the RDBMS handles 
by itself.

VFP has Rushmore, which does a bunch of funky things. At the moment (and 
the MySQL
docs currently agree with me on this), MySQL only has one real weakness 
- it's optimiser.
If you consider this fact (Oracle's optimiser is really it's greatest 
strength for example), the
speed of MySQL is even greater an achievement. The great Monty himself 
names the
optimiser as amongst the hardest things to get right for an RDBMS. It's 
not to say that
the optimiser is bad, it's just to say that MySQL AB have quite a few 
things planned for
improvement in that particular area.

Regards,

Chris

Héctor Villafuerte D. wrote:

Chris Nolan wrote:

Hi!

Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain 
of it all!),
MySQL should be capable of much better performance, considering the
only thing FoxPro has in terms of a possible advantage is Rushmore.

Which indexes do you have on the tables in the query?

Regards,

Chris

Indexes? hmmm... I knew those were useful for something :)
In Visual FoxPro I don't use indexes for this table... so I didn't 
considered them
necessary in MySQL (now I think I need to get to the basics of RDBMS).
This table I'm talking about is a CDR (call detail record), so a 
record represents
a call from a given telephone. I'm surely wrong but, ain't the index 
suppossed to be
on a field with distinct entries?
Does VFP automagically creates indexes depending on the query?
As you can see, I would greatly appreciate any pointers to some 
database theory.
Thanks again!
Hector




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


Re: Really slow query (compared with Visual FoxPro)

2003-10-24 Thread "Héctor Villafuerte D."
Chris Nolan wrote:

Hi!

Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain 
of it all!),
MySQL should be capable of much better performance, considering the
only thing FoxPro has in terms of a possible advantage is Rushmore.

Which indexes do you have on the tables in the query?

Regards,

Chris

Indexes? hmmm... I knew those were useful for something :)
In Visual FoxPro I don't use indexes for this table... so I didn't 
considered them
necessary in MySQL (now I think I need to get to the basics of RDBMS).
This table I'm talking about is a CDR (call detail record), so a record 
represents
a call from a given telephone. I'm surely wrong but, ain't the index 
suppossed to be
on a field with distinct entries?
Does VFP automagically creates indexes depending on the query?
As you can see, I would greatly appreciate any pointers to some database 
theory.
Thanks again!
Hector

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


Re: Really slow query (compared with Visual FoxPro)

2003-10-24 Thread Chris Nolan
Hi!

Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain of 
it all!),
MySQL should be capable of much better performance, considering the
only thing FoxPro has in terms of a possible advantage is Rushmore.

Which indexes do you have on the tables in the query?

Regards,

Chris

Héctor Villafuerte D. wrote:

Hi all,
I'm migrating from Visual FoxPro (I'm a newbie).
On a daily basis I need to run this query on a table with a little 
more than
5 million records.

+ MS Visual FoxPro 6.0
select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos 
from traf_oper
   group by 1, 2, 3, 4 into table oper_grp
This took about 173 seconds (let's say 3 minutes)

+ MySQL 4.1.0-alpha-max-nt
mysql> create table oper_grp select tel, telefb, rutaentran, rutasalien,
   -> sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4;
Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --->>> 516 
minutes!
Records: 3301017  Duplicates: 0  Warnings: 0

Why is there such a big difference?! Any optimization tips I could 
follow?
Thanks in advance,
Hector




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


Really slow query (compared with Visual FoxPro)

2003-10-24 Thread "Héctor Villafuerte D."
Hi all,
I'm migrating from Visual FoxPro (I'm a newbie).
On a daily basis I need to run this query on a table with a little more 
than
5 million records.

+ MS Visual FoxPro 6.0
select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from 
traf_oper
   group by 1, 2, 3, 4 into table oper_grp
This took about 173 seconds (let's say 3 minutes)

+ MySQL 4.1.0-alpha-max-nt
mysql> create table oper_grp select tel, telefb, rutaentran, rutasalien,
   -> sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4;
Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --->>> 516 
minutes!
Records: 3301017  Duplicates: 0  Warnings: 0

Why is there such a big difference?! Any optimization tips I could follow?
Thanks in advance,
Hector
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


Re: MERGE tables error

2003-10-24 Thread Roger Baklund
* [EMAIL PROTECTED]
[...]
>ERROR 1016: Can't open file: 'new_master.MRG'. (errno: 143)
>
> All table structures are identical.

Maybe not..? :)

> Am I missing a basic error numbers page in the online docs?  I've found a
> few, but nothing comprehensive, like a page where I can enter an
> error number and find the detailed message, probable cause, recommdned
> action, etc.  The only thing that I have been able to find is:
> http://www.mysql.com/doc/en/Error-returns.html  which essentially
> confirms that an error 1016 is a problem opening a file.
>
> I am using MySQL version 3.23.56-nt on Windows2000.

Run the perror utility, located in the mysql\bin directory:

C:\mysql\bin>perror 143
Error code 143:  Unknown error
143 = Conflicting table definition between MERGE and mapped table

--
Roger


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



Re: MySQL 4.1 subqueries!

2003-10-24 Thread Victoria Reznichenko
Simon Gentile <[EMAIL PROTECTED]> wrote:
> in version 4.1 states it supports subqueries like
> 
> SELECT * FROM t1 WHERE (1,2,3) IN (SELECT a,b,c FROM
> t2);
> 
> it doesn't support 'IN'
> 
> [Dissertation] ERROR 1235: This version of MySQL
> doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Worked fine for me:

mysql> select * from t2 where (1,2,3) in (select a,b,c from t1);
+--+
| id   |
+--+
|2 |
|3 |
+--+
2 rows in set (0.02 sec)

Could you provide a test case?


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





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



Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1

2003-10-24 Thread Martijn Tonies
Hi Chris,

> I'm not the almighty Heikki, but I might be able to fill you in to some
> extent.

:-)

> Your first and third questions I cannot answer with certainty, but I'm
> pretty
> sure the answer is "no". Regarding the first one, I'm not too sure as to
> why you'd need
> it. Looking at the output of SHOW VARIABLES might help though.

Yeah, I have been looking at SHOW VARIABLES - but it isn't
exactly what I'm looking for.

> As for the second question, AUTOCOMMIT is able to be set at any time
> without a problem. Additionally, issuing BEGIN or it's workalikes (see the
> MySQL manual or the reference manual at www.innodb.com) will act
> in the same way as having AUTOCOMMIT set to 0.

Ugh - read across that one... Woops :-) Thanks for that.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird & MS SQL Server.
Upscene Productions
http://www.upscene.com


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



Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1

2003-10-24 Thread Eduardo D Piovesam
Heikki,

Thanks for clarifying, and as you see, I'm new to MySQL (came from Oracle).

I'll read all the docs first (I'm doing it right now). ;)

We're evaluating MySQL (InnoDB) on NetWare65, we'll move all our Oracle
servers (also, on NetWare) to it, and then start testing.

Currently we have only 2 problems : view + stored procedures, but we can do
some "workaround" because we have only a few of them.

Regards,
Eduardo

- Original Message -
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: "Eduardo D Piovesam" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, October 24, 2003 11:23 AM
Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1


> Eduardo,
>
> - Alkuperäinen viesti -
> Lähettäjä: "Eduardo D Piovesam" <[EMAIL PROTECTED]>
> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Lähetetty: Friday, October 24, 2003 6:17 PM
> Aihe: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
>
>
> > Hello Heikki, thanks for your reply.
> >
> > It was just my curiosity, with the MyISAM concept it's good for the OS,
> > because it'll cache only the tables most useds, and not "all" the
> tablespace
> > file.
>
> but InnoDB only caches the most used 16 kB pages in the tablespaces to the
> buffer pool. MyISAM does not cache .MYD file contents at all, but relies
on
> the OS file cache for them. The MyISAM key_buffer caches .MYI contents.
>
> > And how to administer the multiple tablespace support? I'm worried in
the
> > space unused by some tables, example:
> > - Multiple tablespace support active
> > - Minium tablespace size is 500MB (in the my.cnf)
>
> The minimum size for an .ibd file is 64 kB. It grows page by page to 1 MB,
> up to 32 MB in 1 MB chunks, and after that in 8 MB chunks. .ibd files are
> always 'auto-extending' tablespaces. You do not specify their size in
> my.cnf.
>
> > - 2 tables in the InnoDB database, table1 have only 10kb (few rows), and
> > table2 have 600MB
> >
> > Then you'll have:
> > - table1.ibd with 10kb or 500MB?
> > - 2 x table2.ibd files with 500MB each or
> > - Just one table2.ibd with 600MB or 1GB?
> >
> > Thanks again,
> > Eduardo
>
> Best regards,
>
> Heikki
> Innobase Oy
> http://www.innodb.com
> InnoDB - transactions, row level locking, and foreign keys for MySQL
> InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
> tables
>
>
> > - Original Message -
> > From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Friday, October 24, 2003 10:55 AM
> > Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
> >
> >
> > > Eduardo,
> > >
> > > to make the user interface simple, I decided to take the table per
file
> > > approach. Each .ibd file is internally a 'tablespace'.
> > >
> > > The simple approach I chose is similar to how MyISAM now works. I
> thought
> > it
> > > would be nice for current MySQL users.
> > >
> > > In Oracle, one can store several tables into a single named
tablespace,
> > and
> > > can also split indexes and data of a single table to separate
> tablespaces.
> > > Nothing prevents adding those features to InnoDB, too. It just
requires
> > new
> > > syntax in CREATE TABLE to specify these options.
> > >
> > > Best regards,
> > >
> > > Heikki
> > > Innobase Oy
> > > http://www.innodb.com
> > > InnoDB - transactions, row level locking, and foreign keys for MySQL
> > > InnoDB Hot Backup - hot backup tool for InnoDB which also backs up
> MyISAM
> > > tables
> > >
> > > ..
> > > From: "Eduardo D Piovesam" ([EMAIL PROTECTED])
> > > Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
> > >
> > >
> > > View this article only
> > > Newsgroups: mailing.database.myodbc
> > > Date: 2003-10-23 14:43:28 PST
> > >
> > > (Sorry for the last email, it's not complete).
> > >
> > > Hello Heikki,
> > >
> > > Sorry, but I didn't understand the concept of tablespace applied. It's
> > > different from Oracle, right?
> > >
> > > AFAIK, tablespace is utilized to logically group "tables" into one (or
> > more)
> > > files.
> > >
> > > And to group "indexes" into another files...
> > >
> > > But you said that the each table (with its indexes) will be in one
> file...
> > > is there an reason? Is it better than "split" tables and indexes?
> > >
> > > Thank you.
> > >
> > > Eduardo
> > >
> > >
> > > --
> > > 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: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1

2003-10-24 Thread Chris Nolan
Hi!

I'm not the almighty Heikki, but I might be able to fill you in to some 
extent.

Your first and third questions I cannot answer with certainty, but I'm 
pretty
sure the answer is "no". Regarding the first one, I'm not too sure as to 
why you'd need
it. Looking at the output of SHOW VARIABLES might help though.

As for the second question, AUTOCOMMIT is able to be set at any time
without a problem. Additionally, issuing BEGIN or it's workalikes (see the
MySQL manual or the reference manual at www.innodb.com) will act
in the same way as having AUTOCOMMIT set to 0.
Hope this helps!

Regards,

Chris

Martijn Tonies wrote:

Hi Heikki,

As you know - I'm a transaction zealot :-)

Is there a way to get a signal back to the client when a
transaction has started? Or perhaps something like
@@trancount in MSSQL?
Is there a way to turn ON/OFF auto-commit?

And, are metadata operations under transaction control?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird & MS SQL Server.
Upscene Productions
http://www.upscene.com
 



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


Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1

2003-10-24 Thread Heikki Tuuri
Eduardo,

- Alkuperäinen viesti - 
Lähettäjä: "Eduardo D Piovesam" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Lähetetty: Friday, October 24, 2003 6:17 PM
Aihe: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1


> Hello Heikki, thanks for your reply.
>
> It was just my curiosity, with the MyISAM concept it's good for the OS,
> because it'll cache only the tables most useds, and not "all" the
tablespace
> file.

but InnoDB only caches the most used 16 kB pages in the tablespaces to the
buffer pool. MyISAM does not cache .MYD file contents at all, but relies on
the OS file cache for them. The MyISAM key_buffer caches .MYI contents.

> And how to administer the multiple tablespace support? I'm worried in the
> space unused by some tables, example:
> - Multiple tablespace support active
> - Minium tablespace size is 500MB (in the my.cnf)

The minimum size for an .ibd file is 64 kB. It grows page by page to 1 MB,
up to 32 MB in 1 MB chunks, and after that in 8 MB chunks. .ibd files are
always 'auto-extending' tablespaces. You do not specify their size in
my.cnf.

> - 2 tables in the InnoDB database, table1 have only 10kb (few rows), and
> table2 have 600MB
>
> Then you'll have:
> - table1.ibd with 10kb or 500MB?
> - 2 x table2.ibd files with 500MB each or
> - Just one table2.ibd with 600MB or 1GB?
>
> Thanks again,
> Eduardo

Best regards,

Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
tables


> - Original Message -
> From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, October 24, 2003 10:55 AM
> Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
>
>
> > Eduardo,
> >
> > to make the user interface simple, I decided to take the table per file
> > approach. Each .ibd file is internally a 'tablespace'.
> >
> > The simple approach I chose is similar to how MyISAM now works. I
thought
> it
> > would be nice for current MySQL users.
> >
> > In Oracle, one can store several tables into a single named tablespace,
> and
> > can also split indexes and data of a single table to separate
tablespaces.
> > Nothing prevents adding those features to InnoDB, too. It just requires
> new
> > syntax in CREATE TABLE to specify these options.
> >
> > Best regards,
> >
> > Heikki
> > Innobase Oy
> > http://www.innodb.com
> > InnoDB - transactions, row level locking, and foreign keys for MySQL
> > InnoDB Hot Backup - hot backup tool for InnoDB which also backs up
MyISAM
> > tables
> >
> > ..
> > From: "Eduardo D Piovesam" ([EMAIL PROTECTED])
> > Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
> >
> >
> > View this article only
> > Newsgroups: mailing.database.myodbc
> > Date: 2003-10-23 14:43:28 PST
> >
> > (Sorry for the last email, it's not complete).
> >
> > Hello Heikki,
> >
> > Sorry, but I didn't understand the concept of tablespace applied. It's
> > different from Oracle, right?
> >
> > AFAIK, tablespace is utilized to logically group "tables" into one (or
> more)
> > files.
> >
> > And to group "indexes" into another files...
> >
> > But you said that the each table (with its indexes) will be in one
file...
> > is there an reason? Is it better than "split" tables and indexes?
> >
> > Thank you.
> >
> > Eduardo
> >
> >
> > --
> > 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: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1

2003-10-24 Thread Eduardo D Piovesam
Hello Heikki, thanks for your reply.

It was just my curiosity, with the MyISAM concept it's good for the OS,
because it'll cache only the tables most useds, and not "all" the tablespace
file.

And how to administer the multiple tablespace support? I'm worried in the
space unused by some tables, example:
- Multiple tablespace support active
- Minium tablespace size is 500MB (in the my.cnf)
- 2 tables in the InnoDB database, table1 have only 10kb (few rows), and
table2 have 600MB

Then you'll have:
- table1.ibd with 10kb or 500MB?
- 2 x table2.ibd files with 500MB each or
- Just one table2.ibd with 600MB or 1GB?

Thanks again,
Eduardo


- Original Message -
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, October 24, 2003 10:55 AM
Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1


> Eduardo,
>
> to make the user interface simple, I decided to take the table per file
> approach. Each .ibd file is internally a 'tablespace'.
>
> The simple approach I chose is similar to how MyISAM now works. I thought
it
> would be nice for current MySQL users.
>
> In Oracle, one can store several tables into a single named tablespace,
and
> can also split indexes and data of a single table to separate tablespaces.
> Nothing prevents adding those features to InnoDB, too. It just requires
new
> syntax in CREATE TABLE to specify these options.
>
> Best regards,
>
> Heikki
> Innobase Oy
> http://www.innodb.com
> InnoDB - transactions, row level locking, and foreign keys for MySQL
> InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
> tables
>
> ..
> From: "Eduardo D Piovesam" ([EMAIL PROTECTED])
> Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
>
>
> View this article only
> Newsgroups: mailing.database.myodbc
> Date: 2003-10-23 14:43:28 PST
>
> (Sorry for the last email, it's not complete).
>
> Hello Heikki,
>
> Sorry, but I didn't understand the concept of tablespace applied. It's
> different from Oracle, right?
>
> AFAIK, tablespace is utilized to logically group "tables" into one (or
more)
> files.
>
> And to group "indexes" into another files...
>
> But you said that the each table (with its indexes) will be in one file...
> is there an reason? Is it better than "split" tables and indexes?
>
> Thank you.
>
> Eduardo
>
>
> --
> 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]



SV: SV: SV: GRANT problem

2003-10-24 Thread Datatal AB - Gauffin, Jonas
> Use  'localhost' as the host unless you are specifying the 
> grant to be 
> for users on another machine.
> '%'  stands for all machines except 'localhost'.

I know. And the is exactly what I want.

> 
> Datatal AB - Gauffin, Jonas wrote:
> 
> >I get access denied (from mysql.exe and myodbc) when 
> specifying no host 
> >or % as host in the grant statement.
> >
> >  
> >
> >>-Ursprungligt meddelande-
> >>Från: gerald_clark [mailto:[EMAIL PROTECTED]
> >>Skickat: den 24 oktober 2003 15:11
> >>Till: Datatal AB - Gauffin, Jonas
> >>Kopia: [EMAIL PROTECTED]
> >>Ämne: Re: SV: GRANT problem
> >>
> >>
> >>What do you mean by ' doesn't work ' ?
> >>Have you tried connecting from another machine?
> >>
> >>Datatal AB - Gauffin, Jonas wrote:
> >>
> >>
> >>
> GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO @'%' 
> IDENTIFIED 
> BY ''; FLUSH PRIVILEGES;
> 
>    
> 
> 
> 
> >>>yes. no diffrence. Specifying '%' or no host doesn't work for me.
> >>>
> >>> 
> >>>
> >>>  
> >>>
> >>
> >>
> >>
> >
> >
> >  
> >
> 
> 
> 
> 

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



Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1

2003-10-24 Thread Martijn Tonies
Hi Heikki,

As you know - I'm a transaction zealot :-)

Is there a way to get a signal back to the client when a
transaction has started? Or perhaps something like
@@trancount in MSSQL?

Is there a way to turn ON/OFF auto-commit?

And, are metadata operations under transaction control?


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird & MS SQL Server.
Upscene Productions
http://www.upscene.com


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



Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1

2003-10-24 Thread Heikki Tuuri
Eduardo,

to make the user interface simple, I decided to take the table per file
approach. Each .ibd file is internally a 'tablespace'.

The simple approach I chose is similar to how MyISAM now works. I thought it
would be nice for current MySQL users.

In Oracle, one can store several tables into a single named tablespace, and
can also split indexes and data of a single table to separate tablespaces.
Nothing prevents adding those features to InnoDB, too. It just requires new
syntax in CREATE TABLE to specify these options.

Best regards,

Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
tables

..
From: "Eduardo D Piovesam" ([EMAIL PROTECTED])
Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1


View this article only
Newsgroups: mailing.database.myodbc
Date: 2003-10-23 14:43:28 PST

(Sorry for the last email, it's not complete).

Hello Heikki,

Sorry, but I didn't understand the concept of tablespace applied. It's
different from Oracle, right?

AFAIK, tablespace is utilized to logically group "tables" into one (or more)
files.

And to group "indexes" into another files...

But you said that the each table (with its indexes) will be in one file...
is there an reason? Is it better than "split" tables and indexes?

Thank you.

Eduardo


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



Re: SV: SV: GRANT problem

2003-10-24 Thread gerald_clark
Use  'localhost' as the host unless you are specifying the grant to be 
for users on another machine.
'%'  stands for all machines except 'localhost'.

Datatal AB - Gauffin, Jonas wrote:

I get access denied (from mysql.exe and myodbc) when specifying no host or % as host in the grant statement.

 

-Ursprungligt meddelande-
Från: gerald_clark [mailto:[EMAIL PROTECTED] 
Skickat: den 24 oktober 2003 15:11
Till: Datatal AB - Gauffin, Jonas
Kopia: [EMAIL PROTECTED]
Ämne: Re: SV: GRANT problem

What do you mean by ' doesn't work ' ?
Have you tried connecting from another machine?
Datatal AB - Gauffin, Jonas wrote:

   

GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO @'%'
IDENTIFIED BY ''; FLUSH PRIVILEGES;
  

   

yes. no diffrence. Specifying '%' or no host doesn't work for me.



 

   



 



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


Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1

2003-10-24 Thread Heikki Tuuri
Chris,

1. Peter Zaitsev ran some tests which suggested that multiple .ibd files are
not slower than one ibdata file. But it will depend on the OS version. Some
OS'es may have bad fsync() implementation, which can cause slowdown with a
large number of files.

2. Clustering? If you mean the fact that InnoDB stores the row data in the
PRIMARY KEY index (= clustered index), that is also true for .ibd files. The
only thing that changes is the file, not internal storage formats of tables.

Best regards,

Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
tables


From: Chris Nolan ([EMAIL PROTECTED])
Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1


View this article only
Newsgroups: mailing.database.myodbc
Date: 2003-10-22 09:19:19 PST

Hello Heikki!!!

Multiple table spaces eh? Funky! Of course, as you've no doubt become
aware, I am one of these annoying
people that has many questions to ask the list the moment something like
this comes along! Please feel
free to ignore these questions though, as we're all hanging out for 4.1.1.

1. Are there any performance implications (either way) with the use of
multiple table spaces? Are there any
other implications that you think are worthy of note (other than the
obviously easier backup options).
2. Is there any way that clustering can still happen with multiple table
spaces enabled?

Best regards,

Chris


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



Re: Optimizing table (so-called VACUUM on postgreSQL) and InnoDB

2003-10-24 Thread Heikki Tuuri
All,

the VACUUM in PostgreSQL removes delete-marked records from the database.

In InnoDB, the VACUUM is called the purge. In InnoDB purge happens
automatically in the background, the user does not need to explicitly run
VACUUM, like in PostgreSQL. Also in Oracle purge is automatic.

The way to compact, reorganize, or optimize, an InnoDB table is to run

ALTER TABLE tablename TYPE=InnoDB;

It rebuilds the table.

The command

ANALYZE TABLE tablename;

in InnoDB updates the table statistics for the SQL optimizer. InnoDB
constantly updates the row count estimate, but only updates the index
cardinality estimates for join optimization at a mysqld startup, or when
ANALYZE TABLE is called. This is similar to MyISAM. But remember that in
InnoDB the estimates are not precise, but based on 8 random dives into the
index trees.

Best regards,

Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
tables


From: Chris Nolan ([EMAIL PROTECTED])
Subject: Re: Optimizing table (so-called VACUUM on postgreSQL) and InnoDB
View: Complete Thread (3 articles)
Original Format
Newsgroups: mailing.database.myodbc
Date: 2003-10-24 00:35:58 PST

VACUUM is basically a workalike for:

ALTER TABLE  TYPE=3DInnoDB;

There are various types of VACUUM commands as well. VACUUM FULL basically
locks the table and shrinks the on-disk table space. The normal "VACUUM"
(normal since 7.2 I think), does not lock the table (similar to REORGANISE
in some other databases, while REORGANISE in some databases like SQLBase
REORGANISE locks everything) but does a basic defrag.

Hope this helps!

Regards,

Chris

On Fri, 24 Oct 2003 05:23 pm, Jeremy Zawodny wrote:
> On Thu, Oct 16, 2003 at 05:13:58PM -0300, Leonardo Rodrigues wrote:
> > Hello all,
> >
> > The referencial integrity control don=B4t apply to a simple table, do
> > it?
>
> What's a "simple" table?
>
> > My DB is growing, and growing, and I=B4m very concerned about my host
> > limitions of disk usage. So...
>
> You should be!
>
> > Another question: postgreeSQL has a SQL command called VACUUM, which
> > optimize and reduce the table=B4s used space disk. I discover for mySQL=
the
> > commands "OPTIMIZE TABLE" and "ANALYZE TABLE", but both don=B4t run on
> > InnoDB.
>
> ANALYZE table runs on InnoDB.
>
> > My question are: are these commands similar to VACUUM? What=B4s the
> > difference? How can I run on InnoDB?
>
> I don't know much about PostgreSQL, so I'm not sure.  If you describe
> what VACUUM does, I may be able to help explain how to do the same
> thing with MySQL.
>
> Jeremy
> --
> Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
> <[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
>
> MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,530,867,850 queries
> (439/sec. avg)


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



Re: ALTER TABLE semantics

2003-10-24 Thread Heikki Tuuri
Chris,

for MyISAM and InnoDB, ALTER TABLE normally locks the table to be altered in
a read-only mode.

Best regards,

Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
tables


...
From: Chris Nolan ([EMAIL PROTECTED])
Subject: ALTER TABLE semantics
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.myodbc
Date: 2003-10-24 00:39:40 PST

Hi all,

Does anyone have a list of what affect ALTER TABLE has on each different
type
of MySQL table?

If I have read correctly, ALTER TABLE locks MyISAM tables until completeion.
Am I right on this? How about InnoDB and BDB? If memory serves correctly,
HEAP doesn't support ALTER TABLE.

Regards,

Chris


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



mysql 4.1 installation problems

2003-10-24 Thread phani
Hi,

  I have mysql 4.0 installed on redhat 9.0.I want to install mysql 4.1 and 
keep 4.0 as it is.I get the follownig message whent I try to install 4.1


mysql_install_db : line 166:0:command not found.
Installing privilege tables
mysqld :ERROR :unknown option --log-bin # required for replication.

mysql_install_db : line 443 :9596 broken pipe ...

how do I fix this problem
thanx
phani



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



SV: SV: GRANT problem

2003-10-24 Thread Datatal AB - Gauffin, Jonas
I get access denied (from mysql.exe and myodbc) when specifying no host or % as host 
in the grant statement.

> -Ursprungligt meddelande-
> Från: gerald_clark [mailto:[EMAIL PROTECTED] 
> Skickat: den 24 oktober 2003 15:11
> Till: Datatal AB - Gauffin, Jonas
> Kopia: [EMAIL PROTECTED]
> Ämne: Re: SV: GRANT problem
> 
> 
> What do you mean by ' doesn't work ' ?
> Have you tried connecting from another machine?
> 
> Datatal AB - Gauffin, Jonas wrote:
> 
> >>GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO @'%'
> >>IDENTIFIED BY ''; FLUSH PRIVILEGES;
> >>
> >>
> >>
> >yes. no diffrence. Specifying '%' or no host doesn't work for me.
> >
> >  
> >
> 
> 
> 
> 

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



Re: Easiest way to output to a Latex2e tabular format???

2003-10-24 Thread gerald_clark
You would be better off getting a  good MySQL book, a good Perl book, 
and a good Latex book.
Spend a week or two reading, try the examples in the books, and 
integrate what you have learned.
Then ask questions.

You want a very specific answer to a question you can't even express yet.

Abner Gershon wrote:

I am relatively new to mysql, latex, and linux in
general. I have created a small table of about one
hundred entries that I would like to print out as a
table in a latex document. The form of the latex file
needs to be something like:
"field1 & field2 & field3 \\ \hline"
As I am a novice please provide an example verbatim of
the command I need to enter to accomplish this.
Thank you.
-Abner
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
 



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


Can't connect to MySQL server on '[server]' (10061) - unix & tcp/ip sockets

2003-10-24 Thread Ben Darlow
Hi,

I've recently set up a new linux box and installed MySQL 4 (for the
record, Knoppix/Debian and the 'unstable' MySQL 4 package). I've had no
problems connecting using phpMyAdmin, but when I try to connect remotely
using MySQL Control Center or SQLyog (from Windows) I get the same error
(near enough) from both:

SQLyog:

---
SQLyog
---
Error No. 2003
Can't connect to MySQL server on '[server]' (10061)
---

MySQL Control Center:

[server] ERROR 2003: Can't connect to MySQL server on '[server]'
(10061)

Searching through the mysql.com documentation led me to
http://www.mysql.com/doc/en/Can_not_connect_to_server.html, which talks
about unix and tcp/ip sockets. Running mysqladmin version gives me the
following output:

mysqladmin  Ver 8.40 Distrib 4.0.13, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult
AB
This software comes with ABSOLUTELY NO WARRANTY. This is free
software,
and you are welcome to modify and redistribute it under the GPL
license

Server version  4.0.13-log
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 2 hours 6 min 24 sec

Threads: 3  Questions: 450  Slow queries: 0  Opens: 16  
Flush tables: 1  Open tables: 5  Queries per second avg: 0.059

Since the documentation (I forget where) showed the version output
showing the tcp port the server was running on (and mine doesn't), I am
led to believe the server isn't running on a tcp/ip socket. From what I
can tell though, my.cnf does have the necessary lines for doing this -
port=3306 (as per default) is in there, uncommented-out.

Can anyone suggest what I might need to change in my configuration to
enable tcp/ip connections? All the search results I've seen so far
appear only to relate to people running MySQL (server) under windows...

Cheers,
Ben Darlow

---Disclaimer---

Unless obviously public, this email is confidential to the intended recipient(s). If 
you received it in error please tell the sender and then delete it. We check emails 
from dyslexic.com and iansyst.co.uk, but you should virus check incoming emails. 
Emails do not always represent our official policy or a contract. Errors and omissions 
are excepted.

iANSYST Ltd, Fen House, Fen Road, CAMBRIDGE, CB4 1UN. T +44(0)1223 420101; Fax +44(0) 
1223 42 66 44; [EMAIL PROTECTED]



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



Re: SV: GRANT problem

2003-10-24 Thread gerald_clark
What do you mean by ' doesn't work ' ?
Have you tried connecting from another machine?
Datatal AB - Gauffin, Jonas wrote:

GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO @'%' 
IDENTIFIED BY ''; FLUSH PRIVILEGES;

   

yes. no diffrence. Specifying '%' or no host doesn't work for me.

 



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


RE: MERGE tables error

2003-10-24 Thread Victor Pendleton
Error 143 suggests that you have conflicting table definitions.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, October 24, 2003 7:08 AM
To: [EMAIL PROTECTED]
Subject: MERGE tables error


I've created several merge tables from the command prompt.  All seems well, 
the .frm and .MRG files are created.  The contents of the .MRG files are 
accurate.  

I flushed the tables (even restarted MySQL).  

I can see the newly created merge tables, but when I try to run a DESCRIBE
or 
a SELECT COUNT(*)  to verify the records counts, I get the following 
error message:

   ERROR 1016: Can't open file: 'new_master.MRG'. (errno: 143)

All table structures are identical.  

Am I missing a basic error numbers page in the online docs?  I've found a 
few, but nothing comprehensive, like a page where I can enter an error
number and 
find the detailed message, probable cause, recommdned action, etc.  The only

thing that I have been able to find is:  
http://www.mysql.com/doc/en/Error-returns.html  which essentially confirms
that an error 1016 is a problem opening 
a file.

I am using MySQL version 3.23.56-nt on Windows2000.

Any help, links or guidance would be greatly appreciated.

/Tony

-- 
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]



MERGE tables error

2003-10-24 Thread Tbird67ForSale
I've created several merge tables from the command prompt.  All seems well, 
the .frm and .MRG files are created.  The contents of the .MRG files are 
accurate.  

I flushed the tables (even restarted MySQL).  

I can see the newly created merge tables, but when I try to run a DESCRIBE or 
a SELECT COUNT(*)  to verify the records counts, I get the following 
error message:

   ERROR 1016: Can't open file: 'new_master.MRG'. (errno: 143)

All table structures are identical.  

Am I missing a basic error numbers page in the online docs?  I've found a 
few, but nothing comprehensive, like a page where I can enter an error number and 
find the detailed message, probable cause, recommdned action, etc.  The only 
thing that I have been able to find is:  
http://www.mysql.com/doc/en/Error-returns.html  which essentially confirms that an 
error 1016 is a problem opening 
a file.

I am using MySQL version 3.23.56-nt on Windows2000.

Any help, links or guidance would be greatly appreciated.

/Tony

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



Re: does mysql supports russion language or any other language?

2003-10-24 Thread Jean-Pierre Schwickerath
"Louie Miranda" <[EMAIL PROTECTED]> wrote:

> Hi, i have this form that goes directly.. to a database. I have some
> clients who dont use english. If they enter on a form a special
> russion or chinese character will mysql translate it properly?

If you use a Web form, you should try to transform the input with
something like htmlentities() in PHP. This will translate all non ASCII
characters to encoded full ASCII strings which will then be language
independent. 
On the other side, you might try to use mysql-4.1.x with UTF8 rows



Jean-Pierre

-- 
Powered by Linux From Scratch - http://schwicky.net/
PGP Key ID: 0xEE6F49B4 - AIM/Jabber: Schwicky - ICQ: 4690141

Nothing is impossible... Everything is relative!

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



Re: Simple contains query question

2003-10-24 Thread Egor Egorov
Dale Hans <[EMAIL PROTECTED]> wrote:
> I have a field in the table, call it 'stuff', and it contains a list 
> of values that are comma separated.  i.e.  'Books,DVDs,Video 
> Tapes,CDs'
> 
> I want to SELECT out all records that have an item.  I know IN works 
> the other way, so is there something that will get what I want like 
> this:
> 
> SELECT * FROM Inventory
> WHERE stuff CONTAINS 'Books'
> ORDER BY name
> 

Take a look at FIND_IN_SET() function:
http://www.mysql.com/doc/en/String_functions.html



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




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



Re: newbie: need example of create table syntax

2003-10-24 Thread Egor Egorov
"suwandy" <[EMAIL PROTECTED]> wrote:
> 
> i'm currently learning mysql and interesting to
> constraint, relational and check function.
> 
> could anyone give some example please ?

CHECK clause is parsed but it doesn't do anything. 

Info about FOREIGN KEY CONSTRAINTS you can find at:
http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




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



Re: Saving Binary Files to mysql

2003-10-24 Thread Egor Egorov
Timotius Alfa <[EMAIL PROTECTED]> wrote:
> 
> How to save binary files to mysql ? I want to save *.exe whole in a binary files, 
> after that I want to save it to mysql. thanks 
> 

You can store binary data in the BLOB columns:
http://www.mysql.com/doc/en/BLOB.html



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




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



Re: MATCH ... AGAINST('...' WITH QUERY EXPANSION) syntax?

2003-10-24 Thread Sergei Golubchik
Hi!

On Oct 23, Matt W wrote:
> Hi Sergei,
> 
> More full-text questions from me since I just noticed your code and doc
> changes. :-)
> 
> What does this new WITH QUERY EXPANSION syntax do? More relevant
> results? More flexible? Faster? Is it for NLQ, boolean, or both (since
> both ft_[nlq | boolean]_search.c are changed)? Does it have something to
> do with 2 level indexes, or aren't they being used yet? Sorry for all
> the questions!

First - it's not pushed yet :)
Then - no it does not have anything to do with 2 level indexing, it's
for NLQ only, slower, unrelated, yes.

The idea - well known in Information Retrieval science - basically is to
perform a search, take top N documents, add them to the query, and redo
the search.

It is expected to improve results for short queries (short query text in
AGAINST). I said "expected" because all test collections that I have use
very long queries, so though query expansion did increase recall
significantly, overall results were worse.

So, I need to get test collections with short queries and tune the
algorithm somewhat.

And yes, it makes the search slower - sometimes noticeably
slower. This will also be fixed, when I'll implement so called "unsafe"
optimization for NL search. It is this optimization that relies on 2-level
index structure, but, again, I need to get new test collections to do
it, to adjust thresholds for best results/speed.

> Also noticed that ft_max_word_len_for_sort has become a constant,
> instead of run-time definable, and ft_query_expansion_limit "replaces"
> it, though they don't sound related. I'm wondering about
> max_..._for_sort because, at least in 4.0, I need to lower it to 10-12
> to keep the temp files smaller when building the index. :-( Are the temp
> files going to get too big in 4.1 when I can't adjust
> ft_max_word_len_for_sort or is the algorithm different when indexing? If
> the temp files are the same size as 4.0, I wish ft_max_word_len_for_sort
> would be restored or I'm going to have problems. :-(

I removed it because I thought it's too complex and is never used - so
it's better to remove it for not to confuse users and keep number of
variables manageable. If I'm wrong here - I'll put it back, of course :)

Making it 10-12 to keep temp files smaller - you will not need it in
4.1, as 4.1 uses strlen(word)+const bytes per word in temp file, not
ft_max_word_len_for_sort bytes. (By the way, this nice feature
applies to normal indexes on VARCHAR/CHAR columns too :).

So, the main reason for ft_max_word_len_for_sort (reduce i/o
significantly - from 255 to 20 bytes/word) was removed.

Another reason still applies (that's why it's defined to be 20, and not
255) - in memory each word occupies ft_max_word_len_for_sort bytes, so
the smaller this value the more words will fit in one "sort chunk".
But it doesn't impact performance as much as i/o.

Words that are longer than ft_max_word_len_for_sort will not be added to
index during repair_by_sort - so they have to be added later using old
and slow method (simply inserted into b-tree).

The way to choose this value properly is to run "ft_dump -l"
and set that threshold so that almost all the words are shorter,
but no memory is wasted on few extra long words. 20 is almost always
an ok value here. 

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]



[ANN] Book: The Definitive Guide to MySQL, 2nd ed

2003-10-24 Thread Michael Kofler
I would like to announce the 2nd edition of

  The Definitive Guide to MySQL
  by Michael Kofler
  apress 2004

The book covers many aspects of MySQL application development, including

- installation (Windows, Linux)
- GUIs (MySQL Control Center, phpMyAdmin)
- database design
- SQL
- security
- administration (backups, logging, replication ...)
- InnoDB, transactions, foreign key rules
- programming with PHP, Perl, Java, C, C++, VB6, VB.NET, C#

The book also contains a compact reference of SQL commands, mysql_xxx
options and some APIs (PHP, Perl, JDBC, C).

The book is up-to-date to MySQL 4.0.14 / MySQL 4.1 (snapshot from end of
July 2003).

For more information, a detailed TOC (PDF), a sample chapter (PDF) etc.
please have a look at:

http://www.kofler.cc/mysql/mysqlbook.html

Link to amazon:

http://www.amazon.com/exec/obidos/ASIN/1590591445/michaelkofler-20

Link to apress (publisher):

http://www.apress.com/book/bookDisplay.html?bID=229





There is also a German edition of this book:

http://www.kofler.cc/mysql/mysqlbuch.html





Finally, for all who are considering a migration from Microsoft SQL Server
to MySQL, I wrote mssql2mysql, a free (GPL) script which might help you:

http://www.kofler.cc/mysql/mssql2mysql.html




Thank you for your time,

Michael Kofler


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



Re: Potential Bug with inserting nulls with MySQL 4.1.1 nightly snaps hot

2003-10-24 Thread Victoria Reznichenko
David Ritter <[EMAIL PROTECTED]> wrote:
> 
> I think I have found a bug in the MySQL 4.1.1 C Binding API with inserting
> NULL values using the bind API.  Can someone please send me an address that
> I can send a test case to the MySQL development staff so they can take a
> look at it?
> 
> Alternatively can someone tell me if this is the appropriate forum to post
> such items?
> 

You can report bug to the bugs database http://bugs.mysql.com/


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





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



Auditing MySQL (EAI)

2003-10-24 Thread Stephan Stapel
Dear list,

I currently have the task to write an adapter for MySQL (for SapDB as well
btw) that should track changes on certain tables.
I know that this question has been asked several times before but
unfortunately didn't find no suitable answer:
Is it possible to monitor/ audit changes in certain tables (create, update,
delete) with MySQL and e.g. raise external programs?
If it's not possible now, does anyone know of plans to integrate such a
feature in one of the future versions of MySQL?

Kind regards,

Stephan

-- 
NEU FÜR ALLE - GMX MediaCenter - für Fotos, Musik, Dateien...
Fotoalbum, File Sharing, MMS, Multimedia-Gruß, GMX FotoService

Jetzt kostenlos anmelden unter http://www.gmx.net

+++ GMX - die erste Adresse für Mail, Message, More! +++


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



Re: XML support for MySQL

2003-10-24 Thread Jeremy Zawodny
On Mon, Oct 13, 2003 at 11:45:33PM -0600, Prasad Budim Ram wrote:
> Is it possible to direct xml support for queryiing a SQL statement and
> get the result in also in xml?

Nope.
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,533,760,097 queries (440/sec. avg)

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



Off Topic: Translation Mysql Manual

2003-10-24 Thread carlos cardenas
Hello :P

Only one question. A friend translated the 6 chapter of mysql manual to
spanish. And I want know if this translate is posible or have any
problem with the mysql licence or mysql manual licence.

The translate is here:

http://amauta.ourproject.org/contrib/


Well.  



-- 
|  .---.   |http://www.unimauro.pe.kz |
|  |º_º|   |  Carlo Mauro Cárdenas Fernández  |
|  |:_/|   | irc.freenode.net: apesol tdlp-es |
| //   \\  |   irc.gnome.org: gnome-hispano   |
|(| |) |  telf: 051-001-4582877   |
|/'\_ _/'\ |   Jr.Las Cidras 664, Lima Peru   |
|\__) (__/ |Lo hacemos por diversion."Linus T"|
|__|__|


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



Error: 1016 Can't open file: 'tablename.MYI.

2003-10-24 Thread Marius Røstad
Hello!

 

I get this error from my MySQL-database:

Error: 1016 Can't open file: 'tablename.MYI.

(errno: 145).

 

I know what to do to fix this, but I need to know what causes this error.

If anyone know, please respond.

 

 

Best regards, Marius R

 

-

 

Marius Røstad

System Developer

Intrapoint AS

Beddingen 8

N-7014 Trondheim

Norway

 

Phone +47 73 600 800

Fax +47 73 600 801

Mobile +47 924 23 780

www.intrapoint.no

 



RE: Can I do this in one select sql?

2003-10-24 Thread "Paracková Eva, Ing"
this should work:

select NAME, sum(if(IF_PURCHASED='true',COUNT,0)),
sum(if(IF_PURCHASED='false', COUNT,0)) from TABLENAME group by NAME;

eva


-Original Message-
From: cengiz "taţhan [mailto:[EMAIL PROTECTED]
Sent: Friday, October 24, 2003 9:29 AM
To: [EMAIL PROTECTED]
Subject: Can I do this in one select sql?


Hi everybody;

  I have a table like this;

|| NAME || IF_PURCHASED || COUNT  ||
---  
   tom true5
   tom false   7
   tom false   3
   sam true3
   sam true4
   sam false   2
   ben true1


i want to a select querry that can do this;

|| NAME  || TRUE_TOTAL_COUNT || FALSE_TOTAL_COUNT ||
 
   tom  5   10
   sam  7   2
   ben  1   


I did this with 2 temporary  tables. one of selects  
true total count , other selects false total counts
then I JOINed them with UNION

Can I do this in one select sql?


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

-- 
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]



Help obtaining DBI binaries

2003-10-24 Thread Daniel
Please help, where do I download DBI module binaries
thanks
Daniel

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



ALTER TABLE semantics

2003-10-24 Thread Chris Nolan
Hi all,

Does anyone have a list of what affect ALTER TABLE has on each different type 
of MySQL table?

If I have read correctly, ALTER TABLE locks MyISAM tables until completeion. 
Am I right on this? How about InnoDB and BDB? If memory serves correctly, 
HEAP doesn't support ALTER TABLE.

Regards,

Chris

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



Re: Optimizing table (so-called VACUUM on postgreSQL) and InnoDB

2003-10-24 Thread Chris Nolan
VACUUM is basically a workalike for:

ALTER TABLE  TYPE=InnoDB;

There are various types of VACUUM commands as well. VACUUM FULL basically
locks the table and shrinks the on-disk table space. The normal "VACUUM"
(normal since 7.2 I think), does not lock the table (similar to REORGANISE
in some other databases, while REORGANISE in some databases like SQLBase
REORGANISE locks everything) but does a basic defrag.

Hope this helps!

Regards,

Chris

On Fri, 24 Oct 2003 05:23 pm, Jeremy Zawodny wrote:
> On Thu, Oct 16, 2003 at 05:13:58PM -0300, Leonardo Rodrigues wrote:
> > Hello all,
> >
> > The referencial integrity control don´t apply to a simple table, do
> > it?
>
> What's a "simple" table?
>
> > My DB is growing, and growing, and I´m very concerned about my host
> > limitions of disk usage. So...
>
> You should be!
>
> > Another question: postgreeSQL has a SQL command called VACUUM, which
> > optimize and reduce the table´s used space disk. I discover for mySQL the
> > commands "OPTIMIZE TABLE" and "ANALYZE TABLE", but both don´t run on
> > InnoDB.
>
> ANALYZE table runs on InnoDB.
>
> > My question are: are these commands similar to VACUUM? What´s the
> > difference? How can I run on InnoDB?
>
> I don't know much about PostgreSQL, so I'm not sure.  If you describe
> what VACUUM does, I may be able to help explain how to do the same
> thing with MySQL.
>
> Jeremy
> --
> Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
> <[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
>
> MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,530,867,850 queries
> (439/sec. avg)


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



Can I do this in one select sql?

2003-10-24 Thread taþhan
Hi everybody;

  I have a table like this;

|| NAME || IF_PURCHASED || COUNT  ||
---  
   tom true5
   tom false   7
   tom false   3
   sam true3
   sam true4
   sam false   2
   ben true1


i want to a select querry that can do this;

|| NAME  || TRUE_TOTAL_COUNT || FALSE_TOTAL_COUNT ||
 
   tom  5   10
   sam  7   2
   ben  1   


I did this with 2 temporary  tables. one of selects  
true total count , other selects false total counts
then I JOINed them with UNION

Can I do this in one select sql?


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

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



Can I do this in one select sql?

2003-10-24 Thread taþhan
Hi everybody;

  I have a table like this;

|| NAME || IF_PURCHASED || COUNT  ||
---  
   tom true5
   tom false   7
   tom false   3
   sam true3
   sam true4
   sam false   2
   ben true1


i want to a select querry that can do this;

|| NAME  || TRUE_TOTAL_COUNT || FALSE_TOTAL_COUNT ||
 
   tom  5   10
   sam  7   2
   ben  1   0


I did this with 2 temporary  tables. one of selects  
true total count , other selects false total counts
then I JOINed them with UNION

Can I do this in one select sql?


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

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



Re: Optimizing table (so-called VACUUM on postgreSQL) and InnoDB

2003-10-24 Thread Jeremy Zawodny
On Thu, Oct 16, 2003 at 05:13:58PM -0300, Leonardo Rodrigues wrote:
> Hello all,
> 
> The referencial integrity control don´t apply to a simple table, do
> it?

What's a "simple" table?

> My DB is growing, and growing, and I´m very concerned about my host
> limitions of disk usage. So...

You should be!

> Another question: postgreeSQL has a SQL command called VACUUM, which
> optimize and reduce the table´s used space disk. I discover for mySQL the
> commands "OPTIMIZE TABLE" and "ANALYZE TABLE", but both don´t run on
> InnoDB.

ANALYZE table runs on InnoDB.

> My question are: are these commands similar to VACUUM? What´s the
> difference? How can I run on InnoDB?

I don't know much about PostgreSQL, so I'm not sure.  If you describe
what VACUUM does, I may be able to help explain how to do the same
thing with MySQL.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,530,867,850 queries (439/sec. avg)

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



Re: replication redirector

2003-10-24 Thread Jeremy Zawodny
On Fri, Oct 24, 2003 at 04:00:07AM +0200, Benjamin KRIEF wrote:
> >
> > Is your bottleneck CPU, I/O, or lock contention?
> >
> > Is your slave running on worse hardware than your master?
> 
> my bottleneck is CPU , definitely. the load can go up to 35 in peak hours.
> no swapping occurs , my key efficiency is at 99,89 .

The load average often has little relationship to the actual CPU
utilization.  Is your CPU at 100% most of the time?  Or are your procs
blocked on disk I/O.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,530,693,767 queries (439/sec. avg)

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



Re: gemini

2003-10-24 Thread Jeremy Zawodny
On Fri, Oct 24, 2003 at 05:04:28PM +1000, Chris wrote:
> They gave up on it eh?
> 
> Was that more because of the legal problems that happened, or due
> to BDB and InnoDB continuing to improve and provide better solutions?

Leagal issues, probably.
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

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