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)
Hi,
I am trying to optimize the following query:
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 B
Hi,
I am pretty new in optimizing tables with index and may need some help.
This is my query:
EXPLAIN SELECT timestamp
FROM Meting_INT_COPY
WHERE blockid = '200811252000'
ORDER BY timestamp DESC
LIMIT 1
If I have an index(blockid),
EXPLAIN will return the following information:
type possible_ke
On 6/21/07, Guillermo <[EMAIL PROTECTED]> wrote:
Hello,
I have a query that i could do in 2 diferent ways...i want to know wich one
is recomended:
* Select T1.field1,T1.field2, (select sum(T2.field4) from Table2 T2 where
T2.field1 = T1.field1) from Table1 T1
or
* Select P1.fie
Hello,
I have a query that i could do in 2 diferent ways...i want to know wich one
is recomended:
* Select T1.field1,T1.field2, (select sum(T2.field4) from Table2 T2 where
T2.field1 = T1.field1) from Table1 T1
or
* Select P1.field1,P1.field2,P2.field2 from (Select T1.field1,
"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
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 attention. A
value of zero indicates it is being ignored. There are times when *lots* of
records DateTimeNext
On Tue, Nov 23, 2004 at 09:55:15PM +0100, [EMAIL PROTECTED] wrote:
> * Jesse Sheidlower
> [...]
> > CREATE TABLE `citation_subject` (
> > `id` int(11) NOT NULL auto_increment,
> > `citation_id` int(11) NOT NULL default '0',
> > `subject_id` int(11) NOT NULL default '0',
> > PRIMARY KEY (`i
* Jesse Sheidlower
[...]
> CREATE TABLE `citation_subject` (
> `id` int(11) NOT NULL auto_increment,
> `citation_id` int(11) NOT NULL default '0',
> `subject_id` int(11) NOT NULL default '0',
> PRIMARY KEY (`id`),
> KEY `citation_id` (`citation_id`,`subject_id`)
> )
Try adding an index
I have what I thought was a simple, well-indexed query, but it
turns out that it's acting as a pretty big drag. The one thing
that's clearly a problem (though I'm not sure of the extent of
the problem), I'm not sure how to fix.
There are three tables: citations, subjects, and a many-to-many
table
Hello Victor,
> What version of MySQL are you using? Have you
> checked the cardinality on
> these tables?
Problem solved! Optimizing the table brought the query
time down to 17 secs Wow!
Thanks for the input Victor and merci to Arnaud for
the quick fix.
Patrick
___
What version of MySQL are you using? Have you checked the cardinality on
these tables?
-Original Message-
From: Patrick Drouin
To: [EMAIL PROTECTED]
Sent: 7/15/04 8:27 AM
Subject: Need help optimizing query
Hello everyone,
I'm having a hard time with the following query. It
retr
Bonjour Arnaud,
--- Arnaud <[EMAIL PROTECTED]> wrote:
> On 15 Jul 2004 at 6:27, Patrick Drouin
> Your indexes look good, but I see that you have some
> varchar fields.
> Maybe
> you could run an "optimize table" on these tables?
I'm running it at the moment, I will follow-up on the
list when it'
On 15 Jul 2004 at 6:27, Patrick Drouin <[EMAIL PROTECTED]>
wrote:
> I'm having a hard time with the following query. It
> retrieves about 3K rows from a few tables. One of them
> contains over 40M rows. When run on a 3Ghz server with
> 1G of RAM it returns the rows in more than 1 mini. I
> don't
Hello everyone,
I'm having a hard time with the following query. It
retrieves about 3K rows from a few tables. One of them
contains over 40M rows. When run on a 3Ghz server with
1G of RAM it returns the rows in more than 1 mini. I
don't think that's normal.
Here's the output of EXPLAIN:
mysql>
Matt,
Thank you for the help and you are correct the index can't be utilized when using !=.
I will try
your hack/work around.
Thanks again.
sql,query
>Hi Heath,
>
>MySQL cannot use the trans_team query because you're using !=, for which
>an index is never used (currently anyway). Do you think
f the trans_team values ARE 'team oscar'.
Hope that helps.
Matt
- Original Message -
From: "heath boutwell"
Sent: Wednesday, October 08, 2003 3:15 PM
Subject: Innodb won't recognize index when optimizing query
> The query optimizer will not recognize an ind
The query optimizer will not recognize an index on an innodb table. tranfer_logs is an
innodb
table, auth_user is not. As demonstrated below, trans_team is not even recognized as
a possible
key when EXPLAIN SELECT is used. (my apologies for the extra wide message). I am not
sure if this
is an i
Absolutely! I have multiple indexes. I think it might be a problem with ODBC
> Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html
>
> Saqib Ali
> -
> http://www.xml-dev.com
>
>
-
Visit CARMEL MUSIC & ENTERTAINMENT we
Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html
Saqib Ali
-
http://www.xml-dev.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: RE: Need help optimizing query, awfully slow on only
> 2 records
>
>
> When following query is pulled up, it takes about whole 1-2
> minutes to come up. "inquiries" table has 17000 records,
> &
ilto:[EMAIL PROTECTED]
> Sent: Wednesday, August 20, 2003 3:14 PM
> To: Michael S. Fischer
> Cc: [EMAIL PROTECTED]
> Subject: RE: Need help optimizing query, awfully slow on only
> 2 records
>
>
>
> Result of EXPLAIN is:
> table|type|possible_keys|key|key_len|ref|ro
; > Sent: Wednesday, August 20, 2003 3:14 PM
> > To: Michael S. Fischer
> > Cc: [EMAIL PROTECTED]
> > Subject: RE: Need help optimizing query, awfully slow on only
> > 2 records
> >
> >
> >
> > Result of EXPLAIN is:
> > table|type|poss
Query takes 3.4 seconds to run on the server, but it takes 1-2minutes to run via
MyODBC 3.51.06 using passthrough (Access97 is the front end, but it has query
type that allows bypass of Access interpretation.
> Two Questions:
>
> Is the same query running directly on the linux server thru mysql i
ment) [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, August 20, 2003 2:52 PM
> > To: [EMAIL PROTECTED]
> > Cc: [EMAIL PROTECTED]
> > Subject: RE: Need help optimizing query, awfully slow on only
> > 2 records
> >
> >
> > When following query is pull
gt; > Sent: 19 August 2003 17:29
> > To: [EMAIL PROTECTED]
> > Subject: Need help optimizing query, awfully slow on only
> > 2 records
> >
> >
> > 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0
> >
> > I have about 20K records that res
IL PROTECTED]
> Subject: Need help optimizing query, awfully slow on only
> 2 records
>
>
> 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0
>
> I have about 20K records that result from the following
> query. Front end for the
> database is ACCESS97 and pulling up
Two Questions:
Is the same query running directly on the linux server thru mysql is also very slow ?
Have you done a explain plan on the query ?
Marc.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0
I have about 20K records that result from the following query. Front end for the
database is ACCESS97 and pulling up 20K records makes a huge performance hit.
For the form in question I am using PASSTHROUGH type query (the one that just
pa
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.
Hi,
I'm having trouble getting the following query to use the indexes on the
order by.
SELECT vr.muzeid, vr.releaseformat, vr.releasestatus, vr.collectorsedition,
pt.title FROM v_videorelease vr, v_producttitle pt, v_prodcategory pc WHERE
pc.muzeid = vr.muzeid AND pt.muzeid = vr.muzeid AND vr.rel
Greetings, I am struggling trying to get good performance
using indexes.
I'll spare the details and narrow down the situation. I have about
100,000 rows of data with the following fields:
title - vachar(64)
description - text
state - varchar(2)
priority - int
modified - date
People type in
I need help optimizing the query below. I've included the query, the
table declerations, and the output of explain. Any help would be greatly
appreciated.
What I'd really like is to find out how to get it to use a key on the
lineitems table. I've tried adding indexes on the OrderID and MerchantID
-
> -+---+-+-+-+---
>
> --++
> 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
<[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,
> &
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
, October 28, 2001 5:09 PM
Subject: Re: Optimizing query (2nd attempt)
> I seem to have fixed it.. I ran myisamchk on all the tables--and now the
> indexes work as expected.. Very strange indeed.. Though, there is a
strange
> twist now: explain reports fewer rows to be checked than are d
-
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 l
I have a query as follows:
SELECT log.entity, log.action,
LEFT(users.username,10) AS username,
LEFT(boards.title,15) AS Board,
LEFT(topics.subject,22) as Subject,
log.postid, log.extraid,
LEFT(from_unixtime(log.logtime),19) AS time, log.ip
FROM log LEFT JOIN users ON log.userid = users.
53 matches
Mail list logo
