Thanks for the suggestions everybody.
I added in columns to store the day, month and year of the created_at value,
and then added in an index on (newsletter_id, created_month, created_day),
and the the slow queries reduced from around 20 seconds to 0.5 seconds! I
also removed the redundant indexe
On 1/18/11 10:22 AM, Simon Wilkinson wrote:
SELECT articles.* FROM articles INNER JOIN newsletters ON
articles.newsletter_id = newsletters.id INNER JOIN users ON users.id =
newsletters.user_id WHERE users.id =12 AND MONTH(articles.created_at) = '12'
AND DAY(articles.created_at) = '5' ORDER BY YEA
I concur. In addition to suggested index I would add a new column in articles
table called body_length, which is going to be updated every time the body
column is updated. Add that column to the composite index mentioned below.
This should speed up the query a lot.
Cheers,
Mihail
On Jan 18, 20
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)
"Devananda" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Dan Baker wrote:
>> "Eric Bergen" <[EMAIL PROTECTED]> wrote in message
>> news:[EMAIL PROTECTED]
>>
>>>When you add that index are more than 30% of the rows in the table
>>>DateTimeNext>1126215680?
>>
>>
>> There are curre
Dan Baker wrote:
"Eric Bergen" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
When you add that index are more than 30% of the rows in the table
DateTimeNext>1126215680?
There are currently 28.53% of the rows that have "DateTimeNext>1126215680"
Does this mean something of inter
"Eric Bergen" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> When you add that index are more than 30% of the rows in the table
> DateTimeNext>1126215680?
There are currently 28.53% of the rows that have "DateTimeNext>1126215680"
Does this mean something of interest? If so, what?
When you add that index are more than 30% of the rows in the table
DateTimeNext>1126215680?
Dan Baker wrote:
I have lots of tables that are similar in nature:
id int(11) PRI NULL auto_increment
Name varchar(30)
DateTimeNext int(11)
The "DateTimeNext" field represents when this records needs
enig '" <[EMAIL PROTECTED]>, "'gerald_clark '"
> <[EMAIL PROTECTED]>
> Cc: "'[EMAIL PROTECTED] '" <[EMAIL PROTECTED]>
> Subject: RE: Optimizing Query to use Index in ORDER BY
>
> What version of MySQL are you currently ru
I am
off base with this information.
I hope this helps.
Victor Pendleton
-Original Message-
From: Joseph Koenig
To: gerald_clark
Cc: [EMAIL PROTECTED]
Sent: 10/23/02 2:29 PM
Subject: Re: Optimizing Query to use Index in ORDER BY
The explain shows that it is using the muzeid key for t
Wed, 23 Oct 2002 14:00:29 -0500
> To: Joseph Koenig <[EMAIL PROTECTED]>
> Cc: [EMAIL PROTECTED]
> Subject: Re: Optimizing Query to use Index in ORDER BY
>
> Does explain say an index is used on pt for the join?
> Only one index per table is used in a query.
> It is probably
Does explain say an index is used on pt for the join?
Only one index per table is used in a query.
It is probably more efficient to use the index for the where clause than
the order by.
Joseph Koenig wrote:
Hi,
I'm having trouble getting the following query to use the indexes on the
order by.
-
> -+---+-+-+-+---
>
> --++
> 4 rows in set (0.00 sec)
>
> It's just simply not using the index on users..
> Did I miss something?
>
> David
>
> --
> -- Original Message -
> From: "Bi
L PROTECTED]>
To: "David Wolf" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, October 29, 2001 9:21 AM
Subject: Re: Optimizing query (2nd attempt)
> David Wolf wrote:
>
> >Not quite fixed.. When I run the query without limiting by time, it still
&g
David Wolf wrote:
>Not quite fixed.. When I run the query without limiting by time, it still
>fails to use the userid key. i.e. if I only select where
>users.username="testuser", I'd expect that users.username to return the
>users.id=2, and to search using the indexed log.userid=2
MySQL can onl
Not quite fixed.. When I run the query without limiting by time, it still
fails to use the userid key. i.e. if I only select where
users.username="testuser", I'd expect that users.username to return the
users.id=2, and to search using the indexed log.userid=2
David
isplayed
with
> the query runs.. Is that normal?
>
> David
>
> - Original Message -
> From: "Tore Van Grembergen" <[EMAIL PROTECTED]>
> To: "David Wolf" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Sunday, October 28, 2001 8:57 AM
>
-
From: "Tore Van Grembergen" <[EMAIL PROTECTED]>
To: "David Wolf" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Sunday, October 28, 2001 8:57 AM
Subject: Re: Optimizing query (2nd attempt)
> maybe you heva to declare a compound index with userid and userna
How do you do a compound index to index between two tables?
David
- Original Message -
From: "Tore Van Grembergen" <[EMAIL PROTECTED]>
To: "David Wolf" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Sunday, October 28, 2001 8:57 AM
Subject: Re: Op
t;
Sent: Sunday, October 28, 2001 4:46 PM
Subject: Re: Optimizing query (2nd attempt)
> Yes.. There is an index on users.username :)
>
> David
>
> - Original Message -
> From: "Tore Van Grembergen" <[EMAIL PROTECTED]>
> To: "David Wolf" <[E
Yes.. There is an index on users.username :)
David
- Original Message -
From: "Tore Van Grembergen" <[EMAIL PROTECTED]>
To: "David Wolf" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Sunday, October 28, 2001 8:46 AM
Subject: Re: Optimizing query
do you have an index defined on users.username ?
- Original Message -
From: "David Wolf" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, October 28, 2001 4:26 PM
Subject: Optimizing query (2nd attempt)
> I have a query as follows:
>
> SELECT log.entity, log.action,
> LEFT(u
22 matches
Mail list logo
