May 192008
 

Sometimes when we want to display

information from database, we call the data with query. In most database, in this case

mySQL, we can make some variation of query depend on what your favor. And some web page need

to use more than one query to display related information, e.g:

<?

$sql_query = mysql_query(“SELECT * FROM table_name”);

while(result =

mysql_fetch_array($sql_query))

{

$sql_query2 = mysql_query(“SELECT * FROM

table_name2 WHERE id_table2 =’$result[id_table1]‘ “);

$result2 = mysql_fetch_array

($sql_query2);

echo $result2[example_field_data];

echo “<br

/>”;

}

?>

Above SQL query is work properly but when you tested on

phpmyadmin, you can see how long it takes the query to execute? Maybe can take more than 1

second. You know why this is can happen?

As from the first query, it will doing

looping depend on how many data will be shows, how about 100 records or more than 10.000

record? It’s will looping execute the second query, its mean that from one page will

execute more than two queries, but xx more query. And it will penalized your database

performance, and sometimes can make overloaded

But you can prevent this by joining

those query into one query using JOIN or LEFT JOIN command.

<?

$sql_query =

mysql_query(“SELECT a.*, b.* FROM table_name1 a JOIN table_name2 b ON a.id_table1 =

b.id_table2 “);

while(result = mysql_fetch_array($sql_query))

{

echo

$result[example_field_data];

}

?>

You can test this query on

phpmyadmin and it will can perform faster than first query method because this query using

JOIN command to call two tables and using alias for shorts command (‘a’ is alias for

table_name1 and ‘b’ is alias for table_name2)

Otherwise, you can optimze this query

again by selecting the related field only to display on browser, not select all field

(SELECT *), here the example:

<?

$sql_query = mysql_query(“SELECT a.name,

a.email, b.job, b.job_level FROM table_name1 a JOIN table_name2 b ON a.id_table1 =

b.id_table2 “);

while(result = mysql_fetch_array($sql_query))

{

echo

$result[name].”<br />”;

echo $result[email];

}

?>

By

selected only related field, you can reduce memory utilization by this query and also

improved your SQL query performance.

Have a nice day…

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

To use reCAPTCHA you must get an API key from https://www.google.com/recaptcha/admin/create