Re: optimizing query
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 wrote: > 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 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 longest > article. > > I have run explain on the query, and get the following: > > ++-+--+---+++-+-+--+--+ > | id | select_type | table | type | > possible_keys | > key | key_len | ref | > rows | Extra | > ++-+--+---+++-+-+--+--+ > | 1 | SIMPLE | users | const | > PRIMARY | > PRIMARY | 4 | const > | 1 | Using index; Using temporary; Using filesort | > | 1 | SIMPLE | newsletters | ref | > PRIMARY,index_newsletters_on_user_id | > index_newsletters_on_user_id | 4 | > const | 1 | Using index | > | 1 | SIMPLE | articles | ref | > index_articles_on_newsletter_id,index_articles_on_newsletter_id_and_created_at > | index_articles_on_newsletter_id_and_created_at | 4 | > my_db.newsletters.id | 3 | Using where | > > ++-+--+---+++-+-+--+--+ > 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. This seems really slow > to me. I tried adding in the index > 'index_articles_on_newsletter_id_and_created_at' but the performance doesn't > seem to be any different then when it uses just the > 'index_articles_on_newsletter_id' index. I think this might be because of > the functions I am using on the created_at column to get the day and month > 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 able > to try, or if there is a better way to search on dates in this manner. Any > ideas would be greatly appreciated. > > Thanks, > > Simon > -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: optimizing query
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, 2011, at 13:03, "Michael Dykman" wrote: > 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 > wrote: >> 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 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 longest >> article. >> >> I have run explain on the query, and get the following: >> >> ++-+--+---+++-+-+--+--+ >> | id | select_type | table| type | >> possible_keys | >> key| key_len | ref | >> rows | Extra| >> ++-+--+---+++-+-+--+--+ >> | 1 | SIMPLE | users| const | >> PRIMARY| >> PRIMARY| 4 | const >> |1 | Using index; Using temporary; Using filesort | >> | 1 | SIMPLE | newsletters | ref | >> PRIMARY,index_newsletters_on_user_id | >> index_newsletters_on_user_id | 4 | >> const |1 | Using index | >> | 1 | SIMPLE | articles | ref | >> index_articles_on_newsletter_id,index_articles_on_newsletter_id_and_created_at >> | index_articles_on_newsletter_id_and_created_at | 4 | >> my_db.newsletters.id |3 | Using where | >> >> ++-+--+---+++-+-+--+--+ >> 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. This seems really slow >> to me. I tried adding in the index >> 'index_articles_on_newsletter_id_and_created_at' but the performance doesn't >> seem to be any different then when it uses just the >> 'index_articles_on_newsletter_id' index. I think this might be because of >> the functions I am using on the created_at column to get the day and month >> 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 able >> to try, or if there is a better way to search on dates in this manner. Any >> ideas would be greatly appreciated. >> >> Thanks, >> >> Simon >> > > > > -- > - michael dykman > - mdyk...@gmail.com > > May the Source be with you. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=mmano...@liquidation.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: optimizing query
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 YEAR(articles.created_at), LENGTH(articles.body); Simon - There are a few issues that are slowing down your query. First, you're running functions to calculate the month and day of each article that is looked at. As an aside, are you sure you don't want the DAYOFMONTH() function? Second, it's ideal to have the where clause in your query filter down (using an index) to as few rows as possible of the first table. Other tables you join should ideally be 1 to 1 from the first table. To accomplish this, you would probably need the user_id in your articles table. Another aside -- I noticed you have index_articles_on_newsletter_id as well as index_articles_on_newsletter_id_and_created_at. The first index is redundant, the second index will take care of it. This will slow down your INSERT/UPDATE/DELETE queries to some degree. Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: optimizing query
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 indexes. Cheers, Simon On 19 January 2011 02:11, Steve Meyers wrote: > 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 YEAR(articles.created_at), >> LENGTH(articles.body); >> > > Simon - > > There are a few issues that are slowing down your query. First, you're > running functions to calculate the month and day of each article that is > looked at. As an aside, are you sure you don't want the DAYOFMONTH() > function? > > Second, it's ideal to have the where clause in your query filter down > (using an index) to as few rows as possible of the first table. Other > tables you join should ideally be 1 to 1 from the first table. To > accomplish this, you would probably need the user_id in your articles table. > > Another aside -- I noticed you have index_articles_on_newsletter_id as well > as index_articles_on_newsletter_id_and_created_at. The first index is > redundant, the second index will take care of it. This will slow down your > INSERT/UPDATE/DELETE queries to some degree. > > Steve >
Re: Optimizing query (2nd attempt)
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(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.id
> LEFT JOIN boards ON log.boardid=boards.id
> LEFT JOIN topics ON log.topicid = topics.id
> WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> AND users.username="testuser";
>
> When I run an 'explain' on it, I get:
>
>
+++---+-+-+-+---
> --++
> | table | type | possible_keys | key | key_len | ref | rows
> | Extra |
>
+++---+-+-+-+---
> --++
> | log| ALL| time | NULL|NULL | NULL|
> 1192384 | where used |
> | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId |
> 1 | where used |
> | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId |
> 1 ||
> | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId |
> 1 ||
>
+++---+-+-+-+---
> --++
> 4 rows in set (0.01 sec)
>
> Now.. If I exclude the 'users.username="testuser"' and substitute it for
the
> userid that I got in a previous query (i.e. userid=2) so that the query
> becomes:
>
> 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.id
> LEFT JOIN boards ON log.boardid=boards.id
> LEFT JOIN topics ON log.topicid = topics.id
> WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> AND log.userid=2;
>
>
> and run an explain, I get...
>
>
+++---+-+-+-+---
> ++
> | table | type | possible_keys | key | key_len | ref | rows
> | Extra |
>
+++---+-+-+-+---
> ++
> | log| ref| time,userid | userid | 4 | const |
27198
> | where used |
> | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId |
1
> ||
> | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId |
1
> ||
> | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId |
1
> ||
>
+++---+-+-+-+---
> ++
> 4 rows in set (0.00 sec)
>
> Big difference from 1.19million rows to 27198 rows... My question is this.
> How can I optimize the query with the left joins so that the optimizer
will
> first grab the userid from the username and then use the userid index on
log
> to return the results fast?
>
> Thanks in advance,
>
> David
>
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
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 attempt)
> 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(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.id
> > LEFT JOIN boards ON log.boardid=boards.id
> > LEFT JOIN topics ON log.topicid = topics.id
> > WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> > AND users.username="testuser";
> >
> > When I run an 'explain' on it, I get:
> >
> >
>
+++---+-+-+-+---
> > --++
> > | table | type | possible_keys | key | key_len | ref |
rows
> > | Extra |
> >
>
+++---+-+-+-+---
> > --++
> > | log| ALL| time | NULL|NULL | NULL|
> > 1192384 | where used |
> > | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId |
> > 1 | where used |
> > | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId |
> > 1 ||
> > | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId |
> > 1 ||
> >
>
+++---+-+-+-+---
> > --++
> > 4 rows in set (0.01 sec)
> >
> > Now.. If I exclude the 'users.username="testuser"' and substitute it for
> the
> > userid that I got in a previous query (i.e. userid=2) so that the query
> > becomes:
> >
> > 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.id
> > LEFT JOIN boards ON log.boardid=boards.id
> > LEFT JOIN topics ON log.topicid = topics.id
> > WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> > AND log.userid=2;
> >
> >
> > and run an explain, I get...
> >
> >
>
+++---+-+-+-+---
> > ++
> > | table | type | possible_keys | key | key_len | ref |
rows
> > | Extra |
> >
>
+++---+-+-+-+---
> > ++
> > | log| ref| time,userid | userid | 4 | const |
> 27198
> > | where used |
> > | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId |
> 1
> > ||
> > | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId |
> 1
> > ||
> > | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId |
> 1
> > ||
> >
>
+++---+-+-+-+---
> > ++
> > 4 rows in set (0.00 sec)
> >
> > Big difference from 1.19million rows to 27198 rows... My question is
this.
> > How can I optimize the query with the left joins so that the optimizer
> will
> > first grab the userid from the username and then use the userid index on
> log
> > to return the results fast?
> >
> > Thanks in advance,
> >
> > David
> >
> >
> >
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php (the manual)
> >http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
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 on username.
- Original Message -
From: "David Wolf" <[EMAIL PROTECTED]>
To: "Tore Van Grembergen" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Sunday, October 28, 2001 8:46 AM
> Subject: Re: Optimizing query (2nd attempt)
>
>
> > 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(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.id
> > > LEFT JOIN boards ON log.boardid=boards.id
> > > LEFT JOIN topics ON log.topicid = topics.id
> > > WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> > > AND users.username="testuser";
> > >
> > > When I run an 'explain' on it, I get:
> > >
> > >
> >
>
+++---+-+-+-+---
> > > --++
> > > | table | type | possible_keys | key | key_len | ref |
> rows
> > > | Extra |
> > >
> >
>
+++---+-+-+-+---
> > > --++
> > > | log| ALL| time | NULL|NULL | NULL|
> > > 1192384 | where used |
> > > | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId |
> > > 1 | where used |
> > > | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId |
> > > 1 ||
> > > | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId |
> > > 1 ||
> > >
> >
>
+++---+-+-+-+---
> > > --++
> > > 4 rows in set (0.01 sec)
> > >
> > > Now.. If I exclude the 'users.username="testuser"' and substitute it
for
> > the
> > > userid that I got in a previous query (i.e. userid=2) so that the
query
> > > becomes:
> > >
> > > 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.id
> > > LEFT JOIN boards ON log.boardid=boards.id
> > > LEFT JOIN topics ON log.topicid = topics.id
> > > WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> > > AND log.userid=2;
> > >
> > >
> > > and run an explain, I get...
> > >
> > >
> >
>
+++---+-+-+-+---
> > > ++
> > > | table | type | possible_keys | key | key_len | ref |
> rows
> > > | Extra |
> > >
> >
>
+++---+-+-+-+---
> > > ++
> > > | log| ref| time,userid | userid | 4 | const |
> > 27198
> > > | where used |
> > > | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId |
> > 1
> > > ||
> > > | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId |
> > 1
> > > ||
> > > | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId |
> > 1
> > > ||
> > >
> >
>
+++---+-+-+-+---
> > > ++
> > > 4 rows in set (0.0
Re: Optimizing query (2nd attempt)
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 to declare a compound index with userid and username. > the sql parser now does not use the index on username. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
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 displayed 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
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 on username.
>
> - Original Message -
> From: "David Wolf" <[EMAIL PROTECTED]>
> To: "Tore Van Grembergen" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> 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" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > Sent: Sunday, October 28, 2001 8:46 AM
> > Subject: Re: Optimizing query (2nd attempt)
> >
> >
> > > 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(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.id
> > > > LEFT JOIN boards ON log.boardid=boards.id
> > > > LEFT JOIN topics ON log.topicid = topics.id
> > > > WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> > > > AND users.username="testuser";
> > > >
> > > > When I run an 'explain' on it, I get:
> > > >
> > > >
> > >
> >
>
+++---+-+-+-+---
> > > > --++
> > > > | table | type | possible_keys | key | key_len | ref
|
> > rows
> > > > | Extra |
> > > >
> > >
> >
>
+++---+-+-+-+---
> > > > --++
> > > > | log| ALL| time | NULL|NULL | NULL
|
> > > > 1192384 | where used |
> > > > | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId
|
> > > > 1 | where used |
> > > > | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId
|
> > > > 1 ||
> > > > | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId
|
> > > > 1 ||
> > > >
> > >
> >
>
+++---+-+-+-+---
> > > > --++
> > > > 4 rows in set (0.01 sec)
> > > >
> > > > Now.. If I exclude the 'users.username="testuser"' and substitute it
> for
> > > the
> > > > userid that I got in a previous query (i.e. userid=2) so that the
> query
> > > > becomes:
> > > >
> > > > 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.id
> > > > LEFT JOIN boards ON log.boardid=boards.id
> > > > LEFT JOIN topics ON log.topicid = topics.id
> > > > WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> > > > AND log.userid=2;
> > > >
> > > >
> > > > and run an explain, I get...
> > > >
> > > >
> > >
> >
>
+++---+-+---
Re: Optimizing query (2nd attempt)
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 (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 displayed
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
> 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 on username.
> >
> > - Original Message -
> > From: "David Wolf" <[EMAIL PROTECTED]>
> > To: "Tore Van Grembergen" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > 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" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > > Sent: Sunday, October 28, 2001 8:46 AM
> > > Subject: Re: Optimizing query (2nd attempt)
> > >
> > >
> > > > 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(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.id
> > > > > LEFT JOIN boards ON log.boardid=boards.id
> > > > > LEFT JOIN topics ON log.topicid = topics.id
> > > > > WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> > > > > AND users.username="testuser";
> > > > >
> > > > > When I run an 'explain' on it, I get:
> > > > >
> > > > >
> > > >
> > >
> >
>
+++---+-+-+-+---
> > > > > --++
> > > > > | table | type | possible_keys | key | key_len | ref
> |
> > > rows
> > > > > | Extra |
> > > > >
> > > >
> > >
> >
>
+++---+-+-+-+---
> > > > > --++
> > > > > | log| ALL| time | NULL|NULL | NULL
> |
> > > > > 1192384 | where used |
> > > > > | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId
> |
> > > > > 1 | where used |
> > > > > | boards | eq_ref | PRIMARY | PRIMARY | 4 |
log.boardId
> |
> > > > > 1 ||
> > > > > | topics | eq_ref | PRIMARY | PRIMARY | 4 |
log.topicId
> |
> > > > > 1 ||
> > > > >
> > > >
> > >
> >
>
+++---+-+-+-+---
> > > > > --++
> > > > > 4 rows in set (0.01 sec)
> > > > >
> > > > > Now.. If I exclude the 'users.username="testuser"' and substitute
it
> > for
> > > > the
> > > > > userid that I got in a previous query (i.e. userid=2) so that the
> > query
> > > > > becomes:
> > > > &g
Re: Optimizing query (2nd attempt)
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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
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 that users.username to return the
>users.id=2, and to search using the indexed log.userid=2
MySQL can only use one index on a table at a time. It also uses the columns in
the order in which they are defined. ORDER MATTERS!
The manual does not seem to cover this, but at least Informix will stop using
an index when an inequality is hit. E.g.: if you have an index on (a, b, c )
and the query has WHERE a=5 AND b>2 AND c=10, the only part of the index that
will be used is (a, b). (Monty & co, is this true with MySQL? Can you add
something to the manual either way?)
So assuming this is true in your where clause:
> WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> AND users.username="testuser";
If you have an index on ( logTime, username), since you have an inequality for
lotTime in the query, username will NOT be used. However if you have the index
on (username, logTime) --or even just the first 10 chars or so of username +
logTime-- then both username AND logTime will be used 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 query (2nd attempt)
> >
> >
> > > 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.id
> > > LEFT JOIN boards ON log.boardid=boards.id
> > > LEFT JOIN topics ON log.topicid = topics.id
> > > WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> > > AND users.username="testuser";
> > >
> > > When I run an 'explain' on it, I get:
> > >
> > >
> >
> +++---+-+-+-+---
> > > --++
> > > | table | type | possible_keys | key | key_len | ref |
> rows
> > > | Extra |
> > >
> >
> +++---+-+-+-+---
> > > --++
> > > | log| ALL| time | NULL|NULL | NULL|
> > > 1192384 | where used |
> > > | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId |
> > > 1 | where used |
> > > | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId |
> > > 1 ||
> > > | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId |
> > > 1 ||
> > >
> >
> +++---+-+-+-+---
> > > --++
> > > 4 rows in set (0.01 sec)
> > >
> > > Now.. If I exclude the 'users.username="testuser"' and substitute it for
> > the
> > > userid that I got in a previous query (i.e. userid=2) so that the query
> > > becomes:
> > >
> > > 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.id
> > > LEFT JOIN boards ON log.boardid=boards.id
> > > LEFT JOIN topics ON log.topicid = topics.id
> > > WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> > > AND log.userid=2;
> > >
> > >
> > > and run an explain, I get...
> > >
> > >
> >
> +++---+-+-+-+---
> > > ++
> > > | table | type | possible_keys | key | key_len | ref |
> rows
> > > | Extra |
> > >
> >
> +++---+-+-+-+---
> > > ++
> > > | log| ref| time,userid | userid | 4 | const |
> > 27198
> > > | where used |
> > > | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId |
> > 1
> > > ||
> > > | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId |
> > 1
> > > ||
> > > | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId |
> > 1
> > > ||
> > >
> >
> +++---+-+-+-+---
> > > ++
> > > 4 rows in set (0.00 sec)
> > >
> > > Big difference from 1.19million rows to 27198 rows... My question is
> this.
> > > How can I optimize the query with the left joins so that the optimizer
> > will
> > > first grab the userid from the username and then use the userid index on
> > log
> > > to return the results fast?
> > >
> > > Thanks in adva
Re: Optimizing query (2nd attempt)
Maybe I'm missing something here--I don't know of a way to create an index
on TWO tables at once? Also, when I do:
EXPLAIN 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.id
LEFT JOIN boards ON log.boardid=boards.id
LEFT JOIN topics ON log.topicid = topics.id
WHERE users.username="testuser";
(users.username is indexed), I get the following:
+++---+-+-+-+---
--++
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+++---+-+-+-+---
--++
| log| ALL| NULL | NULL|NULL | NULL|
1199187 ||
| users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId |
1 | where used |
| boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId |
1 ||
| topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId |
1 ||
+++---+-+-+-+---
--++
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: "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
> >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 only use one index on a table at a time. It also uses the
columns in
> the order in which they are defined. ORDER MATTERS!
>
> The manual does not seem to cover this, but at least Informix will stop
using
> an index when an inequality is hit. E.g.: if you have an index on (a, b,
c )
> and the query has WHERE a=5 AND b>2 AND c=10, the only part of the index
that
> will be used is (a, b). (Monty & co, is this true with MySQL? Can you add
> something to the manual either way?)
>
> So assuming this is true in your where clause:
>
> > WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> > AND users.username="testuser";
>
> If you have an index on ( logTime, username), since you have an inequality
for
> lotTime in the query, username will NOT be used. However if you have the
index
> on (username, logTime) --or even just the first 10 chars or so of
username +
> logTime-- then both username AND logTime will be used 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.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimizing query (2nd attempt)
David Wolf wrote:
> Maybe I'm missing something here--I don't know
> of a way to create an index
> on TWO tables at once? Also, when I do:
My bad. You cant.
Since you are joining on the primary key, you want
to add an index like:
ALTER TABLE users ADD UNIQUE ( id, username(10));
Since you are using a left-join and a criteria on
a table that is left-joined, it can be difficult
to get a good index.
b.
(Sorry to spam you for a second time, David)
>
>
> EXPLAIN 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.id
> LEFT JOIN boards ON log.boardid=boards.id
> LEFT JOIN topics ON log.topicid = topics.id
> WHERE users.username="testuser";
>
> (users.username is indexed), I get the
> following:
>
> ++
> ---+---+-+-+-+---
>
> --++
> | table | type | possible_keys | key |
> key_len | ref | rows
> | Extra |
> ++
> ---+---+-+-+-+---
>
> --++
> | log| ALL| NULL | NULL|
> NULL | NULL|
> 1199187 ||
> | users | eq_ref | PRIMARY | PRIMARY
> | 4 | log.userId |
> 1 | where used |
> | boards | eq_ref | PRIMARY | PRIMARY
> | 4 | log.boardId |
> 1 ||
> | topics | eq_ref | PRIMARY | PRIMARY
> | 4 | log.topicId |
> 1 ||
> ++--
> -+---+-+-+-+---
>
> --++
> 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: "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
> > >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 only use one index on a table at a
> time. It also uses the
> columns in
> > the order in which they are defined. ORDER
> MATTERS!
> >
> > The manual does not seem to cover this, but at
> least Informix will stop
> using
> > an index when an inequality is hit. E.g.: if
> you have an index on (a, b,
> c )
> > and the query has WHERE a=5 AND b>2 AND c=10,
> the only part of the index
> that
> > will be used is (a, b). (Monty & co, is this
> true with MySQL? Can you add
> > something to the manual either way?)
> >
> > So assuming this is true in your where clause:
>
> >
> > > WHERE log.logTime >
> UNIX_TIMESTAMP("2000-10-26 23:00:00")
> > > AND users.username="testuser";
> >
> > If you have an index on ( logTime, username),
> since you have an inequality
> for
> > lotTime in the query, username will NOT be
> used. However if you have the
> index
> > on (username, logTime) --or even just the
> first 10 chars or so of
> username +
> > logTime-- then both username AND logTime will
> be used 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.
--
Bill Adams
TriQuint Semiconductor
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimizing query "WHERE date>0"
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 attention. A value of zero indicates it is being ignored. There are times when *lots* of records DateTimeNext values will be zero. I want to find all records in the database that need attention today, so a typical query looks like: SELECT id,Name FROM tbl WHERE DateTimeNext>1126215680 When I EXPLAIN this query, I get the following: table type possible_keys key key_len ref rows Extra Site, ALL, NULL,NULL, NULL, NULL, 53587,Using where If I add an index for "DateTimeNext", the EXPLAIN shows: table type possible_keys key key_len ref rows Extra Site,ALL,DateTimeNext,NULL,NULL,NULL,53587,Using where It appears that the index does NO good in this query. Is there anything I can do to optimize this query? Alter the table to improve the query? Do anything to not scan the entire stinkin' table? Thank you, DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing query "WHERE date>0"
"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? Thanks DanB > 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 attention. A >>value of zero indicates it is being ignored. There are times when *lots* >>of records DateTimeNext values will be zero. >> >>I want to find all records in the database that need attention today, so a >>typical query looks like: >>SELECT id,Name FROM tbl WHERE DateTimeNext>1126215680 >> >>When I EXPLAIN this query, I get the following: >> table type possible_keys key key_len ref rows Extra >> Site, ALL, NULL,NULL, NULL, NULL, 53587,Using where >> >>If I add an index for "DateTimeNext", the EXPLAIN shows: >> table type possible_keys key key_len ref rows Extra >> Site,ALL,DateTimeNext,NULL,NULL,NULL,53587,Using where >> >>It appears that the index does NO good in this query. >>Is there anything I can do to optimize this query? Alter the table to >>improve the query? Do anything to not scan the entire stinkin' table? >> >>Thank you, >>DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing query "WHERE date>0"
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 interest? If so, what? Thanks DanB 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 attention. A value of zero indicates it is being ignored. There are times when *lots* of records DateTimeNext values will be zero. I want to find all records in the database that need attention today, so a typical query looks like: SELECT id,Name FROM tbl WHERE DateTimeNext>1126215680 When I EXPLAIN this query, I get the following: table type possible_keys key key_len ref rows Extra Site, ALL, NULL,NULL, NULL, NULL, 53587,Using where If I add an index for "DateTimeNext", the EXPLAIN shows: table type possible_keys key key_len ref rows Extra Site,ALL,DateTimeNext,NULL,NULL,NULL,53587,Using where It appears that the index does NO good in this query. Is there anything I can do to optimize this query? Alter the table to improve the query? Do anything to not scan the entire stinkin' table? Thank you, DanB You may want to take a look at this page: http://dev.mysql.com/doc/mysql/en/how-to-avoid-table-scan.html Another possibility would be to change your data structures so that you can use an equality, rather than a range scan. For example, make DateTimeNext into a "date" or "datetime" field (rather than an int), and then alter your SELECT statement to be SELECT id,Name FROM tbl WHERE DateTimeNext = DATE(NOW()); Hope that helps, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing query "WHERE date>0"
"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 currently 28.53% of the rows that have >> "DateTimeNext>1126215680" >> Does this mean something of interest? If so, what? >> >> Thanks >> DanB >> >> >> >>>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 attention. A value of zero indicates it is being ignored. There are times when *lots* of records DateTimeNext values will be zero. I want to find all records in the database that need attention today, so a typical query looks like: SELECT id,Name FROM tbl WHERE DateTimeNext>1126215680 When I EXPLAIN this query, I get the following: table type possible_keys key key_len ref rows Extra Site, ALL, NULL,NULL, NULL, NULL, 53587,Using where If I add an index for "DateTimeNext", the EXPLAIN shows: table type possible_keys key key_len ref rows Extra Site,ALL,DateTimeNext,NULL,NULL,NULL,53587,Using where It appears that the index does NO good in this query. Is there anything I can do to optimize this query? Alter the table to improve the query? Do anything to not scan the entire stinkin' table? Thank you, DanB >> > > You may want to take a look at this page: > http://dev.mysql.com/doc/mysql/en/how-to-avoid-table-scan.html > > Another possibility would be to change your data structures so that you > can use an equality, rather than a range scan. For example, make > DateTimeNext into a "date" or "datetime" field (rather than an int), and > then alter your SELECT statement to be > > SELECT id,Name FROM tbl WHERE DateTimeNext = DATE(NOW()); I did notice that if I use an "=" comparison, that it will use the index. Unfortunately, I need all records that are "after a given date", and every record has a different date, so I can't use an "=" comparison. It does seem strange that the = will use the index, but a < or > won't. Thanks for the idea. DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 than the order by. Joseph Koenig wrote: 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.releaseformat = 'DVD' AND vr.releasestatus = 'In Print' AND pc.categorylevel = '1' AND pc.categoryid = '21' ORDER BY pt.title LIMIT 0, 21 The indexes are as follows: Table vr: muzeid prelrefnum releaseformat releasestatus Table pt: muzeid titleakanum title subtitle muzeid, title The explain on the query shows that it is using a temporary table with filesort. Can anyone help me get the ORDER BY to use the pt.title index? Thanks, Joe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimizing Query to use Index in ORDER BY
The explain shows that it is using the muzeid key for the pt table, so yes it is using it for the join. The query takes 1.46 seconds with the GROUP BY and 0.01 without. I was hoping to find a way to speed with query up. Thanks, Joe > From: gerald_clark <[EMAIL PROTECTED]> > Date: 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 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. >> >> 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.releaseformat = 'DVD' >> AND vr.releasestatus = 'In Print' AND pc.categorylevel = '1' AND >> pc.categoryid = '21' ORDER BY pt.title LIMIT 0, 21 >> >> The indexes are as follows: >> >> Table vr: >> muzeid >> prelrefnum >> releaseformat >> releasestatus >> >> Table pt: >> muzeid >> titleakanum >> title >> subtitle >> muzeid, title >> >> The explain on the query shows that it is using a temporary table with >> filesort. Can anyone help me get the ORDER BY to use the pt.title index? >> Thanks, >> >> Joe >> >> >> - >> Before posting, please check: >> http://www.mysql.com/manual.php (the manual) >> http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail >> <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> >> >> >> > > > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
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 the key part is not used in the where clause. If I am correct, the optimizer has been tweaked in version 4.x. Forgive me if 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 the pt table, so yes it is using it for the join. The query takes 1.46 seconds with the GROUP BY and 0.01 without. I was hoping to find a way to speed with query up. Thanks, Joe > From: gerald_clark <[EMAIL PROTECTED]> > Date: 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 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. >> >> 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.releaseformat = 'DVD' >> AND vr.releasestatus = 'In Print' AND pc.categorylevel = '1' AND >> pc.categoryid = '21' ORDER BY pt.title LIMIT 0, 21 >> >> The indexes are as follows: >> >> Table vr: >> muzeid >> prelrefnum >> releaseformat >> releasestatus >> >> Table pt: >> muzeid >> titleakanum >> title >> subtitle >> muzeid, title >> >> The explain on the query shows that it is using a temporary table with >> filesort. Can anyone help me get the ORDER BY to use the pt.title index? >> Thanks, >> >> Joe >> >> >> - >> Before posting, please check: >> http://www.mysql.com/manual.php (the manual) >> http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail >> <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> >> >> >> > > > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimizing Query to use Index in ORDER BY
How stable is version 4.x? This database will be moved into production fairly shortly and I need to make sure the version of mysql is extremely stable. Thanks, Joe > From: Victor Pendleton <[EMAIL PROTECTED]> > Date: Wed, 23 Oct 2002 14:40:58 -0500 > To: "'Joseph Koenig '" <[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 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 the key part is not used in the where clause. If I am > correct, the optimizer has been tweaked in version 4.x. Forgive me if 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 the pt table, so > yes > it is using it for the join. The query takes 1.46 seconds with the GROUP > BY > and 0.01 without. I was hoping to find a way to speed with query up. > Thanks, > > Joe > >> From: gerald_clark <[EMAIL PROTECTED]> >> Date: 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 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. >>> >>> 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.releaseformat > = 'DVD' >>> AND vr.releasestatus = 'In Print' AND pc.categorylevel = '1' AND >>> pc.categoryid = '21' ORDER BY pt.title LIMIT 0, 21 >>> >>> The indexes are as follows: >>> >>> Table vr: >>> muzeid >>> prelrefnum >>> releaseformat >>> releasestatus >>> >>> Table pt: >>> muzeid >>> titleakanum >>> title >>> subtitle >>> muzeid, title >>> >>> The explain on the query shows that it is using a temporary table > with >>> filesort. Can anyone help me get the ORDER BY to use the pt.title > index? >>> Thanks, >>> >>> Joe >>> >>> >>> - >>> Before posting, please check: >>> http://www.mysql.com/manual.php (the manual) >>> http://lists.mysql.com/ (the list archive) >>> >>> To request this thread, e-mail <[EMAIL PROTECTED]> >>> To unsubscribe, e-mail >>> <[EMAIL PROTECTED]> >>> Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php >>> >>> >>> >>> >> >> >> >> - >> Before posting, please check: >> http://www.mysql.com/manual.php (the manual) >> http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail > <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> > > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
