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
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
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)
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,
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
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
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
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
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
DateTimeNext1126215680?
There are currently 28.53% of the rows
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
When you add that index are more than 30% of the rows in the table
DateTimeNext1126215680?
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
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
DateTimeNext1126215680?
There are currently 28.53% of the rows that have DateTimeNext1126215680
Does this mean something of interest? If so, what?
Thanks
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
DateTimeNext1126215680?
There are currently 28.53% of the rows that have DateTimeNext1126215680
Does this mean something of interest?
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
* 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 with
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 (`id`),
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
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 think
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's done.
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
retrieves
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
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
'.
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 index on an innodb table.
tranfer_logs is an innodb
table, auth_user
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
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]
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 website
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]
list?
Which parts of the process are slow? How does the query perform from the
mysql command line?
-Original Message-
From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED]
Sent: 19 August 2003 17:29
To: [EMAIL PROTECTED]
Subject: Need help optimizing query, awfully slow
: 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 pulled up, it takes about whole 1-2
minutes to come up. inquiries table has 17000 records,
contacts
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 is
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|rows|Extra
inquiries|ALL|contact_id| | | |8253|Using filesort
individual_contacts|eq_ref
]
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|rows|Extra
inquiries|ALL|contact_id| | | |8253
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,
contacts has about 7000, individual_contacts has about
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
], '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 running? There exist a feature in
release 3.23.x where a filesort will be used to perform GROUP BY and ORDER
BY's actions when
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
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.
: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 more efficient to use the index for the where clause
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 the pt
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
in the index.
You may want to try this to see if it makes any difference.
And, of course, run myisamchk -a on the tables after you build indexes.
b.
- Original Message -
From: David Wolf [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, October 28, 2001 4:26 PM
Subject: Optimizing
]
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
fails to use the userid key. i.e. if I only select where
users.username=testuser, I'd expect
--
-- Original Message -
From: Bill Adams [EMAIL 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
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 =
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
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 (2nd attempt)
do you have an index defined
: 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 [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Sunday, October 28, 2001 8:46 AM
Subject: Re: Optimizing query (2nd
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: Optimizing query (2nd attempt)
maybe you heva
-
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 username.
the sql parser now does not use the index
rows
The rows column indicates the number of rows MySQL believes it must examine
to execute the query.
- Original Message -
From: David Wolf [EMAIL PROTECTED]
To: Tore Van Grembergen [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Sunday, October 28, 2001 5:09 PM
Subject: Re: Optimizing query
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
52 matches
Mail list logo
