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.
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:
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:
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...
Thank you for taking the time to visit my blog! Take a second to peek around and check out some of my previous posts. Of course, I would love to find out what you think as well, so make sure to comment. See you around!