Seems like MySQL is selecting the primary key, or thinks that reading all rows is faster than using the month_day key. What happens if you do one day at a time with an = as in: WHERE month_day = 14
or WHERE month_day = 14 AND month_day=7 (etc) Does it run faster? Does the explain show key used=month_day then? Ken ----- Original Message ----- From: "Mailing Lists" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, April 21, 2004 3:04 AM Subject: 10 minutes seems excessive... > Hello all... > > Source table has approximately 23,000,000 rows. The explain below shows > we'll be working with 2,707,366 of those rows just inserting them into a > table. Why does this take 10 minues to run? System specifications > below all that. > > * Precompiled binary from mysql.com > * Completely idle machine > * Completely idle disk > * Not I/O bound during query. > * Mysqld maxing one cpu out reading data, but not writing to any temp > tables on disk for quit some time. > * Started with mysql-huge reference config. > > > 'explain select * from below': > ============================== > table: source_table > type: range > possible_keys: PRIMARY,MONTH_DAY > key: PRIMARY > key_len: 1 > ref: NULL > rows: 2707366 > Extra: Using where > > CREATE TABLE tmp.seperate_disk_partition > SELECT * FROM source_table > WHERE month_day IN (14, 7, 31, 24) > > Query OK, 2862629 rows affected (9 min 37.36 sec) > Records: 2862629 Duplicates: 0 Warnings: > > * Sun 420, 4-400mhz, 4gb ram. 100gb EMC attached via fiber channel. > > Server version 4.0.18-standard > Protocol version 10 > Connection Localhost via UNIX socket > UNIX socket /tmp/mysql.sock > Uptime: 7 days 9 hours 6 min 50 sec > > Threads: 1 Questions: 3103 Slow queries: 89 Opens: 352 Flush tables: > 1 Open > >C compiler: gcc (GCC) 3.3 > >C++ compiler: gcc (GCC) 3.3 > >Environment: > <machine, os, target, libraries (multiple lines)> > System: SunOS f10212-06.adc1.level3.com 5.8 Generic_108528-22 sun4u > sparc SUNW,U > Architecture: sun4 > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
