background preloader

Top 20+ MySQL Best Practices

Top 20+ MySQL Best Practices
Database operations often tend to be the main bottleneck for most web applications today. It's not only the DBA's (database administrators) that have to worry about these performance issues. We as programmers need to do our part by structuring tables properly, writing optimized queries and better code. In this article, I'll list some MySQL optimization techniques for programmers. Before we start, be aware that you can find a ton of useful MySQL scripts and utilities on Envato Market. Most MySQL servers have query caching enabled. The main problem is, it is so easy and hidden from the programmer, most of us tend to ignore it. The reason query cache does not work in the first line is the usage of the CURDATE() function. Using the EXPLAIN keyword can give you insight on what MySQL is doing to execute your query. The results of an EXPLAIN query will show you which indexes are being utilized, how the table is being scanned and sorted etc... After adding the index to the group_id field:

http://code.tutsplus.com/tutorials/top-20-mysql-best-practices--net-7855

LiveDocx in PHP - Zend_Service_LiveDocx Generating print-ready well-formatted PDF documents with PHP is not an easy task. Traditionally, there are two main approaches to PDF generation with PHP. Given sufficient time and patience, both partially get the job done, but still leave a lot to be desired: HTML-to-PDF: This approach is widely used in mainstream applications. Firesheep - codebutler When logging into a website you usually start by submitting your username and password. The server then checks to see if an account matching this information exists and if so, replies back to you with a "cookie" which is used by your browser for all subsequent requests. It's extremely common for websites to protect your password by encrypting the initial login, but surprisingly uncommon for websites to encrypt everything else. This leaves the cookie (and the user) vulnerable. HTTP session hijacking (sometimes called "sidejacking") is when an attacker gets a hold of a user's cookie, allowing them to do anything the user can do on a particular website.

Advanced MySQL Query Tuning In this article we will talk about query optimization with the focus on the queries with GROUP BY and ORDER BY. We will start with the basic concepts (Indexes, Explain plan in MySQL, etc) and then will talk about the advanced query optimization techniques. We will cover “loose index scan” and “tight index scan” optimizations and show the benchmark results. Indexes In this article we will focus on the b-tree indexes only (InnoDB and MyISAM only support B-tree indexes). Figure 1 illustrates a basic b-tree index implementation.

27 Examples of Impressive Resume(CV) Designs Learn how to earn $125 or more per hour as a freelancer - Click Here Looking for hosting?. We recommend MediaTemple for web hosting. Use Code MTLOVESDESIGN for 20% off Ten PHP Best Practices Tips that will get you a job Posted by blake on Jun 4, 2008 in Code, Performance, PHP | 167 comments The last couple of weeks have been quite the experience for me. I was part of a big layoff at my former company, which was interesting. I've never been in that position before, and it's hard not to take it personally. I started watching the job boards, and a nice-looking full-time PHP position caught my eye, so I sent out a resume and landed an interview. Before the face-to-face portion, I chatted with the owner and head programmer on a conference call, and they ended up sending me a technical assessment quiz.

7 Secure, Lightweight, and Easy to Use PHP Frameworks Choosing a good PHP frameworks can help you develop complex Rich Internet Applications quickly, with a best practices oriented approach, and saving a lot of time reusing code snippets that are already available. There are a lot of interesting PHP frameworks you can choose for your next web project. Which framework you choose is really a personal decision. There is no one “best” framework on the market at the moment. Different frameworks are better for different types of projects, and for different developer. Today we will focus on 7 secure, lightweight and easy to use PHP Frameworks. How to bypass strict firewalls on public wifi hotspots and restricted networks, by tunneling blocked ports and protocols - verot.net Public wifi hotspots and restricted internet access More and more, you can find public wireless hotspots, in cities, train stations, airports... and even some public hotspots that are available with a subscription, accessible through a web login form. The thing is, most of the time, these hospots will have a reduced connectivity. Only some ports and protocols will be allowed.

Using EXPLAIN to Write Better MySQL Queries When you issue a query, the MySQL Query Optimizer tries to devise an optimal plan for query execution. You can see information about the plan by prefixing the query with EXPLAIN. EXPLAIN is one of the most powerful tools at your disposal for understanding and optimizing troublesome MySQL queries, but it’s a sad fact that many developers rarely make use of it. In this article you’ll learn what the output of EXPLAIN can be and how to use it to optimize your schema and queries. Understanding EXPLAIN’s Output Using EXPLAIN is as simple as pre-pending it before the SELECT queries.

Breathtaking Long Exposure Photography and How to Capture It As you know, our first free e-Book was launched and so we were all, including me, busy making Photoshop resources. Thanks to you, the e-Book has met with a landmark success. Now I am free to focus on the other subjects as well. For today, I chose a form of trick photography. So after many weeks, here’s a super tutorial for learning Long Exposure Photography. As the name suggests, long exposure photo is when the camera’s image sensor was exposed to light for a longer period of time than usual. PHP Optimization Tips This tutorial will explain many small techniques which will, hopefully, help optimize your php scripts. I considered myself a bit of a PHP pro until I started researching some of this stuff and realized that there is a whole realm of information out there about optimizing php that I didn’t know about. I hope you will be as surprised as I was about some of the things you might learn from this article. Output of Data So first off lets start with outputting data to the user.

PHP command line syntax checking Posted April 24th, 2010 in PHP The PHP CLI (command line interface) allows basic syntax checking of a PHP file. I'll show the basic usage of this in this post and then a couple of ways to check all files in a directory, and all files recursively down the directory tree as posted by a couple of people on Twitter. Syntax checking a single PHP file from the command line The syntax for checking is like this, where the -l flag is a lower case L:

Narada's Fluxbox Guide (Page 1) / Applications & Desktop Environments Narada's Fluxbox Guide What is the point of this guide? To provide you with a quick walkthrough and introduction to a functional Fluxbox setup. What is Fluxbox, you ask? 32 Tips To Speed Up Your MySQL Queries If you are interested in how to create fast MySQL queries, this article is for you Use persistent connections to the database to avoid connection overhead. Check all tables have PRIMARY KEYs on columns with high cardinality (many rows match the key value). Well,`gender` column has low cardinality (selectivity), unique user id column has high one and is a good candidate to become a primary key. All references between different tables should usually be done with indices (which also means they must have identical data types so that joins based on the corresponding columns will be faster).

Related: