Category: PHP / MySQL

You are already aware that from January 2008, onwards PHP version 4 will not be available for support and instead, PHP 5 will be in place. There is also a community set up in the web, supporting and encouraging all, to go for PHP5. Now the question is if this was indeed a step required for all? The primary question is Why not PHP4? And Why PHP5? First of all, what the problem is with PHP4 so that everybody is supporting PHP5? The answer is, there is really no such problem with PHP4. Since its birth, PHP4 was a rocking success and it was only climbing up, up and up, eliminating all its contenders virtually out of competition. But will you stick to an old programming language when a new better version is available? I know the immediate question is why then we didn’t shift earlier. This is a valid question and there are again two answers to support the cause. When PHP5 came out of development shell to the market, it had a number of bugs. But gradually all bugs got eliminated and PHP5.2 is free of all reported bugs. Once your application is ready you need to find a host where you can upload it to see it running. But reluctant hosting service providers were not shifting to PHP5, forcing developers to make the application suitable for PHP4. With all downward compatibilities, full potential of PHP5 cannot be explored in PHP4 as otherwise both the version will seem similar. Now, at this moment everybody realized that without a movement this change will not happen. This made the support of PHP4 to stop so that all hosting services can be forced for up-gradation. How you will be benefited? When you are developing your application by using a better technology, the obvious and immediate benefits are in terms of features, speed and functionalities. PHP5 has all the weapons needed to combat the advancement of .Net, Java as web scripting technologies. It has a better mysql database handling feature, a full working version of XML parsing techniques and full object oriented features. The full object oriented features gives you better opportunity to reuse your code as well as altering functionalities in much quicker way. The obvious and real benefit I see is catching up with better web development and going parallel with trends. Developers will also be benefited as they will be enabled with better functions, more flexibility in terms of binding logic and using old code to speed up development and delivery. Moreover almost all third party freewares like osCommerce, PEAR, PHPBB are getting converted to PHP5. This will leave with no option but to use PHP5 while integrating and customizing these applications. It is the time to go for PHP5, rather press for it. And you can only do it by asking your developer to build the application in PHP5 and insisting on PHP5 support when you select your hosting service provider.

As business evolves, the information that they store and process also changes and that means the underlying database structures that have been holding well for many years needs to be changed as well. This are even more complicated when you have redesign an “in use” system. Here is a step by step guide on what needs to be done: Step 1: Draw the structure of the new database on Paper Based on the new business requirements, you need to draw the structure of the new database on paper. We often use MS Access at this stage because it has a got a “Documenter” tool which generates a real professional output in a relatively short time. Step 2: Analyze Existing Scripts / Code Now you need to analyze the existing code to see how they are using the current database structure and how this usage will change based on the revised structure. For instance, if you are trying to split a long table into two separate tables, then all previous “Select *” queries on that table will be useful. Thus, all the existing code will have to be rewritten. Step 3: Create a migration plan You need to create a migration plan. Leaving the tech stuff aside, this basically means how you are going to transfer the data from the old data into the new database. For this you will have a write a script of some kind. This stage is very critical because you might have to adjust your new database. For instance: If in the old database you have customers table which had a name column. Now, if the revised database has firstname, middlename and lastname column then I can bet your database redesign dreams will end here as you can’t migrate the data between the two versions unless your plan includes manual typing. Step 4: Create the new database Now you get to design the new database structure as it should be implemented unless you have done in the very first step itself. Step 5: Test the Migration Plan You now need to test the migration plan on a sub-set of “old” database to ensure that the data can be migrated as you planned. Step 6: Adjust the code Now its time to edit the code! You need to go line by line and change all queries which are affected by the revised design. Step 7: Perform the Migration The day you have been waiting for has finally arrived; you get to perform the migration which means you upload the new code and execute the migration script. Last Step: Test All said and done, done forget to test the whole system once again to ensure that things are working as expected.  Of course, you should never play with the live database. With all those GUI tools available in the market, it’s real tempting and easy to make changes on the live system. Always work on a backup copy and perform the migration on actual system once the dummy system has been tested properly. About our MySQL related services We offer professional mysql database design, implementation and performance tuning services through http://www.script2please.com

