Re: Optimizing a big query...

[sam wun]

mos wrote:
At 07:01 PM 3/10/2005, Chris W. Parker wrote:
Homam S.A. 
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
> page_start AND page_finish.
How about just getting the entire list on the first page and then on all
subsequent pages using LIMIT()?

Chris.

Chris,
This is deja vu all over again.
I already suggested this. He needs the record count and Limit 
doesn't provide it. And separate Sql Count would be too slow.

Mike
This require the change of Mysql engine. 
I wish Mysql has such configuration option for that in future release.

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

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

[sam wun]

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 
(yourpassword) for the creation of password. I m not sure,  not expert 
in DBA.

Sam
and I checked this password another consol  I saw MySQL wasn't
accepted  new passwd else, it didnt old password or without password.
 

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

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

[sam wun]

]
mysql 2895  0.0  8.8 302680 45340 ?  S14:16   0:00 [mysqld]


The questions I would like to ask is how to make sure mysqld is start up 
properly? Because it seems that the start script that come with the 
binary files from mysql4.1.10 can';t be started by typing "mysql.server 
start", and I don't see a /etc/my.conf is installed in the system, so I 
copied the support-files/my-huge.cnf to /etc/my.cnf.

The second question is how to make sure all indexes are in-placed after 
the data restore?  I used the following script backup all data from 
MySQL5.0.1:
DBNAMES="`mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST 
--batch --skip-column-names -e "show databases"|
sed 's/ /%/g'`"

Thank you in advance
Sam.
mel list_php wrote:

Yeah, I have looked at it, but  not sure whether I need to repopulate 
the mysql.host db file. If I do that, I may be also need to recreate 
all db passwords as well.
The error is:
InnoDB: log sequence number 0 43634.
InnoDB: Doing recovery: scanned up to log sequence number 0 43634
050302 16:36:43  InnoDB: Flushing modified pages from the buffer pool...
050302 16:36:43  InnoDB: Started; log sequence number 0 43634
050302 16:36:43 [ERROR] Fatal error: Can't open privilege tables: 
Table 'mysql.host' doesn't exist
050302 16:36:43  mysqld ended

I don't know how you made your backup, apparently you missed the 
mysql.host table.
Usually when making a new install a mysql database containing all the 
privileges is created, maybe you erased this when copying your files?
In an other mail you say you used the --all-databases option so I 
suppose the error is somewhere else.
To identify the problem you may try to recreate the mysql db, see if 
it works, and if yes check your dump.

About your indexes I usually use mysqldump and the indexes are 
exported as well, you can check with show index but I'm not sure that 
is what you are looking for.
http://dev.mysql.com/doc/mysql/en/mysqldump.html




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 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 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 shut it down with killall.
Anyway, after killall mysql, I got the following error when I tried 
to restart it.
Here is the error:
./mysqld
Starting mysqld daemon with databases from /usr/local/mysql/data
STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid
050302 15:52:05  mysqld ended

How can I start mysqld now?
Thanks
Sam

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

Re: Silly mistake

[sam wun]

mel list_php wrote:

Yeah, I have looked at it, but  not sure whether I need to repopulate 
the mysql.host db file. If I do that, I may be also need to recreate 
all db passwords as well.
The error is:
InnoDB: log sequence number 0 43634.
InnoDB: Doing recovery: scanned up to log sequence number 0 43634
050302 16:36:43  InnoDB: Flushing modified pages from the buffer pool...
050302 16:36:43  InnoDB: Started; log sequence number 0 43634
050302 16:36:43 [ERROR] Fatal error: Can't open privilege tables: 
Table 'mysql.host' doesn't exist
050302 16:36:43  mysqld ended

I don't know how you made your backup, apparently you missed the 
mysql.host table.
Usually when making a new install a mysql database containing all the 
privileges is created, maybe you erased this when copying your files?
In an other mail you say you used the --all-databases option so I 
suppose the error is somewhere else.
To identify the problem you may try to recreate the mysql db, see if 
it works, and if yes check your dump.

It worked nearly perfectly before, I can login to db, use perl dbi 
connect to it, etc. So I think it is the problem when I executed command 
killall mysql. I don't supposed it remove the mysql.host file, may be 
mysql.host db is corrupted when I m doing so, so it removes it for me.
This becomes a very big  problem, I m not sure if one day the system 
crashes, mysql will remove any DB without warning me first.
Now, it is most likely I need to erase all dbs and repopulate (restore) 
all data.

About your indexes I usually use mysqldump and the indexes are 
exported as well, you can check with show index but I'm not sure that 
is what you are looking for.
http://dev.mysql.com/doc/mysql/en/mysqldump.html

I have checked all indexes with the show index TABLE commands, and 
compared the result with the one in FreeBSD, and looks quite identical.
The only difference is the one in FreeBSD is running MySQL 5.01 and the 
one in this Redhat is running MySQL 4.1.10.
The indexing problem gave me a big headache because I m not sure whether 
it is the versioning problem or missing indexes or something else...
With MySQL5.01 in FreeBSD 5.3, it takes 2 seconds finish the query, but 
with MySQL 4.1.10 in Redhat, takes forever...

Thanks
Sam



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 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 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 shut it down with killall.
Anyway, after killall mysql, I got the following error when I tried 
to restart it.
Here is the error:
./mysqld
Starting mysqld daemon with databases from /usr/local/mysql/data
STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid
050302 15:52:05  mysqld ended

How can I start mysqld now?
Thanks
Sam

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

Re: Silly mistake

[sam wun]

mel list_php wrote:
Hi Sam,
To shutdown mysql I just use mysqladmin shutdown.
http://dev.mysql.com/doc/mysql/en/server-shutdown.html
Thanks for this help. I will surely use this command next time.
About you restart problem, have a look in mysql/data/host.err file, it 
should give you a reason there.
Last time I had that problem it's because I hadn't kill all the process.

Yeah, I have looked at it, but  not sure whether I need to repopulate 
the mysql.host db file. If I do that, I may be also need to recreate all 
db passwords as well.
The error is:
InnoDB: log sequence number 0 43634.
InnoDB: Doing recovery: scanned up to log sequence number 0 43634
050302 16:36:43  InnoDB: Flushing modified pages from the buffer pool...
050302 16:36:43  InnoDB: Started; log sequence number 0 43634
050302 16:36:43 [ERROR] Fatal error: Can't open privilege tables: Table 
'mysql.host' doesn't exist
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 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 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 shut it down with killall.
Anyway, after killall mysql, I got the following error when I tried 
to restart it.
Here is the error:
./mysqld
Starting mysqld daemon with databases from /usr/local/mysql/data
STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid
050302 15:52:05  mysqld ended

How can I start mysqld now?
Thanks
Sam

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

Looking for start stop script

[sam wun]

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:http://lists.mysql.com/[EMAIL PROTECTED]

Silly mistake

[sam wun]

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 shut it down with killall.
Anyway, after killall mysql, I got the following error when I tried to restart 
it.
Here is the error:
./mysqld
Starting mysqld daemon with databases from /usr/local/mysql/data
STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid
050302 15:52:05  mysqld ended
How can I start mysqld now?
Thanks
Sam

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

SQL runing very slow after restore

[sam wun]

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 mysql 5.0 finsihed in few seconds, but my mysql 4.1.10 takes forever to run. 

I created indexes in mysql 5.0. When doing backup, I use --all-databases and restore 
it use command mysql < mysql.sql and mysql < data.sql.
I don't know whether all indexes are rebuilt correctly in the new database 
(mysql 4.1.10). How do I know all my previous indexes are in-placed with the 
restore?
Thanks
Sam

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

Re: copy DB data from FreeBSD to Redhat9.0

[sam wun]

Hi,
I have installed the server and started up successfully. mysql.sock file 
is written to /var/lib/mysql/ directory as well.
Now I found that I also need to install mysql 4.10-1 client. However 
there is already mysql3 installed in the system. How can I remove the 
old version in Redhat?
Here are all error I got:
# rpm -i MySQL-client-4.1.10-0.i386.rpm
warning: MySQL-client-4.1.10-0.i386.rpm: V3 DSA signature: NOKEY, key ID 
5072e1f5
   file /usr/bin/mysql from install of MySQL-client-4.1.10-0 
conflicts with file from package mysql-3.23.54a-11
   file /usr/bin/mysql_find_rows from install of 
MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11
   file /usr/bin/mysqlaccess from install of MySQL-client-4.1.10-0 
conflicts with file from package mysql-3.23.54a-11
   file /usr/bin/mysqlbinlog from install of MySQL-client-4.1.10-0 
conflicts with file from package mysql-3.23.54a-11
   file /usr/bin/mysqlcheck from install of MySQL-client-4.1.10-0 
conflicts with file from package mysql-3.23.54a-11
   file /usr/bin/mysqldump from install of MySQL-client-4.1.10-0 
conflicts with file from package mysql-3.23.54a-11
   file /usr/bin/mysqlimport from install of MySQL-client-4.1.10-0 
conflicts with file from package mysql-3.23.54a-11
   file /usr/bin/mysqlshow from install of MySQL-client-4.1.10-0 
conflicts with file from package mysql-3.23.54a-11
   file /usr/share/man/man1/mysql.1.gz from install of 
MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11
   file /usr/share/man/man1/mysqlaccess.1.gz from install of 
MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11
   file /usr/share/man/man1/mysqladmin.1.gz from install of 
MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11
   file /usr/share/man/man1/mysqldump.1.gz from install of 
MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11
   file /usr/share/man/man1/mysqlshow.1.gz from install of 
MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-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 option it would also dump the mysql 
database.

I've tried to import the mysql database from the 5.0.2 to 4.1.10. It 
works

fine, You may try just to restore the dump file with mysql < 
dump_file.sql

command. Don't forget to execute FLUSH PRIVILEGES.
 

Hi, I got  the following error when execute mysql < mydata.sql:
# mysql < mysql_2005-03-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?

I also search for where mysql.sock is, but I couldn't find this file 
in the system. have I miss something in the mysqld start up script?
Thanks
Sam

Thanks
Sam


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

Re: copy DB data from FreeBSD to Redhat9.0

[sam wun]

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 the mysql database.
I've tried to import the mysql database from the 5.0.2 to 4.1.10. It 
works

fine, You may try just to restore the dump file with mysql < 
dump_file.sql

command. Don't forget to execute FLUSH PRIVILEGES.
 

Hi, I got  the following error when execute mysql < mydata.sql:
# mysql < mysql_2005-03-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?
I also search for where mysql.sock is, but I couldn't find this file in 
the system. have I miss something in the mysqld start up script?
Thanks
Sam

Thanks
Sam

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

Re: copy DB data from FreeBSD to Redhat9.0

[sam wun]

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 the mysql database.
I've tried to import the mysql database from the 5.0.2 to 4.1.10. It works
fine, You may try just to restore the dump file with mysql < dump_file.sql
command. Don't forget to execute FLUSH PRIVILEGES.
 

Hi, I got  the following error when execute mysql < mydata.sql:
# mysql < mysql_2005-03-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:
 

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 the values of fields in 4.1.x version to values of 
 

 

 

corresponding fields in 5.0.x. For example you may store 5.0.x mysql
 

 

 

database under different name in 4.1.x and then move accounts using
 

 

 

SQL statements.
 

 

 

 

 

 

 

 

Hi, thanks for your help. I just able to backup all the database (with 
   

 

their tables I believe) from mysql 5.0.
   

 

And now I stuck at restoring the database and their tables as well as 
   

 

user accounts rights to mysql4.1.
   

 

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.
   

 

 


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

Re: copy DB data from FreeBSD to Redhat9.0

[sam wun]

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 the values of fields in 4.1.x version to values of 

corresponding fields in 5.0.x. For example you may store 5.0.x mysql
database under different name in 4.1.x and then move accounts using
SQL statements.

 

Hi, thanks for your help. I just able to backup all the database (with 
their tables I believe) from mysql 5.0.
And now I stuck at restoring the database and their tables as well as 
user accounts rights to mysql4.1.
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.
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: copy DB data from FreeBSD to Redhat9.0

[sam wun]

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 in MySQL5.0 in the FreeBSD box has database and tables 
with indexes.
I don't want to rebuild the entire database from scratch in Redhat, 
how can I "transfer" all db files from FreeBSD to Redhat and in 
another version of MySQL?

I also want to copy the user accounts in MySQL5.0 db over to MySQL4.1 in 
Redhat as well. I supposed this is sort of data backup, but more generic 
than that because it is copying data between different version of MySQL, 
and from higher version to a lower version of MySQL.

Sam
Thanks
Sam

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

copy DB data from FreeBSD to Redhat9.0

[sam wun]

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 the FreeBSD box has database and tables with indexes.
I don't want to rebuild the entire database from scratch in Redhat, how 
can I "transfer" all db files from FreeBSD to Redhat and in another 
version of MySQL?

Thanks
Sam

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

download mysql 4 in rpm for Redhat 9.0

[sam wun]

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:http://lists.mysql.com/[EMAIL PROTECTED]

download Mysql4 for Redhat9.0

[sam wun]

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]

Can't drop index.

[sam wun]

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 from transaction;
+-++-+--+-+---+-+--++--++-+
| Table   | Non_unique | Key_name| Seq_in_index | Column_name | 
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-++-+--+-+---+-+--++--++-+
| transaction |  0 | PRIMARY |1 | transcode   | 
A |  161361 | NULL | NULL   |  | BTREE  | |
| transaction |  1 | custcode|1 | custcode| 
A | 810 | NULL | NULL   |  | BTREE  | |
| transaction |  1 | prodcode|1 | prodcode| 
A | 911 | NULL | NULL   |  | BTREE  | |
| transaction |  1 | date|1 | date| 
A |1014 | NULL | NULL   |  | BTREE  | |
| transaction |  1 | netsales|1 | netsales| 
A |   14669 | NULL | NULL   |  | BTREE  | |
| transaction |  1 | salesvolume |1 | salesvolume | 
A | 197 | NULL | NULL   |  | BTREE  | |
| transaction |  1 | custcode_2  |1 | custcode| 
A | 139 | NULL | NULL   |  | BTREE  | |
| transaction |  1 | custcode_2  |2 | date| 
A |   26893 | NULL | NULL   |  | BTREE  | |
+-++-+--+-+---+-+--++--++-+
8 rows in set (0.02 sec)

mysql> show index from inventory;
+---+++--+-+---+-+--++--++-+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | 
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---+++--+-+---+-+--++--++-+
| inventory |  0 | PRIMARY|1 | prodcode| 
A |2377 | NULL | NULL   |  | BTREE  | |
| inventory |  1 | basename   |1 | basename| 
A |  30 | NULL | NULL   | YES  | BTREE  | |
| inventory |  1 | vendorname |1 | vendorname  | 
A |  27 | NULL | NULL   | YES  | BTREE  | |
+---+++--+-+---+-+--++--++-+
3 rows in set (0.01 sec)

mysql> show innodb status;
=
050124 10:50:54 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 11 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 1372, signal count 1344
Mutex spin waits 2173, rounds 23570, OS waits 375
RW-shared spins 889, OS waits 444; RW-excl spins 661, OS waits 553

LATEST FOREIGN KEY ERROR

050124 10:49:59 Error in foreign key constraint of table 
`datacube/transaction`:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match to the ones in the referenced table. Constraint:
,
 CONSTRAINT `transaction_ibfk_2` FOREIGN KEY (`prodcode`) REFERENCES 
`inventory` (`prodcode`) ON DELETE CASCADE
TRANSACTIONS

Trx id counter 0 3143526
Purge done for trx's n:o < 0 3143526 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 3143514, not started, OS thread id 167162880
MySQL thread id 3, query id 69 localhost root
SHOW INNODB STATUS

FILE I/O

I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
79139 OS file reads, 100705 OS file writes, 6249 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-
INSERT BUFFER AND ADAPTIVE HASH IND

Re: INNER JOIN across multiple tables appear very slow.

[sam wun]

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(t.salesvolume),2) 'PastSales Quantity',
ROUND(avg(tt.salesvolume),2) 'Sales Quantity',
ROUND(avg(t.netsales),2) 'PastSales',
ROUND(avg(tt.netsales),2) 'Sales',
Round(((avg(t.netsales) - 
avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff',
Round(((avg(t.salesvolume) - 
avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff'
 FROM customer c
 INNER JOIN transaction t
ON c.custcode = t.custcode
AND date(t.date) >= '$past_date_period_startdate'
AND date(t.date) <= '$past_date_period_enddate'
AND c.salescode = '$salescode_param'
 INNER JOIN transaction tt
ON c.custcode = tt.custcode
AND date(tt.date) >= '$input_date_period_startdate'
AND date(tt.date) <= '$input_date_period_enddate'
AND c.salescode = '$salescode_param'
AND t.prodcode = tt.prodcode
 INNER JOIN inventory i
ON i.prodcode = t.prodcode
AND i.prodcode = tt.prodcode
AND i.basename = '$basename_param'
 WHERE i.prodname is not NULL
   AND i.basename is not NULL
 GROUP BY c.salescode, i.basename, t.prodcode
 ORDER BY SalesDiff desc;

Thanks
Sam

I see several potential problems:
1) The biggest fixable problem is the use of the date() function.  You 
should never use a function of a column value in a WHERE clause, if 
you can avoid it, because that prevents the use of an index on that 
column to help choose rows.  In this case, the date() function isn't 
even needed, so even if there is no index on t.date, or the optimizer 
doesn't choose to use it, you are still forcing 4 unnecessary function 
calls per row.

In other words, change
  AND date(t.date) >= '$past_date_period_startdate'
  AND date(t.date) <= '$past_date_period_enddate'
to
  AND t.date >= '$past_date_period_startdate'
  AND t.date <= '$past_date_period_enddate'
or the equivalent
  AND t.date BETWEEN '$past_date_period_startdate'
 AND '$past_date_period_enddate'
Yes, I am totally agree with this change. I m still new to mysql/sql 
statement. I never realised the date field can be used for comparison 
without using date.

2) In general, you should'nt put things in the JOIN condition which 
are not part of the JOIN criteria.  For example, the condition 
"c.salescode = '$salescode_param'" is a restriction on which rows in c 
to consider, not a condition of the JOIN to t or tt, so it belongs in 
the WHERE clause.

3) You have a complex GROUP BY based on a column from each table, and 
an ORDER BY based on a calculated value.  That rules out using an 
index to do the grouping or ordering.  Hence the dreaded "Using 
temporary; Using filesort" in your explain output.  This appears 
unavoidable in your case.

This clears my question about how to use JOIN.
The "using temporary" may be caused by the clause "create temporary 
table" in other sql statement that created before execute the sql 
statement as written above.
I was trying to break down the entire operation into sub-queires so that 
it produce sub-recordset faster and then JOIN with these recordset later.
I m not sure whether using temporary tables will also speed thing up. I 
will drop the idea of using temporary table if that doesn't help much.
I guess I can change the group by with only one column rather than 3 
columns, and the result still the same since salescode and basename are 
input by the user, so this query only display the recordset of the 
product(code/name) base on value of salescode and the basename.

4) You have redundant conditions on your JOIN to i.  Since t.prodcode 
= tt.prodcode for every row of your results (because you require this 
in the join of t to tt), the two conditions "i.prodcode = t.prodcode" 
and "i.prodcode = tt.prodcode" are equivalent.  The optimizer is 
probably smart enough to notice that, but it will have less work to do 
if you pick one and delete the other.

5) Because you require "i.basename = '$basename_param'", i.basename 
cannot be NULL, so there is no need for the conditrion "i.basename is 
not NULL".

6) Are you sure you need "i.prodname is not NULL"?  If you can be 
certain that all rows in inventory with non NULL prodcode and basename 
have non NULL prodnames, you could leave that out.  I can't assume you 
don't need it based only on the rest of your query, so I'll leave it

create indexes for temporary table

[sam wun]

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 
for the right syntax to use near '"alter table tmp_pastsales add 
index(salescode,basename,prodcode);"' at line 1 Unable to execute our 
query Sales: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 '"alter table tmp_sales add index(salescode,basename,prodcode);"' 
at line 1

This is the actual perl code I use:
$tmp_sql = qq{"alter table tmp_pastsales add 
index(salescode,basename,prodcode);"};
$sth_tmp = $dbh->prepare($tmp_sql) or print "Unable to prep our 
query:".$dbh->errstr."\n";
$sth_tmp->execute or print "Unable to execute our query 
PastSales:".$dbh->errstr."\n";

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

show temporary table

[sam wun]

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]

Error with Storage engine

[sam wun]

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, t.prodcode, i.prodname from inventory i INNER 
JOIN transaction t ON i.prodcode = t.prodcode AND date(t.date) >= 
'2003-01-23' AND date(t.date) <= '2004-01-23' INNER JOIN customer c ON 
c.custcode = t.custcode ;
ERROR 1030 (HY000): Got error 28 from storage engine

How can I correct this error? I musing mysql5.01
Thanks
Sam.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: INNER JOIN across multiple tables appear very slow.

[sam wun]

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),2) 'Sales Quantity',
   ROUND(avg(t.netsales),2) 'PastSales',
   ROUND(avg(tt.netsales),2) 'Sales',
   Round(((avg(t.netsales) - 
avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff',
   Round(((avg(t.salesvolume) - 
avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff'
   from customer c
   INNER JOIN transaction t
   ON c.custcode = t.custcode
   AND date(t.date) >= '$past_date_period_startdate'
   AND date(t.date) <= '$past_date_period_enddate'
   AND c.salescode = '$salescode_param'
   INNER JOIN transaction tt
   ON c.custcode = tt.custcode
   AND date(tt.date) >= 
'$input_date_period_startdate'
   AND date(tt.date) <= '$input_date_period_enddate'
   AND c.salescode = '$salescode_param'
   AND t.prodcode = tt.prodcode
   INNER JOIN inventory i
   ON i.prodcode = t.prodcode
   AND i.prodcode = tt.prodcode
   AND i.basename = '$basename_param'
   WHERE i.prodname is not NULL
   and i.basename is not NULL
 GROUP BY c.salescode, i.basename, t.prodcode
 ORDER BY SalesDiff desc;

Thanks
Sam

Hi, here is the result from the explain command on the query:
mysql> explain SELECT i.basename, c.salescode,
   -> avg(t.salesvolume) 'PastSales Quantity',
   -> avg(tt.salesvolume) 'Sales Quantity',
   -> avg(t.netsales) PastSales,
   -> avg(tt.netsales) Sales,
   -> Round(((avg(t.netsales) - 
avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff',
   -> Round(((avg(t.salesvolume) - 
avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff', c.salescode
   -> from customer c
   -> INNER JOIN transaction t
   -> ON c.custcode = t.custcode
   -> AND date(t.date) >= '2003-01-22'
   -> AND date(t.date) <= '2004-01-22'
   -> INNER JOIN transaction tt
   -> ON c.custcode = tt.custcode
   -> AND date(tt.date) >= '2004-01-22'
   -> AND date(tt.date) <= '2005-01-22'
   -> INNER JOIN inventory i
   -> ON i.prodcode = t.prodcode
   -> AND i.prodcode = tt.prodcode
   -> WHERE i.prodname is not NULL
   -> and i.basename is not NULL
   -> group by c.salescode,i.basename
   -> order by c.salescode, 'SalesDiff' desc ;
++-+---++---+--+-+-+--+--+
| id | select_type | table | type   | possible_keys | key  | 
key_len | ref | rows | 
Extra|
++-+---++---+--+-+-+--+--+
|  1 | SIMPLE  | i | ALL| PRIMARY,basename  | NULL | 
NULL| NULL| 2491 | Using where; Using temporary; 
Using filesort |
|  1 | SIMPLE  | t | ref| custcode,prodcode | prodcode | 
32  | datacube.i.prodcode |   30 | Using 
where  |
|  1 | SIMPLE  | c | eq_ref | PRIMARY,custcode  | PRIMARY  | 
32  | datacube.t.custcode |1 
|  |
|  1 | SIMPLE  | tt| ref| custcode,prodcode | prodcode | 
32  | datacube.i.prodcode |   30 | Using 
where  |
++-+---++---+--+-+-+--+--+
4 rows in set (0.00 sec)

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

INNER JOIN across multiple tables appear very slow.

[sam wun]

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 Quantity',
   ROUND(avg(t.netsales),2) 'PastSales',
   ROUND(avg(tt.netsales),2) 'Sales',
   Round(((avg(t.netsales) - 
avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff',
   Round(((avg(t.salesvolume) - 
avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff'
   from customer c
   INNER JOIN transaction t
   ON c.custcode = t.custcode
   AND date(t.date) >= '$past_date_period_startdate'
   AND date(t.date) <= '$past_date_period_enddate'
   AND c.salescode = '$salescode_param'
   INNER JOIN transaction tt
   ON c.custcode = tt.custcode
   AND date(tt.date) >= '$input_date_period_startdate'
   AND date(tt.date) <= '$input_date_period_enddate'
   AND c.salescode = '$salescode_param'
   AND t.prodcode = tt.prodcode
   INNER JOIN inventory i
   ON i.prodcode = t.prodcode
   AND i.prodcode = tt.prodcode
   AND i.basename = '$basename_param'
   WHERE i.prodname is not NULL
   and i.basename is not NULL
 GROUP BY c.salescode, i.basename, t.prodcode
 ORDER BY SalesDiff desc;

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

Re: sub query is extermely slow

[sam wun]

[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'
AND tt.date <= '2005-01-31'
INNER JOIN customer c
ON c.custcode = t.custcode
AND c.custcode = tt.custcode
WHERE i.basename is not NULL
and i.prodname is not NULL
order by i.basename
This should give you a list of inventory basenames for all current 
customers (their names are still in the customer table) that "had 
transactions" (ordered products?) during both JAN 2004 and JAN 2005. 
This list will show only the products that were ordered during BOTH 
time periods BY THE SAME CUSTOMER at least once (at least one repeat 
sale, year to year, in JAN). Is this what you were after or was there 
a different question you were trying to answer?

Hi, while this is much faster than subquery, I found there is "cumsy" 
way faster than this series inner joins.
I dicovered that if I create two different temporary tables with "create 
view as select ..." for 2 differnet period, the join between this temp 
tables is also much faster than this series inner joins approach.  With 
16 records in the transaction table, the series inner joins takes  
very long time (more than 15 minutes) give out a result.

Any comment?
Thanks
Sam
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

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

indexing operation running for few hours

[sam wun]

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 injected 2300+ records in this table, 
compared to the transaction table in the same database which has 16 
records ,this table is a tiny one in size. I run index on the trasactoin 
table, and it only took 43 min finishsed indexting on the date field.

Can anybody tell me what is going on?
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

[sam wun]

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 
search?

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

Re: sub query is extermely slow

[sam wun]

[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, 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 (
(date(t.date) >= "2004-01-01"
and date(t.date) <= "2004-01-31"
)
and i.basename IN (
select DISTINCT ii.basename
from inventory ii, transaction tt, customer cc
   where ii.prodcode = tt.prodcode
and cc.custcode = tt.custcode
and ii.basename is not NULL
and ii.prodname is not NULL
and(
date(tt.date) >= "2005-01-01"
and date(tt.date) <= "2005-01-31")
)
)
order by i.basename
Here is my proposal:
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'
AND tt.date <= '2005-01-31'
INNER JOIN customer c
ON c.custcode = t.custcode
AND c.custcode = tt.custcode
WHERE i.basename is not NULL
and i.prodname is not NULL
order by i.basename
This should give you a list of inventory basenames for all current 
customers (their names are still in the customer table) that "had 
transactions" (ordered products?) during both JAN 2004 and JAN 2005. 
This list will show only the products that were ordered during BOTH 
time periods BY THE SAME CUSTOMER at least once (at least one repeat 
sale, year to year, in JAN). Is this what you were after or was there 
a different question you were trying to answer?
Hi Shawn, this works great. This will give all products that exist in 
two different period. What if I also want to list products that in 
period 1 but not in peirod 2; while other products in preriod 2 but not 
in period 1? Do you think simply take out the join for i.prodcode = 
t.prodcode and i.prodcode = tt.prodcode and use left/right join will 
yield the desired result?

Thanks
Sam
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

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

Re: sub query is extermely slow

[sam wun]

Clint Edwards wrote:
Sam,
Can you create an index on transaction.date, then run your query 
again?  If that is not better send me the output of 'explain ' 
again.

This index may not be a good idea, depending on how many transaction 
are in the table on a specified date.
May I ask how to add 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:
When was the last time 'analyze table ' (inventory, 
transaction, customer) was executed?

Hi, here is the result of the analyze command:
mysql> analyze table inventory,transaction, customer;
+--+-+--+--+
| Table| Op  | Msg_type | Msg_text |
+--+-+--+--+
| datacube.inventory   | analyze | status   | OK   |
| datacube.transaction | analyze | status   | OK   |
| datacube.customer| analyze | status   | OK   |
+--+-+--+--+
3 rows in set (0.83 sec)
OS:
MySQL Version:

Mysql 5.0
Available Ram:

254RAM
Output from 'SHOW CREATE TABLE ' (inventory, 
transaction, and customer):

Output from "SHOW VARIABLES LIKE '%buffer%';":
mysql> SHOW CREATE TABLE inventory;
+---+---+ 

| Table | Create 
Table  
   
   
   
   
|
+---+---+ 

| inventory | CREATE TABLE `inventory` (
 `prodcode` varchar(32) NOT NULL default '',
 `qty` decimal(9,2) NOT NULL default '0.00',
 `lastupdatedate` date NOT NULL default '-00-00',
 `prodname` varchar(32) default 'UNKNOWN',
 `basename` varchar(32) default 'UNKNOWN',
 `vendorname` varchar(50) default 'UNKNOWN',
 `cost` decimal(9,2) NOT NULL default '0.00',
 PRIMARY KEY  (`prodcode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---+---+ 

