2

Now I must start by saying, I can't copy the string. This is a general question.

I've got a query with several joins in that takes 0.9 seconds when run using the mysql CLI. I'm now trying to run the same query on a PHP site and it's taking 8 seconds. There are some other big joins on the site that are obviously slower, but this string is taking much too long. Is there a PHP cache for database connections that I need to increase? Or is this just to be expected.

Improve this question
2
  • 8 seconds is a lot of time, and not the be expected. You are definitely doing something wrong. Post the query here, along with the output from explain select. Commented Sep 13, 2011 at 15:48
  • Query time depends on what were running at the server that time. Can you please tell that the server was not running anything at both times? Commented Sep 13, 2011 at 15:58

5 Answers 5

Reset to default
5

PHP doesn't really do much with MySQL; it sends a query string to the server, and processes the results. The only bottleneck here is if it's an absolutely vast query string, or if you're getting a lot of results back - PHP has to cache and parse them into an object (or array, depending on which mysql_fetch_* you use). Without knowing what your query or results are like, I can only guess.

(From comments): If we have 30 columns and around, say, a million rows, this will take an age to parse (we later find that it's only 10k rows, so that's ok). You need to rethink how you do things:-

  • See if you can reduce the result set. If you're paginating things, you can use LIMIT clauses.
  • Do more in the MySQL query; instead of getting PHP to sort the results, let MySQL do it by using ORDER BY clauses.
  • Reduce the number of columns you fetch by explicitly specifying each column name instead of SELECT * FROM ....
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Results are pretty big, there are 30 columns and 10000k+ rows (which are then sorted). I think it's the building the array in PHP that's taking the time.
Please see my edits. Can you post a little more detail about what the end-application for this query is? Also, surely you can't mean 10,000 thousand rows can you? That's a rather large database. Do you mean 10k rows?
Sorry, I did mean 10k rows. But the results are then limited using LIMIT and ordered using ORDER BY, I thought you meant the contents of the original table.
The odd thing is, when I profile the query, it looks fine. starting 0.000040 Opening tables 0.000045 System lock 0.000003 Table lock 0.000006 init 0.000010 optimizing 0.000005 statistics 0.000010 preparing 0.000010 executing 0.000025 Sending data 0.000024 end 0.000003 end 0.000003 query end 0.000002 freeing items 0.000003 closing tables 0.000003 removing tmp table 0.000006 closing tables 0.000002 logging slow query 0.000003 cleaning up 0.000002
In that case, it could be your hardware, although 10k rows is still a huge amount to be passing to PHP. In response to your latest comment, that is very strange indeed - I can't see anything that takes any noticeable amount of time. This does show that it's going to be PHP, or the connection between the MySQL and PHP servers, if you have them separate.
|
1

Some wild guesses:

The PHP-version uses different parameters and variables each query: MySQL cannot cache it. While the version you type on the MySQL-CLI uses the same parameter: MySQL can fetch it from its cache. Try adding the SQL_NO_CACHE to your query on CLI to see if the result requires more time.

You are not testing on the same machine? Is the MySQL database you test the PHP-MySQL query with and the CLI the same machine? I mean: you are not testing one on your laptop and the other one on some production server, are you?

You are testing over a network: When the MySQL server is not installed on the same host as your PHP app, you will see some MySQL connection that uses "someserver.tld" instead of "localhost" as database host. In that case PHP will need to connect over a network, while your CLI already has that connection, or connects only local.

The actual connection is taking a long time. Try to run and time the query from your PHP-system a thousand times after each other. Instead of "connect to server, query database, disconnect", you should see the query timing when it is "connect to server, query database thousand times, disconnect". Some PHP-applications do that: they connect and disconnect for each and every query. And if your MySQL server is not configured correctly, connecting can take a gigantic amount of the total time.

Improve this answer

1 Comment

I can run the site on localhost (Apache, PHP and MySQL are all on the same box) and it still takes 8 seconds.
0

How are you timing it?

If the 'long' version is accessed through a php page on a website, could the additional 7.1 seconds not just be the time it takes to send the request and then process and render the results?

Improve this answer

1 Comment

printing a timestamp before the query and after so the rest of the page load time doesn't matter.
0

How are you connecting? Does the account you're using use a hostname in the grant tables? If you're connectinv via TCP, MySQL will have to do a reverse DNS lookup on your IP to figure out if you're allowed in.

If it's the connection causing this, then do a simple test:

select version();

if that takes 8seconds, then it's connection overhead. If it return instantly, then it's PHP overhead in processing the data you've fetched.

Improve this answer

2 Comments

I have many other queries on the site that take 0.01 seconds.
Easy enough to check that too. if it's PHP overhead, then the while() loop to fetch the data will take the longest amount of time. do a microtime(true) call before/after the query call and before/after the fetch loop. That'll get you the total time needed for both.
0

The function mysql_query should should take the same time as mysql client. But any extra mysql_fetch_* will add up.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.