Visit Our UNIX and Linux User Community


Something went awfully wrong in PHP+MySQL :(


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Something went awfully wrong in PHP+MySQL :(
# 1  
Old 10-30-2007
Something went awfully wrong in PHP+MySQL :(

When I'm running a few statements through mysql console, they are working just fine but when I'm trying to write some logic in php and trying to execute it, its awfully slow.. Its perhaps performing, 10 queries in 20 seconds... Smilie Why is this happening?

My table structures are:

Name: asn_number
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| asn | mediumint(9) | | MUL | 0 | |
| ip_address | varchar(18) | | MUL | | |
+------------+--------------+------+-----+---------+-------+

And

Name: trace
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | double | | PRI | NULL | auto_increment |
| gid | int(11) | | | 0 | |
| sno | tinyint(4) | | | 0 | |
| asn | tinyint(4) | | MUL | 0 | |
| ip | varchar(18) | | MUL | | |
| r1 | decimal(10,3) | | | 0.000 | |
| r2 | decimal(10,3) | | | 0.000 | |
| r3 | decimal(10,3) | | | 0.000 | |
+-------+---------------+------+-----+---------+----------------+

Can someone tell me what could've gone wrong?
# 2  
Old 10-30-2007
The bottleneck can be at database level, web server level, at php level, or in your php script itself. Try to identify at which level is the slowness.

If all web pages at your web server are s-l-o-w, then you should check your web server configuration.

If other pages (such as html, perl-cgi etc) are fast, but only php pages are slow, then check what may be wrong with php.

If other php pages are fast and this particular page is slow, then check the php code.

I am not a database expert, so can't say much about it. Particularly, check if you can optimize the query that you are using in your php script.

If number of records in the tables are huge, and your query results in output of say 1000 records, then transferring that much data from web server to client (browser) may be slow.
# 3  
Old 10-30-2007
Thank you... Actually I tried running the same script locally (by the way, I am running all this on a dedicated Unix machine and not a web server as such) and the same problem repeated. I mean, its doing everything with the same degree of slowness...

What I'm trying to do is an adjacent row comparison and if they are different, I'm copying them onto a different table. The code goes something like this:

Code:
<?php

for($i=2;$i<2749750;$i++) {
	//Get the previous row
	$sql_prev = "SELECT * FROM trace WHERE ID=".($i-1);
        $result_prev = mysql_query($sql_prev);
	$row_prev = mysql_fetch_assoc($result_prev);
	//Get the current row
	$sql_cur = "SELECT * FROM trace WHERE ID=".$i;
	$result_cur = mysql_query($sql_cur);
	$row_cur = mysql_fetch_assoc($result_cur);
	
        //Now select the corresponding ASN numbers from the other table
	$sql_prev_asn = "SELECT * FROM asn_number WHERE ip_address='$row_prev[ip]'";
	$result_prev_asn = mysql_query($sql_prev_asn);
	$row_prev_asn = mysql_fetch_assoc($result_prev_asn);

	$sql_cur_asn = "SELECT * FROM asn_number WHERE ip_address='$row_cur[ip]'";
	$result_cur_asn = mysql_query($sql_cur_asn);
	$row_cur_asn = mysql_fetch_assoc($result_cur_asn);
		
	if(($row_prev_asn['asn'] != $row_cur_asn['asn'])) {
		$sql = "INSERT INTO br(asn1,ip1,asn2,ip2) VALUES('$row_prev_asn[asn]','$row_prev[ip]','$row_cur_asn[asn]','$row_cur[ip]')";
		$result = mysql_query($sql) or die(mysql_error());

	}
}

?>

As you can observe, there are many queries and this is the only way I thought would work... Do you think because I'm trying to say "SELECT * FROM trace WHERE ID=" so many times and that the trace databases is big (around 200 MB) this problem is occuring? But I've actually indexed ID and ip fields...
# 4  
Old 10-30-2007
you are running so many queries... that is the reason the page is slow

instead, you can run one query and get all the data in an array. then run the loop over that array to do the computations.

or write a stored procedure that will do the computations for you. this will save running all the thousand queries (i am not so good with stored procedures, so can't tell you exactly how it would be)
# 5  
Old 10-30-2007
couple of questions and suggestions to you Smilie

1)
Are you using all the fields from the table trace ? I could see only the field 'ip' being used.
If am right with the above, then what is the need for select ' * ' from table.
You don't need a ' * ' for that.

Code:
select * from table;

is expensive than
Code:
select specific_column from table;

so if you know the column that you are interested just use the column_name in the query.

2)
Query is run again and again where the data values are known in prior.

Consider this,
Code:
i=2; i<somenumber; i++

i=1 ( executed )
i=2 ( executed )

for the next iteration

Code:
i=2 ( executed )  //why this should be executed again, value is known in the previous iteration itself ???
i=3 ( executed )

effectively for ' n ' iterations you have ' n ' extra queries which I feel can be completely avoided. ( dropped as such )


3)
If only single column is extracted and if you are sure values are there definitely, there is no need for two queries. That could be rewritten something like

Code:
select ip from trace where trace_id >= $i and trace_id <= $i+1;

or even better if you have a running number as column in your table
Code:
select column_with_running_numbers_sequence, ip from trace;

use the above query to fetch all the values in one shot
and now you should have association like
record1 ip_value1
record2 ip_value2

the approach depends upon the table design


4)
What about the commit status of your program ? Is that auto commit ?
If so , that would definitely take more time because for each and every insert statement there is going to be commit statement which will definitely bring down the performance.

