Compressing large chunks of data

access9

Scenario: Our customers can login through our website and view the inventory that has been delivered to our warehouse. Our public webserver is located at the ISP, our dbase (MySQL) is onsite. They login through our website, and the data is loaded from our server. It works, no problems there.

Problem: When their inventory becomes fairly large, the load times are outrageous.

Questions: Is there a way to compress the data before it gets sent back to the client? Is this something that I should be doing on the dbase server rather than in my code?

Thanks,
access9

naomi385

You can use PHP's output buffering features to gzip every page before sending it to the browser, providing that the browser can handle the gzip content type (and most can).

http://us3.php.net/pt_BR/ob-gzhandler

However, you should be sure that this is a problem with data transmission and not the MySQL database.

Are your customers on dial-up lines? Most people on broadband connections would not likely notice any difference between gzipped and uncompressed data on a web page.

But because you say that load times increase as the inventory becomes larger, I suspect your problem is with a slow SQL query. I suggest you analyze the queries you use to generate the page by measuring the execution time of the mysql_query() function. Then take note of which queries take the longest.

Once you have identified a problem query, you should use the SQL EXPLAIN command to analyze the specific query further. Read Chapter 5 ('MySQL Optimisation') of the MySQL manual for more information.

Do you have any indeces on your database tables? Do you know if they are being used? Do you need more?

superwormy

If I understand correctly... the problem is that theres a large amount of data flowing over TCP/IP from MySQL to your remote webserver.

First though, make sure its not your queries that are really slow... benchmark EVERYTHING you can, and optimize slow queries.

If its just the amount of data thats the problem, make sure your queries only get the data they need to, don't always do SELECT * FROM..., do SELECT only, fields, you, need FROM ... instead.

Also MysQL supports a compressed client/server protocal to compress the data before sending to the client... that might help speed things up...

access9

Thanks for the hints. I did some research on the compression client/server protocol and found that the fifth argument in the mysql_connect() command can be MYSQL_CLIENT_COMPRESS. The transmitted data was compressed by more than half of what it was, thus decreasing transmission time for the client.

Oh, and thanks for the opimization tips for my queries. I don't use SELECT * FROM statements, but I do need a better understanding of how to optimize the tables using indexes. I'm not a DBA, I'm a sys admin who developes PHP apps for my company. Go figure.

-a9