How do the enterprise web applications do it?

[Benjamin Choi]

Hey all,

I've been wondering how the search engines and other big-table web applications perform queries so quickly. Taking pricegrabber.com as an example:


"But Selling Source’s DB2 migration may not have been necessary. Corey Ostman, CTO at PriceGrabber.com LLC, said his online comparison shopping site maintains a MySQL database with hundreds of tables spread across many servers. He puts the total number of rows at around 200 million. “We have not seen any performance degradation within tables greater than a million rows,” Ostman said.

PriceGrabber has been using MySQL since the company began in 1999; it now boasts about 12 million users. “We often do queries against tables with 10 million rows that are part of a three- or four-table join with excellent performance,” he claimed.

Ostman said that queries are fine-tuned using the EXPLAIN command, which returns a table showing which search strategy MySQL’s optimizer used for a particular SQL statement."

Or take craiglist.com, which has rows anywhere from 6-19 million rows of data in a single table (http://www.mysql.com/customers/customer.php?id=51).

The question is, how do they do this without incurring performance hits? I try to select 1 row from a simple table based on an indexed column and using that column in a single WHERE equals clause, and the query takes 30 seconds on a column with about 10 million rows. And yet these people allow me to search for an item without a fraction of the second. I'm running MyISAM tables on MySQL 4.0.23, standard MySQL configuration, Windows XP on Athlon XP 2100+, 512MB DDR333 RAM.