Re: problem with InnoDB
On 9/7/06, Paul McCullagh [EMAIL PROTECTED] wrote: It sounds like you program allows ad-hoc queries, so why don't you just limit the number of rows returned by a select? For example you could limit the number of rows to 1001. If the server returns 1001, then display 1000 and tell the user there are actually more rows. The user should then apply further conditions. Some things worth mentioning when using LIMIT: In MySQL the LIMIT clause is applied just before sending the result to the client... so a SELECT col1, col2, ... , colN FROM tableName LIMIT x, y will be performed as SELECT col1, col2, ... , colN FROM tableName and before sending the result to the client the LIMIT will be applied... There are some things to consider... if you have an ORDER BY clause MySQL will stop sorting after LIMIT clause is satisfied... To skit the COUNT(*) query you must use: SELECT SQL_CALC_FOUND_ROWS col1, col2, ... , colN FROM tableName LIMIT x, y This way MySQL will store internally the number of rows that would have been returned without the LIMIT clause [The drawback is that if you have an ORDER BY clause MySQL will not stop after sorting LIMIT x,y rows... as I mentioned above] But the gain is that the second query that will return the number of rows without the LIMIT clause: SELECT FOUND_ROWS() will return instantly. -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Computing a column based on other columns
Hello, Is there a way to generate a column which computes a value from other columns in the same query? For example, I want to do something similar to: SELECT MIN(table.myvalue) as min, MAX(table.myvalue) as max, min/max as derived_column FROM table ORDER BY derived_column ; But MySQL doesn't like the references to the min/max aliases in the computation of the derived_column column. Is there a way to directly refer to the values generated by other columns in this manner? The example I gave is trivial, and theoretically I could generate derived_column with MIN(myvalue)/MAX(myvalue). But in practice, I'd like to use this to refer to columns which generate values from much more complex expressions. Thanks in advance, -- Dan
Re: Computing a column based on other columns
Dan Jakubiec wrote: Hello, Is there a way to generate a column which computes a value from other columns in the same query? For example, I want to do something similar to: SELECT MIN(table.myvalue) as min, MAX(table.myvalue) as max, min/max as derived_column FROM table ORDER BY derived_column ; This works on a table I have. SELECT MIN(tone) as `min`, MAX(tone) as `max`, MIN(tone)/MAX(tone) as ratio FROM pltone p I put the back tick mark around min and max alias, I'm not sure you need it but it is always a good idea anyway. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB my.cnf
Hi, Attached is the sample my.cnf for Innodb engine type. Thanks, ViSolve DB Team. - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 08, 2006 7:31 PM Subject: INNODB my.cnf Hi All, I need some inputs regarding my.cnf : We are using INNODB in our application.We have around 10 million records in the database. This will size up to around 10GB of data. Could you please suggest a sample my.cnf for this configuration. Machine used : Sun netra 240 , dual processor machine with 2 GB ram Mysql version : 5.1.11 Regards Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock default-storage_engine = innodb skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 8M net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K binlog_cache_size = 1M # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin=mysql-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = hostname # # The username the slave will use for authentication when connecting # to the master - required #master-user = username # # The password the slave will authenticate with when connecting to # the master - required #master-password = password # # The port the master is listening on. # optional - defaults to 3306 #master-port = port # # binary logging -
Re: Restarting MySQL on Solaris 8?
Hi, Try /usr/local/bin/mysqld_safe to start MySQL /usr/local/bin/mysqld_safe to start MySQL as a background process. Thanks. ViSolve DB Team. - Original Message - From: Jay Paulson [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 08, 2006 9:51 PM Subject: Restarting MySQL on Solaris 8? A couple questions since I'm not a Solaris person I really don't know how to do the following and was hoping that someone could help me out (Google isn't much help on this). How does one start the MySQL daemon on Solaris 8? (it's running MySQL 3.23.49) How does one tell Solaris 8 to start the MySQL daemon on boot? Lastly, tried running /usr/local/bin/safe_mysqld but got the following error: TIMESTAMP mysqld ended Thanks for any 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: Restarting MySQL on Solaris 8?
Hi, /usr/local/bin/safe_mysqld must do good with MySQL 3.23.49. The error may be due to either one. a. configuration file set up b. data directory - anything removed/modified - not enough disk space c. log file size Thanks, ViSolve DB Team. - Original Message - From: Visolve DB Team [EMAIL PROTECTED] To: Jay Paulson [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, September 11, 2006 9:35 AM Subject: Re: Restarting MySQL on Solaris 8? Hi, Try /usr/local/bin/mysqld_safe to start MySQL /usr/local/bin/mysqld_safe to start MySQL as a background process. Thanks. ViSolve DB Team. - Original Message - From: Jay Paulson [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 08, 2006 9:51 PM Subject: Restarting MySQL on Solaris 8? A couple questions since I'm not a Solaris person I really don't know how to do the following and was hoping that someone could help me out (Google isn't much help on this). How does one start the MySQL daemon on Solaris 8? (it's running MySQL 3.23.49) How does one tell Solaris 8 to start the MySQL daemon on boot? Lastly, tried running /usr/local/bin/safe_mysqld but got the following error: TIMESTAMP mysqld ended Thanks for any 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]