1 row in set (0.39 sec)
mysql> SHOW CREATE TABLE transaction;
+-+

Re: sub query is extermely slow

[sam wun]

Clint Edwards wrote:
Sam,
Can you send the output of the following:
#>explain \G
Thanks for the suggestion, here is the output of the explain query:
mysql> explain 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 ((date(t.date) 
>= "2004-01-01" and date(t.date) <= "2004-01-31") and i.basename IN 
(select DISTINCT ii.basename from inventory ii, transaction tt, customer 
cc where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and 
ii.basename is not NULL and ii.prodname is not NULL and(date(tt.date) >= 
"2005-01-01" and date(tt.date) <= "2005-01-31"))) order by i.basename;
+++---++---+--+-+--+--+--+
| id | select_type| table | type   | possible_keys | 
key  | key_len | ref  | rows | 
Extra|
+++---++---+--+-+--+--+--+
|  1 | PRIMARY| c | index  | PRIMARY   | 
PRIMARY  | 32  | NULL |  317 | Using index; Using 
temporary; Using filesort |
|  1 | PRIMARY| t | ref| custcode,prodcode | 
custcode | 32  | datacube.c.custcode  |   36 | Using 
where  |
|  1 | PRIMARY| i | eq_ref | PRIMARY   | 
PRIMARY  | 32  | datacube.t.prodcode  |1 | Using 
where  |
|  2 | DEPENDENT SUBQUERY | cc| index  | PRIMARY   | 
PRIMARY  | 32  | NULL |  317 | Using index; Using 
temporary |
|  2 | DEPENDENT SUBQUERY | tt| ref| custcode,prodcode | 
custcode | 32  | datacube.cc.custcode |   36 | Using 
where  |
|  2 | DEPENDENT SUBQUERY | ii| eq_ref | PRIMARY   | 
PRIMARY  | 32  | datacube.tt.prodcode |1 | Using 
where  |
+++---++---+--+-+--+--+----------+
6 rows in set (0.01 sec)

