Comparison of Database Performance

[mathceleb]

We have two databases on the same server. The number of rows on both tables in each database that I'm comparing is less than 200. The query is one select statement which runs like Google's "Instant Search".

On the test database, the query runs in 300 milliseconds including the html return of results.

On the preprod, the query seems to hang up for 3-4 seconds.

My question is, with the exact same server and php code, how do I locate what the issue is between the two databases?

[Old Pedant]

Differences in what fields are indexed?

I'd bet a reasonable amount the PHP has very very little to do with the performancee if this is a single query you are talking about.

I don't see how we can really guess, though, without more details.

[mathceleb]

The database indexing is exact. Essentially, here is what happens:

As a user types a letter, a query is ran against one table, and html results are returned based on the image in the database, a headline, and some summary text through ajax. Code is exactly the same in two environments. Database rows are under 200.

But tracing on FirePHP, it seems that after the last letter is typed, when the display to the page happens on each letter, that takes 3-6 seconds versus milliseconds. I used rawurlencode($html) to send back to javascript and unescape(data) in javascript. data is the variable that is returned through ajax.

[Old Pedant]

Ahh...didn't tumble to the AJAX usage before. I should have.

Are you using GET for your AJAX code?

If so, you could test the turn-around time outside of AJAX by just hitting the URL "by hand" from that browser.

That is, if your AJAX code is doing something like

    xmlhttp.open( "GET", "whatever.php?word=" + letters )

You could just hit the URL whatever.php?word=abc in your browser. See if that takes 3 to 6 seconds. Then go try the SQL query on that server using a query tool of some kind. See how long it takes. If hitting the URL takes 3 to 6 seconds but it's instantaneous with the tool, the the problem is with the web server. Whether PHP or just the server, per se.

[mathceleb]

When running the query manually in MYSQL, it returns in 0.201 seconds. It seems like the problem is when it returns the data to load to the page.

However, even without that, it just seems as a whole that the page load in the preprod environment is slower overall than the dev environment.

Let me try what you said and see what happens.