----- Original Message ----- From: "matt ryan" <[EMAIL PROTECTED]>
> I need the DIC in the key to keep the record unique, I have thousands > with everything identical except the DIC. In your schema you had DIC in the PRIMARY KEY and an additional (unnecessary?) KEY on DIC. > I was confused on the multi key index issue, I thought it would seek > faster if I put the most unique field up front, which I do on most > tables, I did not on this one though. I have one large raid array now, > so I cant split the data, or put the binary log on another disk. Having performed a couple of tests on a multi-key index I can't find any significant difference between different orders on the columns (logical I guess), although there may be certain uses that might work better in either situation ... Perhaps a better point to make would be to say change the order of your PRIMARY KEY to reflect the queries that you're performing. e.g. as I said above MySQL will efficiently use the first part of any key. Certainly, if you're performing a WHERE or ORDER BY on any of the columns in the primary key put those at the front. > I found mysql was great up to about 3 gig, then everything hit the > brakes and got really really really slow > > I'm scared of joines, every time I do a join in mysql on indexed fields > in mysql, the performance is horrible, because the where clause is not a > field that's in the join, performance is poopy Hmm, how about (sorry if I'm pointing out the obvious) e.g.: _left (col1 int, col2 int, col3 int, unique(col3,col1,col2)); _right (col1 int, col2 int, col3 int, unique(col1,col2,col3)); _left inner join _right using(col1,col2) where _left.col3=42 and _right.col3=35; The analyzer (explain) shows a const at the end of the ref columns for _right and const for _left. All the best, Tim. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