Clint
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, 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 ((date(t.date) 
>= "2004-01-01" and date(t.date) <= "2004-01-31") and i.basename IN 
(select DISTINCT ii.basename from inventory ii, transaction tt, 
customer cc where ii.prodcode = tt.prodcode and cc.custcode = 
tt.custcode and ii.basename is not NULL and ii.prodname is not NULL 
and(date(tt.date) >= "2005-01-01" and date(tt.date) <= 
"2005-01-31"))) order by i.basename

Thanks
Sam

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

sub query is extermely slow

[sam wun]

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 ((date(t.date) >= 
"2004-01-01" and date(t.date) <= "2004-01-31") and i.basename IN (select 
DISTINCT ii.basename from inventory ii, transaction tt, customer cc 
where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and 
ii.basename is not NULL and ii.prodname is not NULL and(date(tt.date) >= 
"2005-01-01" and date(tt.date) <= "2005-01-31"))) order by i.basename

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

Re: create view not working on mysql 4.1.8

[sam wun]

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
mysql>use sample
mysql>create 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 manual but the syntax is as written above.we also saw error 1024 
in error notepad but we could not get any further details.
 please explain the fault in above statement
Yahoo! India Matrimony: Find your life partneronline.
 

Why don't you upgrade to 5.0? I use create view in 5.0 without any problem.
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Insert if Update failed without Select

