memory issues for large table query
I'm trying to get a handle on how an increase inf physical memory will affect the query time for a large table. Here's the set up. I'm running MySQL 3.23.37 on RedHat 6.0. The server has a 700Mhz process with 1.5 Gig of RAM. I'm querying a table with 47 million plus records that has 3 columns (int primary key, col 1 char(12), col 2 char(10)). I have the primary key index, an index on (col 1, col 2), and an index on col 2 only. I do searches on col 1 only, col 1 and 2, col 2 only. My key buffer is set at 700 MB. When I run a query SELECT count(*) as count FROM tbl where col_1 like 'value1', the query can take up to 45 minutes to bring back a result if there are large number (sometimes 1 million plus) of records that match 'value 1'. I've read the chapters in the manual about query optimisation and tuning the server and I'm trying to figure out what the best approach would be to speed up the query. Would putting more memory in the machine and increasing the key_buffer size solve the problem? Should I look at a processor upgrade as well? Are there any other suggestions that can make the query execute faster? Thanks in advance, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
show processlist: status
I'm trying to understand what the status field of the SHOW PROCESSLIST command means. When I execute a query - SELECT count(*) as count from table1 where col1 like name%;, the query takes a long time to execute. The table has over 47,000,000 records and is indexed on col1. What does the status sending data mean? It doesn't seem like there is that much data that needs to be sent for a count query. Thanks, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query optimization
I'm trying to figure out how to optimize a query on a fairly large table. I've been reading the MySQL documentation and have tried a few of the suggestions, but not seem to have much effect on the query. The table contains three columns: 1) int unsigned 2) char(12) 3) char(10) The table has 47,000,000 + rows. I have my key_buffer set to 512M. I have a primary key index on column 1, a multicolumn index (col2,col3) and another single column index for col3. The columns are not unique, in fact, some column 2 has as may as 1,000,000 rows with the same value. I am running MySQL 3.23.37 on RedHat 6.0 with 1 Gig memory and a single 700 Mhz processor. The hard drive is a single 18 Gig SCSI drive. I am searching on col 2 only, col 3 only, or col 2 and col 3. The queries on single columns can take as long a 6 minutes to execute. Any suggestions on how I can further optimize the setup to get better results? Thanks, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQLGUI file save function
I am using MySQLGUI 1.7.5-1 on Windows 2000. When I save a query result to a file it seems to save it as a Hex output. Is this what is supposed to happen? Can this be used to backup a database? Thanks in advance. Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQLGUI file save function
I am fairly new to MySQL and I am unfamiliar with the term escaped form. Can anyone point me to documentation that explains what that is and how it can be used. Also, I am trying to perform a backup remotely. My understanding is that the mysqldump and mysqlhotcopy commands can only be used on the local machine. Is this correct? What is the best way to perform a backup remotely. Thanks, Jeff - Original Message - From: Sinisa Milivojevic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, August 30, 2001 9:29 AM Subject: Re: MySQLGUI file save function Jeff Isom writes: I am using MySQLGUI 1.7.5-1 on Windows 2000. When I save a query result to a file it seems to save it as a Hex output. Is this what is supposed to happen? Can this be used to backup a database? Thanks in advance. Jeff It will save query results in a file in escaped form. For backup use mysqldump or mysqlhotcopy or BACKUP TABLE command. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Backing up a MySQL database on a remote server
I'm new to MySQL and I'm sure this has been covered, but I can't find it so I'll ask here. What is the best way to backup a MySQL database that is hosted on a remote server. Our web site is hosted by a hosting company and they configured MySQL. I want to backup the database periodically. I've tried using PhPMyAdmin to get data dumps, but the databases are quite large and the browser times out before the file download begins. How can I connect to the MySQL server from my Win2000 machine and perform a dump to my hard drive? Also, what is the best way to reverse the process and restore the dump from my hard drive to the remote server. I apologize if this question is obvious. I've looked in the documentation, but haven't been able to figure it out. Thanks in advance for the help. Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
