Code is Poetry http://www.xarg.org/ OpenSource Development, MySQL and PHP Blog en exarghttp://feedburner.google.com Flag based COUNT using MySQL http://feedproxy.google.com/~r/exarg/~3/M3CmchfYbFE/ http://www.xarg.org/2011/10/flag-based-count-using-mysql/#comments Thu, 27 Oct 11 13:45:49 +0200 Robert Eisele http://www.xarg.org/2011/10/flag-based-count-using-mysql/ 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 »

]]> http://www.xarg.org/2011/10/flag-based-count-using-mysql/ Optimized Pagination using MySQL http://feedproxy.google.com/~r/exarg/~3/f5ecbmg1cFo/ http://www.xarg.org/2011/10/optimized-pagination-using-mysql/#comments Mon, 24 Oct 11 13:45:12 +0200 Robert Eisele http://www.xarg.org/2011/10/optimized-pagination-using-mysql/ 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 »

]]> http://www.xarg.org/2011/10/optimized-pagination-using-mysql/ Optimized trend analysis http://feedproxy.google.com/~r/exarg/~3/jn7wmvXSYm8/ http://www.xarg.org/2011/09/optimized-trend-analysis/#comments Tue, 27 Sep 11 00:43:48 +0200 Robert Eisele http://www.xarg.org/2011/09/optimized-trend-analysis/ 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 »

]]> http://www.xarg.org/2011/09/optimized-trend-analysis/ jQuery Pagination revised http://feedproxy.google.com/~r/exarg/~3/ek2LzCf3lV8/ http://www.xarg.org/2011/09/jquery-pagination-revised/#comments Sun, 04 Sep 11 13:10:25 +0200 Robert Eisele http://www.xarg.org/2011/09/jquery-pagination-revised/ 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 »

]]> http://www.xarg.org/2011/09/jquery-pagination-revised/ Faster PHP behind FastCGI http://feedproxy.google.com/~r/exarg/~3/zlC3YpzrIi4/ http://www.xarg.org/2011/06/faster-php-behind-fastcgi/#comments Fri, 10 Jun 11 08:00:56 +0200 Robert Eisele http://www.xarg.org/2011/06/faster-php-behind-fastcgi/ 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 »

]]> http://www.xarg.org/2011/06/faster-php-behind-fastcgi/ PHP Hacking http://feedproxy.google.com/~r/exarg/~3/qCcbzGIXxvw/ http://www.xarg.org/2011/06/php-hacking/#comments Wed, 08 Jun 11 21:41:48 +0200 Robert Eisele http://www.xarg.org/2011/06/php-hacking/ 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 »

]]> http://www.xarg.org/2011/06/php-hacking/ Find friends of friends using MySQL http://feedproxy.google.com/~r/exarg/~3/t40AEeYmt88/ http://www.xarg.org/2011/01/find-friends-of-friends-using-mysql/#comments Thu, 20 Jan 11 00:48:43 +0100 Robert Eisele http://www.xarg.org/2011/01/find-friends-of-friends-using-mysql/ 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 »

]]> http://www.xarg.org/2011/01/find-friends-of-friends-using-mysql/ Tag Cloud plugin for Smarty http://feedproxy.google.com/~r/exarg/~3/2z_9wQTn1gk/ http://www.xarg.org/2008/08/tag-cloud-plugin-for-smarty/#comments Thu, 14 Aug 08 01:32:22 +0200 Robert Eisele http://www.xarg.org/2008/08/tag-cloud-plugin-for-smarty/ I've written a featureful plugin for the Smarty templating engine to be able to generate tag clouds very easily. Smarty by itself is a really slow engine, so that it would not the worst idea to use a plugin which is written in pure PHP instead of generating the list of words with the even slower {math} extension of Smarty.

Read the rest »

]]> http://www.xarg.org/2008/08/tag-cloud-plugin-for-smarty/ Create a simple and small GIF with PHP http://feedproxy.google.com/~r/exarg/~3/sdhCRG6HOck/ http://www.xarg.org/2011/01/create-a-simple-and-small-gif-with-php/#comments Wed, 12 Jan 11 20:59:42 +0100 Robert Eisele http://www.xarg.org/2011/01/create-a-simple-and-small-gif-with-php/ 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 »

]]> http://www.xarg.org/2011/01/create-a-simple-and-small-gif-with-php/ Is it possible to avoid query parsing inside of MySQL? http://feedproxy.google.com/~r/exarg/~3/y7olY0z8iiU/ http://www.xarg.org/2011/01/is-it-possible-to-avoid-query-parsing-inside-of-mysql/#comments Mon, 10 Jan 11 21:54:25 +0100 Robert Eisele http://www.xarg.org/2011/01/is-it-possible-to-avoid-query-parsing-inside-of-mysql/ 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 »

]]> http://www.xarg.org/2011/01/is-it-possible-to-avoid-query-parsing-inside-of-mysql/