Get a perfect Execution time of each query in MySQL

coming soon

Query to get size of Index,Data in mysql

Hi,

SELECT table_schema AS DatabaseName,
TABLE_NAME AS TableName,
CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , ' MB' ) data_length,
CONCAT( ROUND( index_length / ( 1024 *1024 ) , 2 ) , ' MB' ) index_length,
CONCAT( ROUND( ROUND( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'MB' ) total_size_table
FROM information_schema.TABLES
ORDER BY data_length DESC;

T n R
Kamesh Shah

MySQL Charset and Collations

You need to have a good apprehending of what is unicode,encoding ??


VARIABLES WORKING IN MYSQL TO FORM A PERFECT COLLATION AND CHARSET


1) To know server Charset and Collation  
--character-set-server
--collation-server

SHOW VARIABLES LIKE 'Character_set_server'; 
SHOW VARIABLES LIKE 'Collation_server';

This parameters can be set at OS level

2) To know DB Charset and Collation 


SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

3)  TO Know Table Charset and Collation


SELECT TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE LOWER(TABLE_SCHEMA)='<DB_Name>' AND 
LOWER(Table_Name)='<Table_Name>';

4) TO Know Column Charset and Collation(For All columns)


SHOW FULL FIELDS FROM `<DB_Name>`.`<Table_Name>`;

5) VARIABLE PLAYS PART IN MYSQL 


Client side Charset how to know


SHOW VARIABLES LIKE 'character_set_client'; 

After receiving the statment server uses 2 parameters


character_set_connection 
collation_connection

Server use this Charset to send result and errors

Character_set_results




SET NAMES <charset name> COLLATE <collation_Name> will send the charset to send and receive the data from clients


We can say 


SET NAMES <charset name> IS same as 


SET character_set_client = <charset name>;
SET character_set_results = <charset name>;
SET character_set_connection = <charset name>;




When a client connects to the server, it sends the name of the character set that it wants to use. The server uses the name to set the character_set_client, character_set_results, and character_set_connection system variables.


If you do not want the server to perform any conversion of result sets or error messages, set character_set_results to NULL or binary:

Unveiled wildcard ' := ' in mysql

Hi,


update some_table
set col = col + 1
where key = 'some_key_value'
and @value := col_name


In this statement operator := will be always evaluated as TRUE no matters and second thing  it will store the value of the updated row before updation (dat means before updation what the value was).In query we can use for swapping