|
|
Chef's Blog -
Tiny Stats
|
|
Written by Datagod
|
|
Friday, 13 June 2008 |
This is a realtime display of the current hits per hour that this website is getting. The chart updates every 5 seconds. The chart is rendered via javascript/flash and gets its information from a MySQL query. The data is translated into XML of course, prior to being fed to the flash file. This is all wrapped in PHP. Talk about complicated! 
|
|
Last Updated ( Friday, 13 June 2008 )
|
|
Chef's Blog -
Tiny Stats
|
|
Written by Datagod
|
|
Thursday, 12 June 2008 |
 For the past 6 months I have been fairly quiet on this site. The reason for this is that I have been focusing my attention on learning FusionCharts and FusionWidgets charting tools for my masterpiece MyTinyStats.com.
I love this product, they really did a great job on putting it all together. Considering how many charts and gadgets they provide, the documentation is incredible. My favorite part, however, is in writing PHP code to translate MySQL query results into XML.
I have spent countless hours on this project and am very pleased to say that the hard work is paying off. Not only is MyTinyStats starting to look really great, but I am also able to start selling my services to other clients. The money I make all goes back into paying for the mucho hardware/software being used to hold my little web empire together.
So, if you are looking for some help with PHP, MySQL and FusionCharts, I am the man to talk to. 
|
|
Last Updated ( Thursday, 12 June 2008 )
|
|
Chef's Blog -
Tiny Stats
|
|
Written by Datagod
|
|
Thursday, 15 May 2008 |
 My sites routinely get hammered by bots and crawlers. Some are malicious, attempting to find vulnerabilities in unpatched software. Some are link spammers that send fake referrer information containing URL's back to some crappy site selling cheap drugs.
Other crawlers (Google, Yahoo, etc.) are totally legitimate, indexing the pages as they crawl them.
In any case, I want to slow down the page loads for the bots and crawlers to prevent them from overwhelming the webserver. In particular, MyTinyStats has thousands of webpages that are generated via combination of PHP and complex MySQL queries.
I do this with the following code:
$BotList = file_get_contents("lookup/BotList.txt");
IF (strstr($BotList,$_SERVER["HTTP_USER_AGENT"]))
{
sleep(30);
}
The BotList.txt file contains a list of 350+ known bots and crawlers that I have identified over the past year. This file gets loaded into a local variable using the "file_get_contents()" function.
I then examine the BotList, searching for the current UserAgent. If found, I slow the page down by sleeping for 30 seconds. If not found, the rest of the page loads.
The file is approximately 23Kb and takes only a few milliseconds to load and parse.
While using this approach, I made an interesting discovery: file_get_contents() accepts a URL as a file parameter.
$BotList = file_get_contents("http://mytinystats.com/lookup/BotList.txt");
IF (strstr($BotList,$_SERVER["HTTP_USER_AGENT"]))
{
sleep(30);
}
Specifying a URL however indroduces a 3 second overhead!! 
I highly recommend using a relative file name and not a URL, if at all possible. |
|
Last Updated ( Thursday, 15 May 2008 )
|
|
SQL Goodies -
Performance Tuning
|
|
Written by Datagod
|
|
Wednesday, 05 March 2008 |
 If you have deleted a large part of your table or updated many variable length rows, you should consider running the optimize command.
If you want to quickly generate the optimize commands for every table in you database, executing the following MySQL statement:
select concat("optimize table " , TABLE_NAME , ';')
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA <> 'information_schema' order by 1; |
|
SQL Goodies -
Administration
|
|
Written by Datagod
|
|
Wednesday, 03 October 2007 |
Have you ever wanted to retrieve a list of values from a table, storing the results in a string?
Here is simple yet effective method without having to rely on cursors, while loops, or other complex structures.
declare @string varchar(8000)
select @string = ''
select @string = @string + MyColumn
from MyTable
print @string
It is important to initialize your string to a non-NULL value, otherwise your string will end up as NULL, which is the default behavior of NULLs and string contcatenation.
Give it a try, it works like a charm.  |
|
The News -
Latest News
|
|
Written by Datagod
|
|
Saturday, 12 June 2004 |
 Cooking with SQL Launched Greetings! Have you ever had a craving for some SQL Pizza? What about a nice big bowl of Curried Clustered Indexes? Cooking with SQL has been in the planning stages for some time now. My goal is for this site to be a showcase for all the nifty procedures, scripts, functions, etc. that I have written over the years as part of my job as a DBA. I have a passion for cooking, and a passion for SQL, especially cooking with SQL. I love rising to the challenge of "It can't be done" or "you can't do that". I get enormous satisfaction out of using SQL to solve not just business problems but technical puzzles as well. I specialize in Microsoft SQL Server, but I am branching out to MySQL as well. I am currently engaged in a Data Warehouse project for a large Government client where I am using Microsoft SQL Server 2005, Analysis Services, Business Intelligence Development Studio (which includes SQL Server Integration Services). The concepts are old hat, but some of the tools are new. At the same time, I am quickly learning the ins-and-outs of Google Custom Search Engines and Google's Search AJAX API. Check out the SQL Hunter for a live demonstration of a work in progress. I actually find it quite useful for performing SQL related searches. Over the coming months, I will be writing many articles dealing with SQL Server 2000 and 2005 performance tuning, troubleshooting, and administrating. I will also be publishing an article titled "Log Shipping for the Masses" or something like that. Why upgrade to SQL 2000 Enterprise Edition when all you really want to do is take your production database and have a warm standby version somewhere else for disaster recovery / reporting purposes? Well, with my help, you won't have to. Stay tuned, I am cooking up some SQL goodies as you read this... |
|
Last Updated ( Thursday, 05 April 2007 )
|
|
|