Only if the inserts can be clubbed and committed purely based on the criticality of the application you can commit after ' n ' inserts

Code:
insert
autocommit
insert
autocommit
insert
autocommit

is expensive than

Code:
insert
insert
insert
explicit commit


Please post how it goes ? All the best ! Smilie
# 6  
Old 10-30-2007
@Yogesh

Thank you so much for the advice... I'll look into what stored procedures are now... Maybe that could help me out...

@matrixmadhan

I really appreciate the time you've spent on framing that reply... Thanks a million... Probably by receiving help from people like you, even we, newbies can learn "something" Smilie Thanks again...

I'm currently making changes as you suggested in 1 and 2... I think 3 is linked to 2.

Quote:
If only single column is extracted and if you are sure values are there definitely, there is no need for two queries. That could be rewritten something like

Code:

select ip from trace where trace_id >= $i and trace_id <= $i+1;

or even better if you have a running number as column in your table
Code:

select column_with_running_numbers_sequence, ip from trace;

use the above query to fetch all the values in one shot
and now you should have association like
record1 ip_value1
record2 ip_value2

the approach depends upon the table design
Could you kindly elaborate this one? I seem to have missed some simple sql rules... Regarding the table design, I can change it even now because I'm just testing this on 2 million values... The actual data set contains 50 million values...

This 'id' column of trace is an auto_increment... So can I write something like:
Code:
SELECT id, ip FROM trace;

