Davy's Blog

MySQL Optimize Top 21 Tips

1. Optimize Your Queries For the Query Cache Most MySQL servers have query caching enabled. It’s one of the most effective methods of improving performance, that is quietly handled by the database engine. When the same query is executed multiple times, the result is fetched from the cache, which is quite fast. The main problem is, it is so easy and hidden from the programmer, most of us tend to ignore it. Some things we do can actually prevent the query cache from performing its task. vie...

MySQL group by day, month or year

Using the DATE_FORMAT operator, you can easily group the timestamp, date or datetime column using any format you want. For example, I needed to group rows that were added on the same day. Here is my query: This query will give you result like this : Group by month: Group by year:

php5.3 and mysqlnd old authentication issue

After upgrading to php5.3, i got this problem: Here is what i found: * php 5.3 now use a native extension to use mysql : mysqlnd * my database credential was old (4 years old maybe) and still use the old mysql authentication * mysqlnd needs the new 41bytes password The problem was easy to solve: First, open '/etc/my.cnf' in Linux or 'my.ini' in Windows, if you set 'old_passwords=1' before, please delete it. Then, reset the user's password:

How to Find and Replace Text in MySQL Database using SQL

MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be replaced by new string (to_string). This is useful if there is need to search and replace a text string which affects many records or rows, such as change of company name, postcode, URL or spelling mistake. The syntax of REPLACE is REPLACE(text_string, from_string, to_string) MySQL reference describes REPLACE as function that returns the string text_string wit...

Using mysql to replace word in database

Table：mt-entry subject: entry_text search：abc replace：def