Re: Optimizing a big query...

mos wrote: At 07:01 PM 3/10/2005, Chris W. Parker wrote: Homam S.A. mailto:[EMAIL PROTECTED] on Wednesday, March 09, 2005 5:09 PM said: If your tables are mostly read-only, you could pre-generate page numbers on a periodic basis and select only specific ranges WHERE row_number BETWEEN

Please help - MySQL4.1.10 don't run properly after data restored from 5.0.1

. Is there simple way for recreating all indexes? or check whether indexes are in-placed? Thanks Sam Good luck! From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Silly mistake Date: Wed, 02 Mar 2005 15:58:28 +0800 Hi, I admit I m silly to shutdown mysqld with the killall command

Re: ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Deniss Hennesy wrote: Hi, I have had to change mysql root passwd My procedure is just below shell mysql -u root -p mysql UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root'; mysql flush privileges; YOu may be need to execute command mysqladmin -u root password

Looking for start stop script

Hi, Does anyone have a working start/stop script for mysql4.1.10? The mysql.server script come with the mysql4.1.10 package does not work with argument (start/stop). Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:

Re: Silly mistake

050302 16:36:43 mysqld ended Since all DB data in this server are restored from the FreeBSD system in MySQL 5.0. I don't know how to recreate all indexes for db. Is there simple way for recreating all indexes? or check whether indexes are in-placed? Thanks Sam Good luck! From: sam wun [EMAIL

Re: Silly mistake

. Is there simple way for recreating all indexes? or check whether indexes are in-placed? Thanks Sam Good luck! From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Silly mistake Date: Wed, 02 Mar 2005 15:58:28 +0800 Hi, I admit I m silly to shutdown mysqld with the killall command

Re: copy DB data from FreeBSD to Redhat9.0

-01.Tuesday.sql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I have looked thru the mysqld script in /etc/init.d/ directory, but didn't get a cule about start up mysqld with unix sock. You got any idea? Thanks Sam sam wun [EMAIL PROTECTED] wrote

Re: copy DB data from FreeBSD to Redhat9.0

sam wun wrote: Gleb Paharenko wrote: Hello. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. If you add --all-databases option it would also dump

Re: copy DB data from FreeBSD to Redhat9.0

-11 Thanks Sam sam wun wrote: sam wun wrote: Gleb Paharenko wrote: Hello. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. If you add --all-databases

SQL runing very slow after restore

Hi, I finally got the backup data ported from mysql 5.0 (from freebsd) to mysql 4.1.10 in Redhat. Webmin started up and I can browse the data in each table. After installed all prerequisit perl modules, and start the web gui and start to run some queries, I expeirence it runs very slow. A sql in

Silly mistake

Hi, I admit I m silly to shutdown mysqld with the killall command in the Redhat server, I can't restart mysql service now. Most of the reason is because the script mysql.server come with the mysql 4.1.10 does not like mysql.server start or mysql.server stop, so I need to start it up use and

Re: copy DB data from FreeBSD to Redhat9.0

Gleb Paharenko wrote: Hello. I also want to copy the user accounts in MySQL5.0 db over to MySQL4.1 in Redhat as well. All user accounts and rights are stored in the tables of mysql database. And fields of grant tables in 4.1.x version just a subset of fields in 5.0.x. You should set

copy DB data from FreeBSD to Redhat9.0

Hi, I just fresh install mysql4.1 in Redhat9.0. I have another MySQL5.0 installed in FreeBSD5.3, and just tried to copy all data in /var/db/mysql/ from FreeBSD5.3 to /var/db/mysql/data/ in Redhat9.0. After finished copied the data over to Redhat, MySQL4.1 can't start. The DB data in MySQL5.0 in

Re: copy DB data from FreeBSD to Redhat9.0

sam wun wrote: Hi, I just fresh install mysql4.1 in Redhat9.0. I have another MySQL5.0 installed in FreeBSD5.3, and just tried to copy all data in /var/db/mysql/ from FreeBSD5.3 to /var/db/mysql/data/ in Redhat9.0. After finished copied the data over to Redhat, MySQL4.1 can't start. The DB data

download mysql 4 in rpm for Redhat 9.0