[sam wun]

[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.  If you update the record with the exact same
information, mySQL will return a count of 0 rows updated.  Yet the row
exists.
 

Hi, how can I find out the return value from update if I execute update 
in perl dbi?
Here is my sample code:
$sql ="update transaction set salesvolume=$salesvol, netsales=$netsales,
   transtype=\"$transtype\", 
returnreason=\"$returnreason\"
   where prodcode=\"$prodcode\" and 
custcode=\"$custcode\" and date=$transdate";
$udpate_sql = qq {$sql;};
$sth = $dbh->prepare($update_sql) || die "prepare: $update_sql: 
$DBI::errstr";
$sth->execute || die "Unable to execute query: $dbh->errstr\n";
$sth->finish;
print $sql;

Currently I found somethingn strange with mysql(perhaps with perl dbi as 
well), after setup 1 or 2 test data, I tried to make a change to one of 
the fiield in a table, but after update is completed, I don't see the 
corresponding field in a record is updated by the new value.

If I copy the update statement exactly from the output of the print 
statement ( print $sql), and paste it to the mysql login prompt to 
execute it, the update statement  update the record instantly. I don't 
know what is happening here, why perl dbi does not do what the update 
statement supposed to do? Have I missed a commit statement? but I don't 
have idea how to place a commit statement to perl dbi.

Thanks
Sam
 Brad Eacker ([EMAIL PROTECTED])

 


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

Update does not doing updates

[sam wun]

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"
where prodcode="$prodcode" and 
custcode="$custcode" and date="$transdate";};
$sth = $dbh->prepare($update_sql) || die "prepare: $update_sql: 
$DBI::errstr";
$sth->execute || die "Unable to execute query: $dbh->errstr\n";
$sth->finish;

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

