On MySQL query optimization used for improving the quality of e_Learning platforms

1. 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

2. 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

3. 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 e-Learning and one conventional site (website), even if the same processes cycle is used.

4. 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.

5. 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).

6. MySQL - thedatabase • 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).

7. 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.

8. 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...

9. 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:

10. The data table structure 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.

11. Measuring the time The execution time (unix time) can be found in the $totaltimevariable using themicrotime() 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);

12. 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> <? } ?>

13. 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> “; } ?>

14. Results obtained 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 .

15. 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> "; } }

16. 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.

17. 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;

18. 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

19. 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.e-learningguru.com/gloss.htm, accesat la 11.01.2007