List:General Discussion« Previous MessageNext Message »
From:James Harvard Date:January 4 2006 8:44pm
Subject:Inconsistent rows returned & examined in slow query log
View as plain text  
I have entries in my slow query log for identical queries but, as you can see from the log
entries below (including one irrelevant query), the number rows examined and returned
varies. The tables are _not_ being updated.

The query cache is 'on demand', so I'm also not sure why the subsequent queries were not
dealt with by the query cache.

Any ideas?

Thanks, James Harvard

# Time: 060103  9:45:12
# User@Host: lasso[lasso] @ localhost [127.0.0.1]
# Query_time: 86  Lock_time: 0  Rows_sent: 12  Rows_examined: 6733255
select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id =
dates.date_id and dates.date_year = 1998 group by dates.date_month order by
dates.date_month;
# Time: 060103  9:45:46
# User@Host: lasso[lasso] @ localhost [127.0.0.1]
# Query_time: 70  Lock_time: 0  Rows_sent: 7  Rows_examined: 3737912
select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id =
dates.date_id and dates.date_year = 1998 group by dates.date_month order by
dates.date_month;
# User@Host: lasso[lasso] @ localhost [127.0.0.1]
# Query_time: 50  Lock_time: 0  Rows_sent: 4  Rows_examined: 1585713
select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id =
dates.date_id and dates.date_year = 2000 group by dates.date_month order by
dates.date_month;
# User@Host: lasso[lasso] @ localhost [127.0.0.1]
# Query_time: 117  Lock_time: 0  Rows_sent: 9  Rows_examined: 5196480
select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id =
dates.date_id and dates.date_year = 1998 group by dates.date_month order by
dates.date_month;
# User@Host: lasso[lasso] @ localhost [127.0.0.1]
# Query_time: 113  Lock_time: 0  Rows_sent: 9  Rows_examined: 5196063
select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id =
dates.date_id and dates.date_year = 1998 group by dates.date_month order by
dates.date_month;
Thread
Calendar table workaroundJonathan Mangin4 Jan
  • Re: Calendar table workaroundJonathan Mangin4 Jan
    • Re: Calendar table workaroundPeter Brawley4 Jan
      • SELECT DISTINCT uses index but is still slowJames Harvard4 Jan
        • Inconsistent rows returned & examined in slow query logJames Harvard4 Jan
  • RE: Calendar table workaroundJay Paulson \(CE CEN\)4 Jan
  • Re: Calendar table workaroundJonathan Mangin4 Jan
  • Re: SELECT DISTINCT uses index but is still slowC.R.Vegelin5 Jan
    • Re: SELECT DISTINCT uses index but is still slowJames Harvard5 Jan