MySQL Tips and Tricks

1. String Data type change Query

ALTER TABLE table_name MODIFY remark VARCHAR(65535);

the above query is medium text data type column should return on describe the

2.Mysql CAST()

The CAST() function in MySQL is used to convert a value from one data type to another data type specified in the expression. It is mostly used with WHERE, HAVING, and JOIN clauses. This function is similar to the CONVERT() function in MySQL. It converts the value into a DATE datatype in the "YYYY-MM-DD" format.

> mysql> SELECT CAST(1944.35 AS YEAR), CAST(1944.50 AS YEAR);
> +-----------------------+-----------------------+
> | CAST(1944.35 AS YEAR) | CAST(1944.50 AS YEAR) |
> +-----------------------+-----------------------+
> |                  1944 |                  1945 |
> +-----------------------+-----------------------+
> 
> mysql> SELECT CAST(66.35 AS YEAR), CAST(66.50 AS YEAR);
> +---------------------+---------------------+
> | CAST(66.35 AS YEAR) | CAST(66.50 AS YEAR) |
> +---------------------+---------------------+
> |                2066 |                2067 |
> +---------------------+---------------------+

3.Index and Use Same Column Types for Joins

If your application contains many JOIN queries, you need to make sure that the columns you join by are indexed on both tables. This affects how MySQL internally optimizes the join operation.

Also, the columns that are joined, need to be the same type. For instance, if you join a DECIMAL column, to an INT column from another table, MySQL will be unable to use at least one of the indexes. Even the character encodings need to be the same type for string type columns.

// looking for companies in my state
SELECT company_name FROM users
    LEFT JOIN companies ON (users.state = companies.state)
    WHERE users.id = $user_id";

// both state columns should be indexed
// and they both should be the same type and character encoding
// or MySQL might do full table scans

4. Do Not ORDER BY RAND()

This is one of those tricks that sound cool at first, and many rookie programmers fall for this trap. You may not realize what kind of terrible bottleneck you can create once you start using this in your queries.

If you really need random rows out of your results, there are much better ways of doing it. Granted it takes additional code, but you will prevent a bottleneck that gets exponentially worse as your data grows. The problem is, MySQL will have to perform RAND() operation (which takes processing power) for every single row in the table before sorting it and giving you just 1 row.

> SELECT username FROM user ORDER BY RAND() LIMIT 1

So you pick a random number less than the number of results and use that as the offset in your LIMIT clause.

5. Autoincrement: Direct Reset

One of the great default features of MySQL is the Autoincrement Column Index. When making a new row, the program will automatically calculate and apply the values to the table. However, this default value is not always needed in its default settings. A few helpful commands to change the settings that everyone will eventually need can be easily applied.

ALTER TABLE [table name] AUTO_INCREMENT =1;

6. Use Your Indexes to Create Tables

Save time in this process by killing two birds with one stone. An index can actually be created while the table itself is being created.

CREATE TABLE records (
name VARCHAR(50), 
age INT,
id_num INT, INDEX (id)

)

To create the index on multiple columns

ALTER TABLE (records ADD INDEX id(id_num), 
ADD INDEX name(name);

The Wrap-Up