I have some queries that return around 75,000 rows, and I've been trying to figure out how to speed them up a little. In the course of looking for the bottleneck, I discovered that simply getting a large result was considerably slower on OS X.
I tested on a number of machines, with MySQL versions from 4.0.16 to 4.1.5, running either Mac OS X 10.3.x, Linux, or FreeBSD. In each case, the x86 machine reported a time of 0.12 sec - 0.20 sec for the last SELECT below, and the Mac reported in the range of 0.75 sec - 1.0 sec. Configurations (RAM/CPU MHz/system load) were similar (tested on a dual 2GHz G5), and I checked all MySQL variables that seemed relevant. Speeds were consistent and repeatable. I understand that MySQL may be more optimized for x86, or just run better on that architecture, but a difference of this magnitude seems wrong. I'm interested in the reported time for the last query below. mysql running on same machine as mysqld. Setup: CREATE TEMPORARY TABLE tmp (tid MEDIUMINT UNSIGNED NOT NULL); INSERT INTO tmp values (42); CREATE TEMPORARY TABLE tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; Benchmark (75,025 rows): SELECT * FROM tmp; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
