I was asked for help in optimizing a MySQL query where flags are stored in a database and references should be counted based on the flag value. Sounds not complicated at first, but there are several flags that should be counted and also just once per reference. A lot of food for GROUP BY you may think. Having said this, search and group for flags in this table would be really slow due to a very poor cardinality. But let's start with the actual problem. The example is fictitious, but I did my best to find a general use case for this problem. Read the rest of the article »
Dealing with large data sets makes it necessary to pick out only the newest or the hottest elements and not displaying everything. In order to have older items still available, Pagination navigation's have become established. However, implementing a Pagination with MySQL is one of those problems that can be optimized poorly with MySQL and certainly other RDBM systems. However, knowing the underlying database can also help in optimizing pagination queries, because there is no real copy and paste solution. Read the rest of the article »
If you look at the number of visitors of your website, you can only take the data of the previous days for a serious analysis. In itself logical, but it would also be desirable to know which traffic you can expect today, based on the expectation of the day. For the statement about the expected traffic, you can use a simple average, but this has no real significance. A better approach would incorporate all available information. Read the rest of the article »
There are many nasty pagination implementations and certainly sooner or later I've also written such a cruelty. That's why I've been working on a generalized solution for long in order to combine the variety of needs that are imposed on such a navigation to finally put an end to blocks of code that are copied from one project to another with the consequence that it gets no longer understood over the time. Read the rest of the article »
A few years ago, Jan Kneschke came up with
the idea of using lighttpd's X-Sendfile to send dynamic content
without copying it several times. I liked the idea and used it as basis of my
framework. It seems, there are now some immature implementations of this idea
on the lighttpd bug tracker. All of these implementations also use the shared
memory, with the difference that I haven't used PHP's tempfile()
function, but rather exported lighttpd's client-file-descriptor to the PHP
scope in order to use it as the file-name of the temporary file. It could also be the IP or something else but the client-fd is the most unique identifier inside of the webserver <-> PHP construct. Read the rest of the article »
It took me a while but here's a new toy. Today I publish my own PHP fork
based on the PHP 5.3.6 code base with a few changes that make the everydays
developer life more bearable. It includes some of the patches I've already
published about 3 years ago, my defcon extension and also my infusion extension
plus a good bunch of extra gimmickries. Read the rest of the article »
In a previous article, I've already talked about an optimized way to connect locations in a geographic point of view by using MySQL. In this manner, locations of pubs, drugstores, barbers or even users can be obtained. Communities, or perhaps I should use the newer term Social Networks, make use of the buddy network of indiviual members in addition to the geographical mapping. This has many psychological advantages, because new members can be integrated in an established network very easily and I'm more willing to become involved when I already know some of the members. Read the rest of the article »
There were times, in which table layouts and pixel-bungling was part of the daily life of a web developer. Fortunately, those days are past and nearly every layout can be implemented with XHTML or better HTML5 and CSS only. A nice side effect is that, indirectly, the loading speed of the complete site is reduced by lowering the number of HTTP requests. Read the rest of the article »
I've just started learning MySQL's internals but I've got an idea which I want to convert to a question here, to ask people who are already deeper in it. Is it a bad idea to completeley avoid query parsing on the server side and use a binary protocol instead? This way the client parses the query and could cache the statement structure for further usage or another client API uses a NoSQL approach to send the request data to the server. Read the rest of the article »
The business-logic should not be the task of a database server, because hardware of database servers is mostly more expensive than the sheet metal of some stupid application servers. The other problem are inconsistencies if you change the code-base on a large database cluster very often. You could reduce the risk of inconsistency with batches, but that does not solve the problem completely. However, I've developed a handful of stored functions to simplify queries, reduce index sizes and to shorten strings where only a small portion is needed, which in turn saves a lot of bandwidth. Read the rest of the article »