List:General Discussion« Previous MessageNext Message »
From:Jake Maul Date:December 27 2008 6:38pm
Subject:Re: Performance tips
View as plain text  
I few random things come to mind...

1) Try the query with IGNORE INDEX calldate_idx ... I can't see how
this could possibly be faster, but I always like to check anyway. In
your case this should result in a full table scan, given the
information you've given us.

2) If the performance problem comes from the date_format() function,
there might not be much you can do about it, except to call it less
often. Maybe you could add one or more WHERE clauses to restrict the
range of rows you're looking at? (WHERE calldate > "2000-01-01
01:01:01" or something).

3) Obviously it'd probably be faster if you weren't using
SQL_NO_CACHE... guessing you just did that to show us what it's like
that way?

4) I'd check the values of sort_buffer_size and read_rnd_buffer_size.
Perhaps you could benefit from raising them slightly.

As for general performance tips, Here's a couple scripts I like to use
that give some decent tuning advice:

http://www.day32.com/MySQL/
http://wiki.mysqltuner.com/MySQLTuner

Neither of these will be specific to this particular query of course.
There is a query profiler tool in maatkit (http://www.maatkit.org/),
however... might be worth a shot.

Good luck,
Jake

On Sat, Dec 27, 2008 at 10:15 AM, Chris Picton <chris@stripped> wrote:
> Hi
>
> I am trying to get to grips with understanding mysql performance.
>
> I have the following query:
>
> select sql_no_cache count(*), date_format(calldate, '%y-%m-%d') as m from
> cdr_warehouse group by m;
>
> This gives me:
> 115 rows in set (59.52 sec)
>
>
> mysql> explain select count(*), date_format(calldate, '%y-%m-%d') as m from
> cdr_warehouse group by m\G
> *************************** 1. row ***************************
>           id: 1
>  select_type: SIMPLE
>        table: cdr_warehouse
>         type: index
> possible_keys: NULL
>          key: calldate_idx
>      key_len: 8
>          ref: NULL
>         rows: 43708571
>        Extra: Using index; Using temporary; Using filesort
> 1 row in set (0.00 sec)
>
>
> mysql> show keys from cdr_warehouse \G;
> *************************** 1. row ***************************
>       Table: cdr_warehouse
>  Non_unique: 1
>    Key_name: uploaded_idx
> Seq_in_index: 1
>  Column_name: uploaded
>   Collation: A
>  Cardinality: 66
>    Sub_part: NULL
>      Packed: NULL
>        Null:
>  Index_type: BTREE
>     Comment:
> *************************** 2. row ***************************
>       Table: cdr_warehouse
>  Non_unique: 1
>    Key_name: calldate_idx
> Seq_in_index: 1
>  Column_name: calldate
>   Collation: A
>  Cardinality: 5526774
>    Sub_part: NULL
>      Packed: NULL
>        Null:
>  Index_type: BTREE
>     Comment:
>
>
> How can I improve the running speed of this query?  I am running 5.1.30, but
> don't (yet) want to partition the table (horizontally or vertically).
>  Nothing else on the server is touching this table at the moment.  The exact
> date_format is not important, as I may want to group by 5 second intervals,
> or full months.
>
> Any tips/ideas for me?
>
> Chris
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
Thread
Performance tipsChris Picton27 Dec
  • Re: Performance tipsJake Maul27 Dec
    • Re: Performance tipsJim Lyons30 Dec
      • Re: Performance tipsBaron Schwartz31 Dec
  • Re: Performance tipsDan Nelson27 Dec
  • Re: Performance tipsewen fortune27 Dec
    • Re: Performance tipsewen fortune27 Dec