In my series Learning the Yii Framework, I discuss the individual parts of the MVC (Model, View, Controller) architecture in some detail, from a Yii perspective. In the post on [intlink id=”659″ type=”post”]Controllers[/intlink], I introduce Access Control Lists (ACLs), Yii’s default way of restricting who can take what actions. This is a key part of the security of any Web application. For example, a site’s content can often be read by anyone at all, registered or non-registered users alike (like the text you’re reading now). Some content may only be viewable by registered users and some by registered users of a certain type (e.g., paid members). Finally, some content may only be viewable by administrators. In this post, I detail how to completely control access to your Web application using Yii’s Access Control Lists. Continue Reading…
Archives For MySQL
In a [intlink id=”826″ type=”post”]previous post[/intlink], I walk through the Yii framework’s built-in authentication system for adding login functionality to a Web site. There are a number of files and components involved, but simple authentication works fine out of the box, or with just a little tweaking. That’s the focus of that previous post. In this post, I explain how you can customize the authentication process further. Continue Reading…
I wanted to write up a strong post on Access Control Lists in Yii, for controlling who can do what in an application. I still will, of course, but since authorization depends upon authentication, I thought it necessary to discuss Yii’s authentication system first. And, as happens with me, in writing about that, and how you would [intlink id=”849″ type=”post”]customize the authentication process[/intlink], I had to break the subject down into two posts. So here’s the first of an informal three-part series on authentication and authorization in Yii. In this post, I discuss how the parts of Yii’s authentication system work together; much of what I say in this first post is also available in the Yii documentation, just not presented in this way. I also show a couple of quick ways to modify its behavior to suit your situation. Continue Reading…
A few months back I came across a resource for designing databases (it was posted in a MySQL discussion list). Database Answers has tons of sample designs (a.k.a., models) for various situations. What the site lacks in attractive design in more than makes up for in its usefulness. There are hundreds of designs for you to consider, covering everything from “dog whisperer” to event reservations to online banking. Definitely worth looking at when you’re thinking about the database scheme for your next project.
The performance of any Web application is greatly impacted by how well the database application is being used (if being used, of course). The four biggest impacts on performance are:
- The storage engine(s) used
- How columns are defined
- The existence of proper indexes
- How queries are written and executed
MySQL supports many different storage engines, each with its own strengths and weaknesses. Which you choose isn’t just a performance issue, it’s also one of features, like whether you need to support FULLTEXT searches (and therefore MyISAM tables) or transactions (which means InnoDB). Sometimes you can get away with using HEAP tables, which exist only in memory. There’s no permanent storage with HEAP tables, but the performance can’t be beat.
For the column definitions, the first goal is to use the smallest possible size for each column. This will be a limitation in your application, so the column’s size still needs to meet or exceed the largest possible value to be stored, but, generally speaking, the more data you want to possibly store, the more space will be required for all the data stored. You should also avoid allowing NULL values in columns. This is a factor in both good database design and in performance optimization.
The indexes you establish, and how they are used by queries, is a serious subject that requires a book in itself. To start, make sure you read MySQL’s own guide to how MySQL uses indexes. Second, learn to run your queries using EXPLAIN to see what, exactly, MySQL is doing when it executes that query. Also, of course, only select the data your application will actually need. No need to retrieve information that won’t be used. And always go back and double-check that your queries need all the clauses and references they’ve got. I’ve been known to leave legacy conditionals in queries that have no effect whatsoever.
For more on some of these topics, see the MySQL manual, of course (which has a section on optimization), the PlanetMySQL blog, and this article at DatabaseJournal.com. Also, if it applies to you, you can tweak MySQL’s behavior from a hardware/OS perspective. For an introduction to this subject, with respect to Linux, check out this article by Jeremy Zawodny, an expert on MySQL performance tuning.