Its true that I will be having all the values at once, but how am I gonna compare adjacent values then?
Usually to fetch the rows I use something like:
Code:
while($rows = mysql_fetch_assoc($result) {
//Perform operations on rows. But how will I get the previous row or next row in this loop?
}

Thats my problem...

And if I'm not asking too much, I'd like to know about your 4th point too... I've never worked with autocommit... Maybe I'm unknowingly doing that... Is my code potraying that picture by any chance? How can I disable that? Really sorry for the trouble...
# 7  
Old 10-31-2007
Quote:
I'm currently making changes as you suggested in 1 and 2... I think 3 is linked to 2.
Yes, a sort of.

Quote:
Could you kindly elaborate this one? I seem to have missed some simple sql rules... Regarding the table design, I can change it even now because I'm just testing this on 2 million values... The actual data set contains 50 million values...

This 'id' column of trace is an auto_increment... So can I write something like:
If you have the control of the table design, then its relatively easy Smilie

So what I have been trying to say is
with column1 and an auto increment column you could actually build a hash of the auto increment column and the other column_value

something like,

autoincrement other_column
1 10
2 30
3 5

with this, indexing the column and comparing would become straight forward and the retrieval of the values also would be only in o(1)

Quote:
And if I'm not asking too much, I'd like to know about your 4th point too... I've never worked with autocommit... Maybe I'm unknowingly doing that... Is my code potraying that picture by any chance? How can I disable that? Really sorry for the trouble...
do you have any explicit commit statements in your code ? If so, we could be sure we are overriding autocommit and explicitly we control the order and frequency in which the records need to be committed.

With the code segment that you had posted, I don't see any explicit commit statement being used, so I guess it should be " autocommit "

Try changing that, it will definitely improve the performance level Smilie am sure of that.

Previous Thread | Next Thread
Test Your Knowledge in Computers #345
Difficulty: Medium
Scott McNealy received $238 million USD when the Oracle acquisition closed.
True or False?

9 More Discussions You Might Find Interesting

1. Web Development

Can't Install MySQL with PHP

Hi, I'm on a Raspberry Pi with Raspbian Wheezy. I urgently need to get MySQL running with PHP, but I get an error. For example: $con=mysql_connect("127.0.0.1","root","******","ids"); gives PHP Fatal error: Call to undefined function mysql_connect() So, I found I needed to install some... (2 Replies)
Discussion started by: FreddoT
2 Replies

2. Programming

PHP and MySQL

Hello, While I was interpretation the PHP manual on database security the recent past, it said that you should by no means connect to the database as the super user but rather as one more user with more limited options. My question is: How do you generate new users and set access... (2 Replies)
Discussion started by: AimyThomas
2 Replies

3. Emergency UNIX and Linux Support

Migration of website... PHP/Mysql -which path for DB.php

Hi, I have two websites: website1.com and website2.com I didn't write either but have successfully moved all the files from website1.com to website2.com I (thought) I installed all the correct php modules and website2 is mostly up and running. However, my boss found that when we go to a... (15 Replies)
Discussion started by: Astrocloud
15 Replies

4. Shell Programming and Scripting

Mysql is not connected in php

Hi, The php is not able to connect into my mysql database. But i can able to connect by manually. I think that I have missed some points. Please guild for the same. Thanks, Mani (1 Reply)
Discussion started by: Mani_apr08
1 Replies

5. Programming

MySQL - PHP

Hello every one i have question i want to build DATAbase using PHP as interface i use shell to access to linux . i have in linux psql and SQLplus i'll call all html files that has db tabels from shell directory. what should to do before design php pages. can build the database sql design... (3 Replies)
Discussion started by: Scotch
3 Replies

6. Shell Programming and Scripting

PHP/MySQL slow_queries

Hi All, I have a problem with my database having lots of 'stale' slow_queries. I think the problem may be because of the following code: $numresults=mysql_query("select * from links where catagory=".$catagory." order by linknum"); $numrows=mysql_num_rows($numresults); I believe this... (4 Replies)
Discussion started by: pondlife
4 Replies

7. Shell Programming and Scripting

Problem with PHP and MySQL

Okay, I'm new to this PHP and MySQL stuff, so help would be VERY much appreciated. :) On my iMac runnning Panther, it has MySQL and PHP installed. Yet when I view a PHP file from the iMac or another computer at my house, I get the source code. What's wrong? (11 Replies)
Discussion started by: Danny_10
11 Replies

8. UNIX for Dummies Questions & Answers

PHP and MySQL

I want to design a database, using mysql as a backend, and PHP as the frontend, I wanna be able to easily build forms in PHP to communicate with MySQL, is there any programs that will allow this, I really dont want to program all the forms by hand.. thankyou (2 Replies)
Discussion started by: kwalick
2 Replies

9. Cybersecurity

mysql php

with a limited knowledege of php and sql, what is a good and secure way to do passwords running an https server? (1 Reply)
Discussion started by: macdonto
1 Replies

Featured Tech Videos