Insert if Update failed without Select

[sam wun]

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

Any example would be very apprecated.
Thanks
Sam.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Error in Insert on Duplicate Key Update

[sam wun]

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
   
qty=$qty,lastupdatedate=$date,prodname=$prodname,basename=$basename,
   vendorname=$vendorname,cost=$cost;};
   $sth = $dbh->prepare($insert_sql) || die "prepare: $insert_sql: 
$DBI::errstr";
   
$sth->execute($prodcode,$qty,$date,$prodname,$basename,$vendor,$value)
   || die "Unable to execute query: $dbh->errstr\n";

Error:
DBD::mysql::st execute failed: 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 'abc4_93419_06888,basename=Revlon, 
vendorname=,cost=' at line 3 at insert-inventory-mysql.pl line 55, 
 line 1.
Unable to execute query: DBI::db=HASH(0x864a988)->errstr

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

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

[sam wun]

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):
mysql -u root -p test -e 'drop view *'
Sam.
Thanks
Sam

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

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

[sam wun]

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: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: traverse the recordset more than once

[sam wun]

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 = $sth->fetchrow_arrayref
However when the "for" loop is finished, the pointer of the recordset 
is point to the end of the recordset. How can I revise the point to 
the beginning of the recordset ($sth)?

Another thing you might try is to Set
$arefgood = $sth->fetchrow_arrayref
$arefusethisone = $arefgood
Use the $arefusethisone in the for loop.  When you want to restart the 
for loop, just set "$arefusethisone = $arefgood" again.  Doing this, 
you should always have your original.  This will probably work in any 
language you are programming in, but since I don't program in to many 
languages, I can't give you a definite on that one.

