Transcript while($row=mysql_fetch_array($result))

On MySQL query optimization
used for improving the quality
of
e_Learning platforms
Vasile MOLDOVAN,
UNIVERSITY OF ORADEA
FACULTY OF ENERGY ENGINEERING
Florin POPENTIU-VLADICESCU,
UNIVERSITY OF ORADEA
UNESCO CHAIR IN INFORMATION TECHNOLOGIES
Abstract


The paper presents some aspects concerning the
quality improvement of the script design , using
MySQL for the development of the e-Learning
platforms.
Also some methods and techniques are proposed
for the optimization of the execution time for
„query” processing using these data bases. The
performances of these methods and techniques
are illustrated by experimental results
Introduction



The basic difference between conventional learning and
distance learning, is in the target group, who is often remote,
even crossing borders into other countries. Distance learning is
defined as a formal education process where the majority of the
instruction occurs when the instructor and student are not in
the same location. The process and the target groups are often
separated by time
Even, if for e-Learning we can consider a basic definition
something like this: “A process that facilitates education using a
network (Internet, LAN or WAN)”, at the present time there are
more different terms used for, like On-line Learning, Distance
Learning or Web Based Learning or Web-Based Training (WBT)
In this paper we try to point out an analogy between the eLearning and one conventional site (website), even if the same
processes cycle is used.
Introduction



The users (students) request information from a web-server
using a web browser such as Microsoft Internet Explorer or
Opera. The HTTP server after receiving the request and after
processing some specific tasks, will return the requested
information for the user.
At the server side we must have a HTTP server, the most widely
available HTTP server is Apache.
Apache is an “open source” web server that runs on most
commonly used platforms. It has a modular design that provides
a variety of services such as server-side scripting.
The server side language



One of the most used server-side scripting language is PHP
being a good alternative for ASP(Active Server Pages - which is
a web server extension of Microsoft);
PHP - Hypertext Preprocessor is an “open source” server side
programming language extensively used for web scripts and to
process data passed via the Common Gateway Interface from
HTML forms etc. PHP can be written as scripts that reside on
the server and may produce HTML output that downloads in the
web browser
PHP was created in 1994 by Rasmus Lerdorf to track the visitors
to his online resume(blog). As its capabilities grow (and as more
people started to use it), it came to stand for “PHP: Hypertext
Preprocessor,” which basically means that PHP handles data
before it becomes HTML (HyperText Markup Language).
MySQL - the database



There are many advantages of PHP, but the most important it is
the capability to allow native access to the most important
databases like MySQL, PostgreSQL, SQL and Oracle. With PHP
the developers can quickly create high-performance,
dynamically generated sites.
MySQL is probably the world's most popular “open source”
database in the present and its newest releases are in the
version 5.
The main reason for popularity can be explained by the number
of the open source community users, and the GPL (General
Public License) license facility. Although this kind of license
can be restrictive, we need to release the complete source code
for the application which is built on MySQL. We do not need to
release the source code for components that are generally
installed on the operating system on which our application runs,
such as system header files or libraries( it is recommended the
commercial license to all commercial and governmental
organizations).
MySQL - the database


MySQL supports all known platforms, including Windows-based
platforms, AIX-based systems, HP-UX systems, Linux Intel, Sun
Solaris and so on. Yahoo and Google are two big top companies,
and they also use MySQL on its many application. PHP is
probably the most common language used with a MySQL
database, but almost all popular high level programming
languages can work with MySQL using some special functions
for this.
Of course, for administration of the databases on MySQL we can
use the command line but we have many alternatives for this,
like PhpMyAdmin (written 100 % in Php), Navicat, Aqua Data
Studio and others. Many of these tools have a lot of capabilities
like importing – exporting in different formats and also execute
or build the specific MySQL queries.
MySQL Query



We can consider that the Web Based Learning requires a
webpage, which usually is dynamically built. This means that
there is a database with many records, and the server side files
will run some specifically lines “query”, interacting with the
database, and the returned data will be sent to the user browser.
In this paper we try to analyze and improve the running time of
these “queries”. By reducing the execution time we can return
faster the data package requested by the users, serving in
better circumstances, what is equal with a quality improvement.
Let’s see how this can be made...
Increasing the quality ?
There are many way to increase the quality, we have analyzed the
server side possibilities:


One of the method for improving the quality is decreasing the
processing time at the server side
This can be done in two ways:
•
•
Optimizing the code lines
Optimizing the query syntax
For running the tests we used Apache + Php + MySQL triade and
2000 records in the database we are used for running the tests,
with the following structure:
The data table structure
Columns
Type
ID
INT
NAME
VARCHAR
SURENAME
VARCHAR
EMAIL
VARCHAR
PHONE
VARCHAR
YEAR
VARCHAR
SPECIALIZATION VARCHAR
USERNAME
VARCHAR
PASSWORD
VARCHAR
Lenght
11
255
255
255
255
255
255
255
255
Special
Unique
We have generated randomly and fill the database before the tests with 2000 records like
the structures from table, using the INSERT MySQL query with a FOR cycle in 2000 steps.
The ID columns is the auto-increment value and also the unique identifier. In this way we can
be sure that we don’t have any duplicate records in the test database.
Measuring the time
The execution time (unix time) can be found in the $totaltime
variable using the microtime() function with this line codes:
At the beginning:
$mtime = microtime();
$mtime = explode(" ",$mtime);
$mtime = $mtime[1] + $mtime[0];
$starttime = $mtime;
At the end:
$mtime = microtime();
$mtime = explode(" ",$mtime);
$mtime = $mtime[1] + $mtime[0];
$endtime = $mtime;
$totaltime = ($endtime - $starttime);
Code lines
Case1 - WHILE cicle:
...
while($row=mysql_fetch_array($result))
{
?>
<tr>
<td><? echo $row['id']; ?></td>
<td><? echo $row['numele']; ?></td>
<td><? echo $row['prenumele']; ?></td>
<td><? echo $row['telefon']; ?></td>
</tr>
<? } ?>
Code lines
Case2 - WHILE cicle:
...
while($row=mysql_fetch_array($result))
{
echo “
<tr>
<td> $row['id']
</td>
<td> $row['numele']
</td>
<td> $row['prenumele'] </td>
<td> $row['telefon']
</td>
</tr>
“;
} ?>
Results obtained
Nr. Records
1
200
2
400
3
600
4
800
5
1000
6
1200
7
1400
8
1600
9
1800
10
2000
Time-case1 [sec]
0,132285881042
0,250323057175
0,294636020660
1,383291959760
2,022243022920
2,429026126860
2,884603977200
5,217308998110
5,420905876160
6,206202030180
Time-case2 [sec]
0,215481119156
0,233474969864
0,261955022812
1,307677030560
2,081826925280
2,186830997470
2,667994976040
2,755152940750
3,827739953990
5,991676092150
Running the scripts and measuring the times, with the optimized and unoptimized WHILE
instruction, we have obtained different execution times. Also we have runs the scripts for
different record sets (200, 400, 600 … 2000 records) to see in witch way in the execution
times are increased or decreased. The results are presents in the above table .
Query sintax
Case1 – “query” will take all the
records:
$query="SELECT * FROM utilizatori";
while($row=mysql_fetch_array($result))
{
if ($row['anul'] == 2)
{
echo "
<tr>
<td>$row[id]</td>
<td>$row[numele]</td>
<td>$row[prenumele]</td>
<td>$row[telefon]</td>
</tr> ";
}
}
Query sintax
Case2 – “where” is used in the
“query”:
$query="SELECT * FROM utilizatori WHERE anul=2 ";
while($row=mysql_fetch_array($result))
{
echo "
<tr>
<td>$row[id]</td>
<td>$row[numele]</td>
<td>$row[prenumele]</td>
<td>$row[telefon]</td>
</tr> ";
}
The execution time was reduced by 0,569401884080 sec.
Some conclusions







Use WHERE if it’s possible in the “queries”;
Use the “index” for records in the database;
Use the LIMIT 1, to force the query to stop after the record is
founded where it is possible;
Do not interrogate the database for the same data more time;
Use the minimum required type of data for a field;
By reducing the open-close cycle for php parsing <? ?>, the
execution time can be improved;
echo function is faster than print;
References






M. Vlada, E-Learning si Software educational, Conferinta Nationala de
Învatamânt Virtual, 2003, pag. 1–2 http://fmi.unibuc.ro/cniv/ , accesat la
10.01.2007
Matthew Norman, Database Design Manual, PHP si MySQL, Springer 2004, pag.
199-203
Betsey Bruce, Building Online Learning Applications, Peachpit Press 2002, pag.
420-446
Simon Stobart, Mike Vassileich, PHP and MySQL Manual, Springer 2004, pag.
29-35
Farida Hasanali, Paige Leavitt, A Guide for Your Journey to Knowledge
Management, APQC 2003, pag. 63-90
Larry Edward, Php and MySQL for dinamic webpages, Peachpit Press 2003,
pag. 107-112






Hugh E. Williams, David Lane, Web Database Applications with PHP & MySQL,
O'Reilly 2002, pag. 110-113
Ben Forta, MySQL Crash Course , Computers 2005, pag. 121-131
Andrew M. St. Lauren, Understanding Open Source and Free Software
Licensing, O'Reilly 2004, pag. 1-11
Larry Edward Ullman, Php and MySQL for dinamic webpages, Peachpit Press
2003, pag. 185-189
Julie C. Meloni, PHP Essentials, Second edition, Tomson Course Technology
2000, pag. 109-135
E-Learning glossary - World's biggest e-Learning glossary - http://www.elearningguru.com/gloss.htm, accesat la 11.01.2007