MYSQL : Tuning / Optimizing my.cnf file

Posted by Jiltin     Tags: , 16 August, 2009    20,747 views    (4) Comment

In my new server, I Have to do some fine tuning of MySQL 5 and here is what my.cnf file looks like for a 2GHz machine with 12GB of memory. Below are notes on some of the important variables, I took down while tuning the config file. 1. query_cache_size: * MySQL provides one feature that can prove very handy – a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the Read Full Article (Click here)...

Categories : MySQL, Web & Scripts Tags : ,

How to Default Timestamp and/or Update timestamps with MySQL?

Posted by Jiltin     Tags: 9 July, 2009    3,428 views    (1) Comment

A lot of relational tables need created and update timestamps columns. I prefer having them for all tables with no exception. However, most of applications I am working on are running MySQL. MySQL has minor limitation on timestamps. Unfortunately you can create only one time stamp column that has DEFAULT NOW() value. Read more to see how to avoid this limitation. The simplest way to do this is create the following columns in the table: stamp_created timestamp default now(), stamp_updated Read Full Article (Click here)...

Categories : MySQL Tags :

Common MySQL Queries with MySQL 5&6

Posted by Jiltin     Tags: 17 April, 2009    1,064 views    (2) Comment

This is not my own script. I found this very useful from the net and copied here or my reference: Basic aggregation This is the simplest grouping query pattern. For column foo, display the first (smallest), last (largest) or average value of column bar: SELECT foo, MIN(bar) AS bar FROM tbl GROUP BY foo Return the highest bar value for each foo, ordering top to bottom by that value: SELECT foo, MAX(bar) AS Count FROM tbl GROUP BY foo ORDER BY Count DESC; Ditto for AVG(), COUNT() etc. The Read Full Article (Click here)...

Categories : MySQL Tags :

MySQL snippets – CREATE TABLE statement sample as bash shell.

Posted by Jiltin     Tags: , 15 April, 2009    1,477 views    (0) Comment

The CREATE TABLE statement is used to create a table in MySQL. Syntax: CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, …. ) sample #!/bin/sh # # Developed by notesbit # Shows all users and the user status # #mysql> desc user_transactions; #+——————+————-+——+—–+——————-+——-+ #‘ Field     Read Full Article (Click here)...

Categories : MySQL, Web Mysql Tags : ,

How to install LAMP (Linux, apache, php, mysql) – Blogging – Make It Easy?

Posted by Jiltin     Tags: , , , , 24 March, 2009    8,222 views    (1) Comment

How to install LAMP (Linux, apache, php, mysql)? Here is the simple way to create your own LAMP and starting blogging right away. The easiest way to install LAMP using yum program in CentOS. Below are the steps to install LAMP (Apache, PHP and MySQL in Linux) using Yum:- * Go to CentOS and download a copy of CentOS Linux and install it (I will not go thru the how to install CentOS with you here – ) * Once your CentOS is installed. Open a terminal (if you are using X-Window), and type Read Full Article (Click here)...

Categories : MySQL, Scripts Unix, Web & Scripts Tags : , , , ,