Thanks I hvae thought about this method, but conerning that it  will 
require extra memory to keep the orginal copy of the recordset, and also 
whenever assign the original recordset to the temprecordset, it will 
need to take extra time to do the copies, I assumed this is not shadow 
copies.

Sam
Steve

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

Re: traverse the recordset more than once

[sam wun]

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 = $sth->fetchrow_arrayref
However when the "for" loop is finished, the pointer of the recordset 
is point to the end of the recordset. How can I revise the point to 
the beginning of the recordset ($sth)?

Kind of depends on what programing language you are writing the script 
in.  If you are using PHP, you might look into the reset function.  I 
"think" that might do what you want.  At least it is a startmaybe
http://us3.php.net/manual/en/function.reset.php


I m using Perl DBI.
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

traverse the recordset more than once

[sam wun]

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 finished, the pointer of the recordset is 
point to the end of the recordset. How can I revise the point to the 
beginning of the recordset ($sth)?

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

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

[sam wun]

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 archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

value of array to sql statement

[sam wun]

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 that assigned to the array, the sql 
statement only return result for  the first element of the array.
To illustrate the problem please see the code below.

The following perl code does return result when execute the SQL statement:
@outlets = ("07-6-0057","07-3-0051","07-2-0036");
my $mycustcode = "$outlets[1]"; # also work for [0] or [2]
$create_view_sql = qq {create view $viewtab as
select c.custcode, c.custname, c.type, sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and c.custcode = "$mycustcode"
group by c.custcode;};
However if I change the code to be  a bit more dynamic like the following:
In html.pl: # the following value (in the OPTION tag) will be submitted 
to the query1_result.cgi.

