Hi, I currently have a v3.2.0 database that contains two tables that I regularly query with a join, e.g.,
select t1.a, t1.b, t2.c, t2.d from t1 join t2 on t1.x = t2.x and t1.a >= 100 and t1.a < 200 group by t1.a, t1.b, t2.c, t2.d Table t1 has an index on a Table t2 has an index on x I've created a new v3.3.4 database with the same schema and data. Table t1 has about 150 million rows and t2 has about 3000 rows. When I run the query using v3.2.0, it returns in about 0.1 seconds. When run using v3.3.4, it takes about 20 seconds. For completeness, I ran the same query using v3.2.8, v3.3.2, and v3.3.3. v3.2.8 performed the same (roughly) as v3.2.0; v3.3.2 and v3.3.3 performed the same as v3.3.4 (so, it appears that the change was made going into v3.3.x). When I ran an explain on the query in both databases, the main difference I noticed was in the opening of the database tables; in v3.2.0, it appears that the indices of both tables are being used... addr opcode p1 p2 p3 ---- -------------- ---------- ---------- --------------------------------- <snip> 37 OpenRead 1 4 38 SetNumColumns 1 7 39 Integer 0 0 40 OpenRead 3 6 keyinfo(1,BINARY) 41 Integer 2 0 42 OpenRead 0 2 43 SetNumColumns 0 8 44 Integer 2 0 45 OpenRead 4 3 keyinfo(1,BINARY) 46 Integer 3795633 0 <snip> In v3.3.4, it appears that the index of only one of the tables is being used... addr opcode p1 p2 p3 ---- -------------- ---------- ---------- --------------------------------- <snip> 37 OpenRead 0 2 38 SetNumColumns 0 3 39 Integer 0 0 40 OpenRead 1 4 41 SetNumColumns 1 7 42 Integer 0 0 43 OpenRead 3 6 keyinfo(1,BINARY) 44 Rewind 0 78 45 Integer 3795633 0 <snip> It's quite possible that I'm misinterpreting the explain data as I'm fairly new with it... However, it seems to me that this might explain the huge difference in performance. Has anyone else seen this type of performance difference between v3.2.x and v3.3.x? Any ideas on how we can improve the performance using v3.3.x (we'd really like to use the most current version of the database, but the performance difference would be a killer)? Thanks, Steve -- Steve Green SAVVIS Transforming Information Technology SM This message contains information which may be confidential and/or privileged. Unless you are the intended recipient (or authorized to receive for the intended recipient), you may not read, use, copy or disclose to anyone the message or any information contained in the message. If you have received the message in error, please advise the sender by reply e-mail at [EMAIL PROTECTED] and delete the message and any attachment(s) thereto without retaining any copies.
