Performance of MySQL

Question

MyPHP Application sends a SELECT statement to MySQL with HTTPClient.

It takes about 20 seconds or more. I thought MySQL can’t get result immediately because MySQL Administrator shows stat for sending data or copying to tmp table while I'd been waiting for result.

But when I send same SELECT statement from another application like phpMyAdmin or jmater it takes 2 seconds or less.10 times faster!!

Dose anyone know why MySQL perform so difference?

Answer 1

Like @symcbean already said, php's mysql driver caches query results. This is also why you can do another mysql_query() while in a while($row=mysql_fetch_array()) loop.

The reason MySql Administrator or phpMyAdmin shows result so fast is they append a LIMIT 10 to your query behind your back.

If you want to get your query results fast, i can offer some tips. They involve selecting only what you need and when you need:

• Select only the columns you need, don't throw select * everywhere. This might bite you later when you want another column but forget to add it to select statement, so do this when needed (like tables with 100 columns or a million rows).
• Don't throw a 20 by 1000 table in front of your user. She cant find what she's looking for in a giant table anyway. Offer sorting and filtering. As a bonus, find out what she generally looks for and offer a way to show that records with a single click.
• With very big tables, select only primary keys of the records you need. Than retrieve additional details in the while() loop. This might look like illogical 'cause you make more queries but when you deal with queries involving around ~10 tables, hundreds of concurrent users, locks and query caches; things don't always make sense at first :)

These are some tips i learned from my boss and my own experince. As always, YMMV.

Answer 2

Dose anyone know why MySQL perform so difference?

Because MySQL caches query results, and the operating system caches disk I/O (see this link for a description of the process in Linux)