while ($aref = $sth->fetchrow_arrayref){
print "[0],>$aref->[1]: $aref->[2]\n";
}
In query1_result.cgi:
sub split_outlets_to_array
{
  my ($s) = @_;
  @outlets = split(',',$s);
  return @outlets;
}
$outlet_str = $in{'outlets'}; # the outlets contains a list of custcodes 
that seperated by comma.
@outlets_array = &split_outlets_to_array($outlet_str);
my $mycustcode = "$outlets_array[0]"; # but value of [1] and [2] can't 
make the following SQL statement return a result.

$sql = qq {
select c.custcode, c.custname, c.type, sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and c.custcode = "$mycustcode"
group by c.custcode;};
Summary:
The problem with the second case is that outlets_array[0] does make the 
SQL statement return result, but [1] and [2] does not.
With the first case (with hardcoded values), all elements of 
outlets_array does make the SQL return result.
There may be problem in the code $in{'outlets'}. However from printing 
each element of the array on the html page, I found nothing wrong with 
the value, they are all printed in the following format on the html page:
outlets[0]: 07-6-0057
outlets[1]: 07-3-0051
outlets[2]: 07-2-0036

I don't know what caused this error when execute it with the SQL statement.
I tried to turn on warninig with -w, but not sure how to see them when 
running in web browser.

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

Comparison SQL always return false

[sam wun]

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. 
@array[1]) and use this variable in the SQL statement, the SQL does not 
return any result.

For example:
The following code does not return any result
my $mycustcode = "@outlets[1]"; # this array contains string "07-2-0057"
However, if I use the following hard coded assignment, the SQL statement 
returns result:
# my $mycustcode = "07-2-0057";

$create_view_sql = qq {create view $viewtab as
 select c.custcode, c.custname, c.type, sum(t.netsales) 
as sales
 from customer c, transaction t
 where c.custcode = t.custcode
 and date(t.date) >= "$start_date"
 and date(t.date) <= "$end_date"
 and (c.type = "EXPORT" or c.type = "LOCAL")
 and (c.custcode = "$mycustcode")
 group by c.custcode;};

I may be using incorrect way to split the tokens, here is the function 
to extract a list of the token:
$outlet_str = $in{'outlets'}

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

Sum on Float type

[sam wun]

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 "Sub-Total"
from customer c, transaction t
where c.custcode = t.custcode
group by t.custcode
I don't know why the Sum function returns more than 2 precision number.
If I should not use Float type for the Cost field, what type should I use?
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]