On Sat, 31 Jul 2004 17:50:38 -0500, Keith Thompson <[EMAIL PROTECTED]> wrote:
> I just discovered that two of my tables (out of about 300) > show a very unusual behavior. This is that "select count(*) ..." > and selecting all the rows and counting them do not produce > the same number. > > This is on MySQL 4.1.3 on Solaris9. Look at this: > > $ mysql -e "select count(*) from pstat.plist" > +----------+ > | count(*) | > +----------+ > | 15315 | > +----------+ > $ mysql -e "select * from pstat.plist" | wc -l > 15372 > > Actually, these counts shouldn't quite be the same. The second > produces a header line that's getting counted, so it should be > one more than the count(*). But, it's off by 57! The other bad > table is off by 3. First, have you verified there is no data in the table with embedded newlines or some such? Perhaps there is some index corruption.. Do an explain on the count(*), it is likely doing an index scan. Then try a select column_in_index_that_is_being_used from pstat.plist and see if that returns the same as the count(*), or try doing the select count(*) with an "ignore index" of whichever index it is using. If it seems to be related to that one index, you could try dropping and rebuilding the index. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
