Category Archives: mysql
Hi,
today i wanted to give some common and not so common ideas of how to improve the performance of your web app. as you know, when you have a busy website, the performance (and along with the stability) is a key factor to have your project up and running, in this cases, you need to try to improve every aspect of your app, that is the idea, to help you to identify what can be or not be done in your case.
Database Optimization
Build a efficient database schema is not something easy, it cost time, study and testing to do it. at the time to build a efficient DB schema you need to take in consideration the following:
*Use the minimum columns as possible, and choose wisely the type of data that the database will store (if you will store number of 3 digit max, set a int(3) and not a varchar, text or a int(20))
*use the relational model, for dynamic or multiple values, use a index pattern where you have the definition of the data in 1 table and the data itself inserted by the user in other. do not create tons of columns as value_1, value_2 etc..
*add indexes to your tables
*use the primary key and unique key for columns that should have unique values to avoid data corruption or misbehavior.
in cases of the query, try to group the query’s in the less quantity of calls, use JOIN’s, select only the columns that you will really going to use, don’t make generals select’s. in case you need to return the number of something, use functions as SUM() and not mysql_num_rows().
Clean and re-usable code
you need to be careful at coding too, even that php bring a big liberty whiteout having to worry about types and variable types, you need to be responsible for what you write. the php code get messy when you do messy stuff. organize variables, functions and classes as much as you can. i’ll recommend to use a MVC model if is possible (even that before i rejected it) and a framework will also make you life easier when you learn how to use it.
there is always a good practice separate the logic from the view (i mean the main php code from the design), for this you can grab any framework, most of them already support this or get a template engine. i have always used smarty, and is very flexible and you can extend it with custom functions and modifiers, also it support time cache and others types too. even that i’m happy with it, and i’ll recommend to you, i have realized with the time that is a waste if time. it will never be faster than the normal php code, so if you want to go really well, i’ll recommend you to get a or build a class that will just load the php script as templates and that’s it. you will save time from learning a template script and save process in the meantime
cache everything
this is one of the most important thing of all. the cache is the base of any optimization. let say true, most of the pages of any app doesn’t change and if they change dynamically, is usually for a user action. if this happen you can use a selective caching, where you cache at the event if a action. make this type of cache is not a easy task, takes time and testing, but really it make great results on the practice and worth the time invested on it. the cache of css and js files is also a good technique, as well the compress of them.
use the power of javascript
i learning caching a lot of stuff to use the power of javascript. i have cached pages that needed to be refreshed each 20 seconds. surely you are asking how i made it, so basically i just cached them once and filtered the results with javascript after the ajax load of the filter using JSON and the html code from other call. if you are wondering if this is really a performance advantage, well yes, you will see a 50% of difference from a normal php processing. anyway, be careful with this type of technique, remember that javascript run on the client side, so the information passed there can be manipulated in evil ways. is a powerful tool that need to be used very careful. also, i will recommend you to use a javascript framework, they are cross browser compatible for almost all their functions, with a big community and plugins available, and heavily optimized.
i hope this help you in some way at the moment to optimize your web app’s 
Regards,
Shadow.
![[Digg]](http://cdn1.diggstatic.com/img/favicon.a015f25c.ico){kind=small}
![[del.icio.us]](http://images.del.icio.us/static/img/delicious.small.gif){kind=small}
Related Post
pChart Logo
Hi,
today i wanted to talk about a class that i have been using a lot lately and i have worked with it for the past months, called pChart. this class let you create charts from stream data. the class let you create line, curve, cake/basic pie, bar,stacked,radar, 3d pie,radar, and scatter charts, and in this list i’m sure i’m forgetting some of them. the process to create the chart is very simple
pChart Creation Process
as you see in the graph, the process consist in get the data from some stream, something that there is not mentioning is that the data retrieved, if is not a csv that the class have inbuilt functions to parse it, you need to prepare it, ordering in a specific way depending the type of chart you are using (if is a line bar, you need to define the serie name’s for each axis and the values for it). after you have the data, set the style, as the background, the font type and size (it support ttf fonts) and the scale. and then you are ready to draw the chart, that the returned data can be printed to the user directly, of course first setting a image type stream with the header() function or write it in a file.
below, you can see some example code:
// Standard inclusions
include(“pChart/pData.class”);
include(“pChart/pChart.class”);
// Dataset definition
$DataSet = new pData; // initialize the data parser object
$DataSet->ImportFromCSV(“Sample/bulkdata.csv”,”,”,array(1,2,3),FALSE,0); //get the data input from a csv file
$DataSet->AddAllSeries();
$DataSet->SetAbsciseLabelSerie();
$DataSet->SetSerieName(“January”,”Serie1″);
$DataSet->SetSerieName(“February”,”Serie2″);
$DataSet->SetSerieName(“March”,”Serie3″);
$DataSet->SetYAxisName(“Average age”);
$DataSet->SetYAxisUnit(“µs”);
// Initialise the graph
$Test = new pChart(700,230); //setting the widht and height of the chart
$Test->setFontProperties(“Fonts/tahoma.ttf”,8); //the font used for the chart and the font size
$Test->setGraphArea(70,30,680,200); //the width, height, x and y position of the area where the graph will be drawn
$Test->drawFilledRoundedRectangle(7,7,693,223,5,240,240,240);
$Test->drawRoundedRectangle(5,5,695,225,5,230,230,230);
$Test->drawGraphArea(255,255,255,TRUE);
$Test->drawScale($DataSet->GetData(),$DataSet->GetDataDescription(),SCALE_NORMAL,150,150,150,TRUE,0,2);
$Test->drawGrid(4,TRUE,230,230,230,50);
// Draw the 0 line
$Test->setFontProperties(“Fonts/tahoma.ttf”,6);
$Test->drawTreshold(0,143,55,72,TRUE,TRUE);
// Draw the line graph
$Test->drawLineGraph($DataSet->GetData(),$DataSet->GetDataDescription());
$Test->drawPlotGraph($DataSet->GetData(),$DataSet->GetDataDescription(),3,2,255,255,255);
// Finish the graph
$Test->setFontProperties(“Fonts/tahoma.ttf”,8);
$Test->drawLegend(75,35,$DataSet->GetDataDescription(),255,255,255);
$Test->setFontProperties(“Fonts/tahoma.ttf”,10);
$Test->drawTitle(60,22,”example 1″,50,50,50,585);
$Test->Render(“example1.png”);
and this is the result
pChart Example
you can download the class from here, you can find documentation and the classes definition at here, and here several examples of how to create different types of graphs
i hope this help you in some way .
Regards,
Shadow.
Related Post
Hi,
2 days ago my boss was talking with a developer, and he recommended to change our phpmyadmin and use a one file DB manager called DBKiss
so, i installed it (what only take to extract from the compressed file) and tried it. the interface of course is not “nice” as the phpmyadmin but is functional, you have almost all the same functions except the one of privileges as phpmyadmin, but you can execute sql queries, so if you know what you are doing, can be done manually.
the advantages of use DBKiss from phpmyadmin is the speed, is a lot more faster, even can make dumps of big SQL databases, something that phpmyadmin sometimes fails. also i think is more secure against attacks, like is less used and depends only from 1 file (anyway, if you have a DB manager that can be accessed from the web, always need to be in a protected directory)
the only thing i feel a little annoying is that force you to write the DB that you want to access to login. i usually login as root in the DB as i need to manage several of them, so i going to need to have the name of some at hand for the first login 
i’d recommend you give it a try you can download it from here: http://www.gosu.pl/dbkiss/
Regards,
Shadow.
