MySQL Subqueries and INNER JOINs 2009 Apr 3
Two advanced MySQL tips today:
- Never use a subquery inside a WHERE field IN(…) list
- How-to: Update values in a table from a calculation that uses the table’s own values, in a single query
Two advanced MySQL tips today:
How fast do your dynamic PHP/MySQL pages load? Answer: probably not fast enough. A truly optimized website should serve pages instantaneously to its visitors. There should be no lag time, even 200 milliseconds of server-side script execution time should be considered a burden worth removing.
The keyword for performance is “caching.” By taking the load off the processor and database, a website can easily handle hundreds of thousands of visitors. Many frameworks offers some sort of caching method that stores the final rendered HTML. This is easy to implement for anonymous users, but what about logged-in users?
I recently released an open-source Drupal module called Authenticated User Page Caching (Authcache) (and a demo site) for logged-in users. Drupal is a great PHP/MySQL CMS/framework for building community-based websites, though it suffers from a “one-more-query” syndrome–a single page request can have hundreds of SQL queries! The reason why authenticated caching is difficult is because most of the time, when you’re logged into a website, the content changes according to your account (like a “You’re logged in as [username]” link), so it’s pointless to save the final HTML to the server’s cache. Instead the entire page has to be rendered by PHP on each request, requiring precious CPU cycles & database hits. Ajax/JavaScript can be used for customizing the cached HTML, however. Of course, this technique can’t be used with every website–some sites are just too dynamic–but it can work if the content of pages is similar across users.
Everyone knows backups are important, not just for restoring from a system failure, but also for fixing user/admin/developer mistakes. Here’s an efficient way to automatically backup files on your Linux/CentOS server using a spare Windows or Linux machine. You should already have a basic grasp of the Linux shell prompt for this guide.
<style> tags. For this reason, use inline CSS. Also, using tables to build your newsletters is a safe idea as well, since the float property isn’t always supported. Outlook 2007 is extremely limited when rendering HTML–no background CSS images, no forms, no custom bullet images.
mail() function – This function opens and closes a connection to your mail server every time, which is extremely inefficient. It is possible to write your own bulk email sender in PHP, however! Use an SMTP class for more control and speed.
Received-SPF: neutral or Received-SPF: fail. It should only take 5 minutes to set this up and make it say “pass.” For example, my domain register allows me to add DNS TXT records, so for the host field I would type “httpremix.com.” and for the address field I would add "v=spf1 a mx -all" (quotes included for the TXT).
ORDER BY FIELD and GROUP_CONCAT() 2008 Jun 12I love databases because I always feel there is a way for retrieving my data in any shape or form I desire. Trying to find the correct syntax can sometimes be frustrating, however. Here are two tricks that have helped me during those special times:
FIELD() function comes in handy.
The disadvantage is that this will slow down your query. For maximum performance, store the field as an ENUM with the values defined in the correct order.
Scenario: We have multiple values spread out across rows a table, but need to retrieve them together in a single field without performing multiple queries. I'm doing this for my archive page--I want to retrieve a list of topic tags for each article, but I want to do it with the same query that selects all my articles. GROUP_CONCAT() is the answer for string concatenation.
The "topic" column could then return values such as "php,mysql,css".
What if you need to grab a string from text, such as the name of the department below:
Here's a quick way to pick out that string: