From: Michael Dykman
Date: January 18 2011 6:00pm
Subject: Re: optimizing query
List-Archive: http://lists.mysql.com/mysql/224104
Message-Id: <AANLkTi=ZUaevDaCmUJXdkx21QAzsheifq-gnptrzmWJL@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Simon,

once you apply functions to a field, an index on that field is pretty
much useless.  For this particular query, I would be tempted to create
additional fields to store the values of MONTH(articles.created_at)
and DAY(articles.created_at).  Create an index on (month_created,
day_created)  You could just sort by articles.created_at; no need for
the YEAR function, the result will be the same given your other
selectors.

Given those adjustments, the query looks righteous enough.

 - michael dykman

On Tue, Jan 18, 2011 at 12:22 PM, Simon Wilkinson
<simon.wilkinson@stripped> wrote:
> Hi,
>
> I am trying to optimize the following query:
>
> SELECT articles.* FROM articles INNER JOIN newsletters ON
> articles.newsletter_id =3D newsletters.id INNER JOIN users ON users.id =
=3D
> newsletters.user_id WHERE users.id =3D12 AND MONTH(articles.created_at) =
=3D '12'
> AND DAY(articles.created_at) =3D '5' ORDER BY YEAR(articles.created_at),
> LENGTH(articles.body);
>
> I am trying to retrieve all the articles created on a specific day of a
> specific month that belong to a user, ordered by the oldest and then long=
est
> article.
>
> I have run explain on the query, and get the following:
>
> +----+-------------+----------+-------+----------------------------------=
--------------------------------------+------------------------------------=
--------+---------+---------------------+------+---------------------------=
-------------------+
> | id | select_type | table =A0 =A0| type =A0|
> possible_keys =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|
> key =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0| key_len | ref =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
> rows | Extra =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0|
> +----+-------------+----------+-------+----------------------------------=
--------------------------------------+------------------------------------=
--------+---------+---------------------+------+---------------------------=
-------------------+
> | =A01 | SIMPLE =A0 =A0 =A0| users =A0 =A0| const |
> PRIMARY =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|
> PRIMARY =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0| 4 =A0 =A0 =A0 | const
> | =A0 =A01 | Using index; Using temporary; Using filesort |
> | =A01 | SIMPLE =A0 =A0 =A0| newsletters | ref =A0 |
> PRIMARY,index_newsletters_on_user_id =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|
> index_newsletters_on_user_id =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 4 =A0 =
=A0 =A0 |
> const =A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A01 | Using index =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|
> | =A01 | SIMPLE =A0 =A0 =A0| articles =A0| ref =A0 |
> index_articles_on_newsletter_id,index_articles_on_newsletter_id_and_creat=
ed_at
> | index_articles_on_newsletter_id_and_created_at | 4 =A0 =A0 =A0 |
> my_db.newsletters.id | =A0 =A03 | Using where =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|
>
> +----+-------------+----------+-------+----------------------------------=
--------------------------------------+------------------------------------=
--------+---------+---------------------+------+---------------------------=
-------------------+
> 3 rows in set (0.00 sec)
>
> This seems pretty decent, and does perform pretty well for some users (~0=
.5
> - 1 sec), but for some users (seemingly those with large numbers of
> articles) the query can take 20 - 30 seconds to run. =A0This seems really=
 slow
> to me. =A0I tried adding in the index
> 'index_articles_on_newsletter_id_and_created_at' but the performance does=
n't
> seem to be any different then when it uses just the
> 'index_articles_on_newsletter_id' index. =A0I think this might be because=
 of
> the functions I am using on the created_at column to get the day and mont=
h
> from it, making an index on created_at useless in this instance.
>
> Running both an 'optimize table entries' and 'analyze table entries' also
> didn't seem to have any real impact on the performance.
>
> I was wondering if anybody had any suggestions for what else I might be a=
ble
> to try, or if there is a better way to search on dates in this manner. =
=A0Any
> ideas would be greatly appreciated.
>
> Thanks,
>
> Simon
>



--=20
=A0- michael dykman
=A0- mdykman@stripped

=A0May the Source be with you.