Linux command to check the number connections to mysql server
///check the number of connections to database netstat -antp | grep :3306 | wc -l
sweet
satishgaudo.com
Understanding technology
Archive for the 'Mysql' Category
HAVING Clause
The HAVING clause was added to SQL because the WHERE clause could not be used with aggregate functions. Syntax: SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value Example: SELECT customer,SUM(orderprice) FROM trans_orders GROUP BY customer HAVING SUM(orderprice)
Wow, your post makes mine look fbeele. More power to you!
ALTER TABLE for table indexing:Example
ALTER TABLE `listing` ADD INDEX `idx_uid_type` ( `uid` , `listing_type` ) ; ALTER TABLE `users` ADD INDEX ( `email` ) ; ALTER TABLE `listing_reply` ADD INDEX ( `listingid` , `uid` , `parent_listing` ) ;
Export the query result in a temporary file
select video_file from videos INTO OUTFILE ‘/tmp/video1.txt’; Query Above exports all the rows for coulmn video_file into a text file “/tmp/video1.txt” .
shell script to optimize database
DB_LIST=”$(mysql -u root -Bse ’show databases’ | egrep -v ‘information_schema|mysql|test’)” for db in ${DB_LIST[@]} do TABLENAMES=”$(mysql -u root $db -Bse ’show tables’)” echo “Database: “$db for TABLENAME in ${TABLENAMES[@]} do mysql -u root $db -Bse “optimize TABLE $TABLENAME;” echo $TABLENAME” table has been optimized” done echo $db – $TABLENAME “Optimized has been completed” done echo “All Databases have been successfully Optimized”
shell script to Analyze database
DB_LIST=”$(mysql -u root -Bse ’show databases’ | egrep -v ‘information_schema|mysql|test’)” for db in ${DB_LIST[@]} do TABLENAMES=”$(mysql -u root $db -Bse ’show tables’)” echo “Database: “$db for TABLENAME in ${TABLENAMES[@]} do mysql -u root $db -Bse “Analyze TABLE $TABLENAME;” echo $TABLENAME “Analyze has been done” done echo $db – $TABLENAME “Analyzis has been completed” done echo “All Databases have been successfully Optimized”