Tips Mysql

case-sensitive-query

Make a case-sensitive query
----------

One way:
SELECT *  FROM `table` WHERE BINARY like '%value%'

Other way:
SELECT *  FROM `table` WHERE `column` like '%value%' COLLATE utf8_bin

get_schema_db.sql

//get schema table
SELECT table_name, table_type, engine 
FROM information_schema.tables 
WHERE table_schema =  "<database_name>"
ORDER BY table_name DESC;

get_schema_size

SELECT  sum(round(((data_length + index_length) / 1024 / 1024 / 1024), 2))  as "Size in GB" FROM information_schema.TABLES  WHERE table_schema = "<database_name>"

new_gist_file_6

Optimize mysql

Analyse errors:
$ show engine innodb status

one-table-multiple-ts

One Mysql Table with Multiple TIMESTAMP Columns
----------

You can initialize or update any TIMESTAMP column to the current
date and time by assigning it a NULL value, unless it has been defined with 
the NULL attribute to permit NULL values.

show_process.mysql

//mostrar processos, a correr no mysql:
show full processlist

///////////////////////////////////////////////////////

//analisar querys
SET profiling = 1;

//list querys
SHOW PROFILES;

//see what query is doing
SHOW PROFILE FOR QUERY [id_query];

timezones_load_info

Load timezones tables with information: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

----------

By default, (at least on Debian-based installations) no time zone data is loaded into MySQL. If you want to test if they are loaded, try executing:

SELECT CONVERT_TZ('2012-06-07 12:00:00', 'GMT', 'America/New_York');
If it returns a DATETIME (in this case 2012-06-07 08:00:00), you have time zones loaded. If it returns NULL, they aren't. When not loaded, you are limited to converting using offsets (e.g. +10:00 or -6:00).

This should work fine in many cases, but there are times when it is better to use named time zones, like for not worrying about daylight savings time. Executing the following command loads the time zone data from the system (Unix-only. I'm not sure what the equivalent Windows command would be):

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

If you need to continually rely on MySQL time zones, the above command should be executed every time the system time zone is updated. You could also just add it to a weekly or monthly cron job to do it for you automatically.

Then, to view a list of time zones, just do the following:

USE mysql;
SELECT * FROM `time_zone_name`;
Note, the time zone info takes up about 5 MB in MySQL. If you ever want to un-load the timezone info, just execute the following and restart MySQL:

TRUNCATE `time_zone` ;
TRUNCATE `time_zone_leap_second` ;
TRUNCATE `time_zone_name` ;
TRUNCATE `time_zone_transition` ;
TRUNCATE `time_zone_transition_type` ;
Do not DROP these tables or bad things will happen.