RE: [PHP-DB] How Fast Is mySQL?

A lot depends on the type of data you are storing and the load on the database/web server you expect. The big thing that I found that will make or break your queries is dependant on how you set up your indexes. Creating additional indexes for columns that you will be querying against in your where condition, such as last name + first name, city, zipcode helps immensely. See the mysql manual for more information. Depending on your type of data, another thing that has worked well for me is to build up summary tables of information and query from those when needed. That is ideal if your data will not be changing once it has been stored in the database and you will be running the same queries over and over again. I have use this method at work on my company's intranet with huge success, and on the internet page I maintain for our local emergency management office. To see an example of query against 4.37 million row database (as of this morning), go to http://wcem.co.waseca.mn.us. To display the last update, I do two queries, one gets the maximum timestamp. Once I have that, I query again using that result in my where condition to retreive the record for that timestamp. If you click on the Daily History search button, you will retrieve data from the summary table. The summary table is generated at the top of the hour for the previous hour's worth of data. It does not take long to generate the summary tables (probably less than 30 seconds), but doing it from a from a web browser, it seems to take forever. By using the generated tables, I get the response time (browser click to page display) down to around 2-3 seconds per query and most of that time is sending data back to the requesting machine and having the browser render the page. In case you are wondering, this site is not running on high end hardware. The web server is a p5-100 connected to a T-1. The database server is a p5-150. Both servers are connected to a 10BaseT network and are running on SCSI drives. The mysql database has it's own seperate partition to help with fragementation issues. Hope this helps, Frank -----Original Message----- From: PHP [mailto:tabarry@email.com] Sent: Monday, June 05, 2000 10:46 To: php-db@lists.php.net Subject: [PHP-DB] How Fast Is mySQL? If I have a million records in my mySQL table and I run the Select Query in a PHP page, does anyone has an idea, how long will it take? Thanks in advance! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net