I've been asked to make some reports recently for some people in my company.Almost all of them are quite heavy and hit the database pretty hard.The thing is that these reports are usually generated once or twice a month, but they process a year's worth of data. Every time they are generated, they really upsets my poor production MySQL database.
I was thinking if there maybe could be a better way to just save the processed data in a table and update it every now and then.
So.. this is what I did:

1. I created a table that keeps the aggregated results from the report.
   
2. I added to the start of the SQL statement "INSERT INTO reports.thistable SELECT (the worlds most intensive SQL query) ;"
   
3. At this point I realised that I need a bit extra help determining which parts of the data should be updated and …

I have been spending a few days looking into optimisation for a few of our products, the biggest of which is Intellichat, which this great MySQL function is soon to be used for. Intellichat handles thousands of chats every hour, we average at about 190,000 chats a day, which is increasing daily. Every one of these chats holds information about the chat, from the Agent name, through the the IP address that launched the chat. This data allows us to provide detailed statistics to our customers from which Agent name converts best to which Country generates the most sales for their product.

*** Please note that all data sizes below are taken from the PHPMyAdmin row size and not the individual data size as listed in the mysql manual ***

Since starting out, I had always used a char(15) which uses 16 bytes to store ’192.168.0.1′. Since …

Hi all,

Are you aware of the fact that you can use NetBeans IDE for programming with Python? It's still early in the game, but it's true. We've already set up a quick tutorial for Python at http://wiki.netbeans.org/NetBeansPythonTutorial.

As mentioned, it's still early, but efforts are already underway to provide tutorials that feature the following:

• Python and Django
• Database Interaction (SQLAlchemy, mysqldb)
• GUI programming (wxPython, pyGTK)
• PyQT4

I'll let you know more as the story develops.

Cheers!

--James

As you might have seen MySQL QA Team has published their benchmarks for MySQL 5.0.72 and 5.1.30.

It is interesting to compare with results I posted previously

The quote from the results mentioned above:

"Maybe you’ve seen some claims by others in the MySQL community that MySQL 5.1 runs slower than MySQL 5.0. Maybe you’ve also seen some claims by others in the MySQL community that MySQL 5.1 runs faster than MySQL 5.0.

Guess what? They’re both right. "

But is it really what results are telling us ?

I do not think so. When you're doing benchmarks you should be comparing best performance settings for given application and conditions. For example it is unfair to …

I have had the privilege to go to the O'Reilly Open Source Convention 4 of the past 5 years.  During that time, it has been held in beautiful Portland, Oregon.  Well, the OSCON 2009 web site is up and they are moving it to San Jose, CA.  I have never been to the San Jose Convention Center.  The pictures look nice.  I have only been to San Jose at all to get off a plane and go to the Santa Clara Convention Center.  I hope San Jose has more to offer than Santa …

Why a new tool?

The MySQL test framework needs a tool that will increase the production of test cases. The proposed tool will be a command line client that records queries and responses to then create the .test and .result files. There were a couple of proposed options that would also achieve a similar objective. The first was using the MySQL command line client and logging the client-server interaction, then creating the appropriate test files. Using this method, writing test cases for concurrent connections would have to be serialized and some concurrent test cases cannot be written in this fashion. Another option was patching the MySQL command line client to record queries and responses like my proposed tool. This would work well with concurrent connections, but a tool would have to be built to convert the log file to .test and .result files regardless. So instead of implementing these alternatives that would take more time …

After weeks of revising UML diagrams I think it is actually time to write some code and prove myself to be at least somewhat productive. Here is the final UML diagram for the tool that I will be creating.

As I go forth and begin coding I have decided to use the extreme programming practice of prototyping and testing. This will allow me to modify the design if need be during the project depending on what incompatibilities are found, but I will hopefully find them faster than if I implemented this in a modular fashion.

As always, any comments are more than welcome.

A few days ago the mysql proxy tool was released (link) and it does exactly what the name would suggest. But it does boast a few features that have changed the development of the test creation tool.

• It has a Lua interpreter that allows user written scripts or modules to be run by the proxy. These scripts can intercept queries and responses between the client and the MySQL server, and also manipulate them in whatever way necessary. There are examples on the wiki of how to use the scripts.
• Instead of writing an entirely new command line client, we can use the standard mysql client and focus on the scripts that will intercept the client-server communication and translate that into test cases.

For these reasons the test creation tool will not be written in C/C++ but instead as Lua modules that the proxy will run. This will hopefully …

Via Ditesh:

Interesting aside: the bus stopped in a ${RANDOM} town in Johor for food, and at the cashier, I spotted the cashier using KC POS which had a prominent “Powered by MySQL” text and the Sakila logo. Very cool!

This was a small town coffee shop, using a cash register, powered by the mighty Sakila. Similar to the chain of restaurants, Old Town White Coffee.

The whole blog post from Ditesh itself is interesting, but knowing you can find MySQL just about anywhere, showing the ubiquity of the database, just makes you proud to know, you work at/on/with MySQL.