MySQL is a rock solid, lighting fast database server which has been designed for two factors speed and performance. It is the Ferrari of databases: Light weight, fast and Built for the high speed tracks! I still hear an awful lot of stories from owners whose databases are running two slow. In my experience, the three main places to look for problems are: 1) Faulty Database Design 2) Bad Queries 3) Server related factors Faulty Database Design Proper database design is the single most important factor for the ensuring performance and maintainability of the database. Here is what you need to answer when designing a table: “Can I reduce the size of data that each row will have?” Here is what you can do: Use unsigned numeric values when the application will not store negative numbers. Like the “quantity ordered” of an item in an ecommerce application is never going to be -$125. Use Variable length values instead of fixed length value i.e. use varchar instead of char Do not use unnecessarily large field sizes. For most ecommerce application “unsigned smallint” is more than enough to store inventory count. A field described as “unsigned smallint” can store a max value of 65535. Don’t ignore normalization; its helps prevent unnecessary repetition of data. The part B of this is, don’t overuse normalization. If the table will not grow in size significantly, there is no point in normalization. For example, if the user table has just 20 rows (i.e. 20 employees in a company), all attempts of normalization are wasted. Use Index Keys. Don’t decide keys by “The customer id has to be indexed in the order table”. If the order table is being searched 90% of the times by “order date”, it makes more sense to index “order date”. Remember, how a table will be used should determine how it is designed. Spending time here will save years of frustration. Bad Queries It sounds too good to be true but you wont believe the number of developers out there who completely suck at writing queries. There are two types of bad queries: a) Unnecessary Queries: These are the queries that shouldn’t have been made in the first place. The only way to avoid this is asking, “Do I really need this data?” b) Inefficient Queries: These are the queries that do not use the underlying table structure or MySQL functions in the correct way. Here is a starting point to start looking at problem areas: Unnecessary usage of “Select * from“statements when the entire processing is being done on a single column. The more data is fetched from the server the more work MySQL has to do and more bandwidth it takes. Using sub-query instead of a join. On a properly designed database, joins are incredibly fast. Using sub-queries just shows a lack of knowledge. Improper use of Keys. This is especially valid for range checks. Remember to use the “Explain” statement to check the usage of keys and then use the “use key” statement in your “where” clauses to force key usage. Server Factors Everything done correctly, there still may be some server factors that may be causing the system to be slow. These are: Hardware related and Server configuration related. Here is what you can do about the hardware: The more RAM is on the system the better it is. MySQL frequently fetches data from the RAM and more the RAM is on the system, the better it is. Buy the fastest possible RAM! A slower RAM is just irony. Once you are settled with the RAM size and speed, look for processing speed. MySQL can use multiple processors. Once you are satisfied with the hardware, there are a set of variables in “my.cnf” that you must look at: key_buffer_size: This describes the memory available to store the index keys. The default is 8 MB but you can set it to 25% of the RAM query_cache_size: This value is by default 0. if you have a lot of repeating queries like in reporting applications etc, make sure you set this value high. table_open_cache: This determines the number of table descriptors that MySQL will keep in the cache. The default value is 64. But, if you have 100 users accessing a table concurrently then this value should atleast be 100. You also have to take into considerations joins etc. Thus, this value should also be kept high. I hope this article will take one step further in unlocking the mystery of slow servers and help solve some of the problems. About our MySQL related services We offer professional mysql database design, implementation and performance tuning services through http://www.script2please.com

MySQL has become the ideal choice of database for projects within new companies or new projects within existing companies. Most of these companies are choosing LAMP architecture for multitude of advantages that it offers. The main criteria for choosing a database comes down to four factor namely: Reliability, Performance, Scalablity and Ease of deployment and maintenance. A lot of times I hear people asking this question : “Based on my requirements , do you think MySQL is good enough for me?“. Thus, I thought maybe it will be a good idea to find out some instances where MySQL is being used and then let the clients do the comparison themselves. Here are a few places where MySQL is being used: YouTube Craigslist Flickr Habbo Hotel LiveJournal Technorati Second Life Trulia FeedBurner Right Now Google (for AdSense and AdWords) Wikipedia Travelocity Weather.com Some other places where MySQL was used: Earth Unit of NASA’s Mars Rover used MySQL Special effects of The Lord of the Rings HotorNot Some mission critical deployments: Google and Yahoo run mission critical applications on MySQL Nokia’s and Alcatel’s mobile phone networks Emergency systems during the tsunami in South East Asia During Hurricane Katrina So, let me ask that again – “Is MySQL good enough for you?”