Hi, I realised that install mysql 4 thru rpm is the best way for a smooth installation. Can anyone please point me to a site where I can download mysql 4 rpm for Redhat9? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:

download Mysql4 for Redhat9.0

Hi, Can anybody tell me where can I download Mysql4 rpm for Redhat9.0? I cannot confirm the specific version in rpmfind.net is for Redhat9.0/ Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

create indexes for temporary table

Hi, I want to create indexes for temporary created tables in perl dbi. The following is perl code that I tried, but perl dbi seems not allow the syntax: Unable to execute our query PastSales:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

Re: INNER JOIN across multiple tables appear very slow.

Hi Michael, Thank you for being so kind to explain the problem to me. Michael Stassen wrote: sam wun wrote: sam wun wrote: Hi, Can anyone tell me how to optimize the following sql statement? $sql_1 = SELECT t.prodcode 'Product Code', t.prodname 'Product Name', ROUND(avg

Can't drop index.

Hi, I created an index on a foreign in a table before. I need to drop this index. but I got the following error: mysql alter table transaction drop index prodcode; ERROR 1025 (HY000): Error on rename of './datacube/#sql-30e8_3' to './datacube/transaction' (errno: 150) mysql mysql show index

INNER JOIN across multiple tables appear very slow.

Hi, Can anyone tell me how to optimize the following sql statement? $sql_1 = SELECT t.prodcode 'Product Code', t.prodname 'Product Name', ROUND(avg(t.salesvolume),2) 'PastSales Quantity', ROUND(avg(tt.salesvolume),2) 'Sales

Re: INNER JOIN across multiple tables appear very slow.

sam wun wrote: Hi, Can anyone tell me how to optimize the following sql statement? $sql_1 = SELECT t.prodcode 'Product Code', t.prodname 'Product Name', ROUND(avg(t.salesvolume),2) 'PastSales Quantity', ROUND(avg(tt.salesvolume

Error with Storage engine

Hi, I was trying to optimize a sql (for INNER JOIN) by spliting the sql into 2 temporary tables. But I got error with the following sql: mysql CREATE TEMPORARY TABLE pastsales_tab_3141604663377652915968 SELECT c.salescode,c.type, c.custcode, i.basename, i.vendorname, t.salesvolume, t.netsales,

show temporary table

Hi, How can I see all temporary tables that created by Create Temporary Table command in mysql 5.01? thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

optimize mysql 5.01

HI, Currently 16 records in a table slow down the search alot. Most of the queries are based on the date field. so I m adding index on it at the moment. I m wondering how much speed I can gain after finshed the index. Apart from indexing, what other alternativies I can use to speed up the

indexing operation running for few hours

Hi, I don't know what is wrong with running indexing on this table. It have been last for nearly 3 hours. here is the command I run at the prompt of mysql : alter table inventory add index(basename); Because it is running indexing, all operation seems hang up. As far as I can remember I only

Re: sub query is extermely slow

[EMAIL PROTECTED] wrote: SELECT DISTINCT i.basename FROM inventory i INNER JOIN transaction t ON i.prodcode = t.prodcode AND t.date = '2004-01-01' AND t.date = '2004-01-31' INNER JOIN transaction tt ON i.prodcode = tt.prodcode AND tt.date = '2005-01-01'

sub query is extermely slow

Hi list, The following sql statement takes 3 mintues to complete the query. How can I improve its speed? select DISTINCT i.basename from inventory i, transaction t, customer c where i.prodcode = t.prodcode and c.custcode = t.custcode and i.basename is not NULL and i.prodname is not NULL and

Re: sub query is extermely slow

From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: sub query is extermely slow Date: Wed, 19 Jan 2005 20:02:37 +0800 Hi list, The following sql statement takes 3 mintues to complete the query. How can I improve its speed? select DISTINCT i.basename from inventory i, transaction t

Re: sub query is extermely slow

index to a field after a table is created? Thanks Sam Clint From: sam wun [EMAIL PROTECTED] To: Clint Edwards [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 22:05:58 +0800 Clint Edwards wrote: Sam, Can you send the following information

Re: sub query is extermely slow

[EMAIL PROTECTED] wrote: I would simplify it by converting everything to us explicit (not implicit) JOIN statements,skipping unnecessary type conversions, and logically merging your conditions. Here is your original query, slightly reformatted. SELECT DISTINCT i.basename FROM inventory i,

Re: create view not working on mysql 4.1.8

sirisha gnvg wrote: we are working on mysql 4.1.8 and windows XP platform. This version supports views.We created a view like this mysqluse sample mysqlcreate view v1 as select * from sam2; sam2 is a table We got an error errror 1024(42000):you have an error in your sql syntax; we checked the

Update does not doing updates

Hi, I use the following statement to update a table, but I don't see the testing record being updated: $udpate_sql = qq {update transaction set salesvolume=$salesvol, netsales=$netsales, transtype=$transtype, returnreason=$returnreason

Re: Insert if Update failed without Select

[EMAIL PROTECTED] wrote: Please also note hat UPDATE returns the number of records updated. If your UPDATE returns 0, you know that the record does not exist, and you might want to INSERT instead. There is one situation where the number of records updated will return 0, yet the row exists.

Insert if Update failed without Select

Hi, Without using Select statement, how can I execute Insert SQL statement if Update action is failed? I may be asking for too much. If Select statemnet have to be used to determine the existence of a recordset, what is an efficient way to execute Insert if Update is failed? I m using MySQL 5.0

Error in Insert on Duplicate Key Update

HI, the following insert/update produced error. I m using MySQL 5.0. $insert_sql = qq {insert into inventory (prodcode,qty,lastupdatedate,prodname,basename,vendorname,cost) values (?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE

Re: easy way to drop a tempoary table created by Create view?

Gleb Paharenko wrote: Hello. Use -e command line option for mysql client program. For example: mysql -u root -p test -e 'drop view v' Hi, does it take wildcard? something like: mysql -u root -p test -e 'drop view v*' Thanks Sam -- MySQL General Mailing List For list archives:

Re: easy way to drop a tempoary table created by Create view?

sam wun wrote: Gleb Paharenko wrote: Hello. Use -e command line option for mysql client program. For example: mysql -u root -p test -e 'drop view v' Hi, does it take wildcard? something like: mysql -u root -p test -e 'drop view v*' Or even drop all view tables (only affect the view tables

traverse the recordset more than once

Hi list, How can I traverse the recordset moret than once? For example, the recordset $sth is returned from executing the sql statement. The following statement is writen with the for loop for retrieving each record from the recordset. $aref = $sth-fetchrow_arrayref However when the for loop is

Re: traverse the recordset more than once

Steve Buehler wrote: At 08:11 PM 1/5/2005, you wrote: Hi list, How can I traverse the recordset moret than once? For example, the recordset $sth is returned from executing the sql statement. The following statement is writen with the for loop for retrieving each record from the recordset. $aref

Re: traverse the recordset more than once

Steve Buehler wrote: At 08:11 PM 1/5/2005, you wrote: Hi list, How can I traverse the recordset moret than once? For example, the recordset $sth is returned from executing the sql statement. The following statement is writen with the for loop for retrieving each record from the recordset. $aref

How to determine a field is part of the primary key in a table?

Hi list, I m writing a perl program and would like to use it (wiht mysql command) to determine whether a field name is (or is part of) a primary key of a table. If you know how to handle this, please drop me a line. Thanks and Happy New Year. Sam -- MySQL General Mailing List For list

Comparison SQL always return false

Hi, I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3. I found that if I assign a hard coded string (with double quote) to the string variable which in turns become part of the SQL statement, the SQL does return result. If I assign a value which is return from an array in perl (eg.

value of array to sql statement

Hi list, There is problem when with executing the sql statement in mysql 5.0. I m not sure whether version 4.x has the same problem. The problem is if I use static (hardcoded) values in the SQL statement, mysql returns result for each element in the array. If I use dynamically allocated value

Sum on Float type

Hi, I created a Transaction table with a field Cost which is a Float type with only 2 precision eg. 123.01. When I use the following sql statement to make a sum of this field, it returned a Float number with more than 2 precision numbers eg. 456.92384933 select t.custcode, sum(t.cost) as