Re: number of rows in EXPLAIN for unpacked vs packed tables
Hi Dan, The contents of both tables is identically. I checked both tables with SHOW INDEX FROM ... and the cardinalities are exactly the same. Nevertheless, thanks for mentioning that those numbers are estimates. Regards, Cor - Original Message - From: Dan Buettner [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, June 30, 2006 4:36 PM Subject: Re: number of rows in EXPLAIN for unpacked vs packed tables Cor - Those numbers are an estimate, not a hard number, of how many rows MySQL thinks it will have to exmaine to find what you're looking for. They could be different because your compressed table is brand-new and all the indexes are clean and up to date, whereas your original table has possibly been inserted, updated, and deleted from, causing the key information to be less accurate. You could try running a CHECK TABLE tablename EXTENDED on the old one, which should update all the key info, and then checking your EXPLAIN results again. Be aware it could take a while for a large table and will lock the table for the duration. If your indexes are different on the compressed table that would make a difference too, as MySQL might well be basing its estimate off a different index. Dan On 6/30/06, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi All, I compressed a MyISAM table successfully with MYISAMPACK, followed by MYISAMCHK. Both tables (MyISAM + Compressed ) have exactly the same number of rows with Count(*). But when I give a SELECT query with EXPLAIN on both tables, I get different number of rows. For example: EXPLAIN SELECT ... FROM data ... shows 1983 rows, but the same EXPLAIN SELECT ... FROM pack ... shows 2064 rows. Any idea why ? Regards, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
number of rows in EXPLAIN for unpacked vs packed tables
Hi All, I compressed a MyISAM table successfully with MYISAMPACK, followed by MYISAMCHK. Both tables (MyISAM + Compressed ) have exactly the same number of rows with Count(*). But when I give a SELECT query with EXPLAIN on both tables, I get different number of rows. For example: EXPLAIN SELECT ... FROM data ... shows 1983 rows, but the same EXPLAIN SELECT ... FROM pack ... shows 2064 rows. Any idea why ? Regards, Cor
Re: number of rows in EXPLAIN for unpacked vs packed tables
Cor - Those numbers are an estimate, not a hard number, of how many rows MySQL thinks it will have to exmaine to find what you're looking for. They could be different because your compressed table is brand-new and all the indexes are clean and up to date, whereas your original table has possibly been inserted, updated, and deleted from, causing the key information to be less accurate. You could try running a CHECK TABLE tablename EXTENDED on the old one, which should update all the key info, and then checking your EXPLAIN results again. Be aware it could take a while for a large table and will lock the table for the duration. If your indexes are different on the compressed table that would make a difference too, as MySQL might well be basing its estimate off a different index. Dan On 6/30/06, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi All, I compressed a MyISAM table successfully with MYISAMPACK, followed by MYISAMCHK. Both tables (MyISAM + Compressed ) have exactly the same number of rows with Count(*). But when I give a SELECT query with EXPLAIN on both tables, I get different number of rows. For example: EXPLAIN SELECT ... FROM data ... shows 1983 rows, but the same EXPLAIN SELECT ... FROM pack ... shows 2064 rows. Any idea why ? Regards, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
