Hi,
OK, did you just create key1 and CreatedTime? If you didn't have them
before and don't need them, you can remove them and just create this
index for your query (you can definitely replace key1 with this):
ALTER TABLE article
ADD INDEX (ClassID, Auditing, CreatedTime, ArticleID);
And then the query should only use the index for execution. Then you can
of course run the second query to get all columns you want:
SELECT * FROM article WHERE ArticleID IN (<Comma seperated list of
ArticleIDs from first query>) ORDER BY CreatedTime;
Hope that helps!
Matt
----- Original Message -----
From: "avenger"
Sent: Tuesday, October 28, 2003 1:37 AM
Subject: Re: Limit Optimization??
> It's my info publsih system....
>
> My table structure :
> #
> # Table structure for table 'article'
> #
> CREATE TABLE article (
> ArticleID int(11) NOT NULL auto_increment,
> ClassID int(11) NOT NULL default '0',
> Title varchar(100) NOT NULL default '',
> TitleState char(3) default '000',
> Digest text,
> Content mediumtext NOT NULL,
> Author varchar(20) default NULL,
> Today smallint(1) NOT NULL default '0',
> View int(6) NOT NULL default '0',
> Auditing smallint(1) default '0',
> PostUser varchar(20) default NULL,
> PostUserID int(11) NOT NULL default '0',
> AuditingUser varchar(20) default NULL,
> AuditingUserID int(11) NOT NULL default '0',
> CreatedTime int(11) default NULL,
> Template int(11) default NULL,
> PRIMARY KEY (ArticleID),
> KEY key1 (ClassID,Auditing,CreatedTime),
> KEY CreatedTime (CreatedTime),
> ) TYPE=MyISAM ;
>
> My query: "SELECT ArticleID FROM article WHERE ClassID = 101 AND
Auditing =
> 1 ORDER BY CreatedTime DESC LIMIT x , y"
>
> I allreday created the key1 and the CreatedTime key..... And it's even
> slowly now ... :(
>
> Avenger
>
> ""Matt W"" <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED]
> > Hi,
> >
> > That's good! :-)
> >
> > Are you saying that you need to have a WHERE clause on the first
"...
> > LIMIT 1500000, 20" query? Can you give an example of the full query,
> > with the WHERE etc.? We'll see which columns can or should be added
to
> > an index.
> >
> > Also, can you show which indexes are already on the table? The last
> > lines of "SHOW CREATE TABLE table;" with the KEY definitions is
fine.
> >
> >
> > Matt
> >
> >
> > ----- Original Message -----
> > From: "avenger"
> > Sent: Monday, October 27, 2003 10:24 PM
> > Subject: Re: Limit Optimization??
> >
> >
> > > good job!!
> > > it short my query time from 30 sec to 0.6 sec.
> > > IOW,now i can not use the 'where' & 'order by' clause in the
SELECT .
> > can i
> > > need more indexs ?
> > >
> > > thx Matt.
> > >
> > >
> > > ""Matt W"" <[EMAIL PROTECTED]> wrote
> > > [EMAIL PROTECTED]
> > > > Hi,
> > > >
> > > > Yes, MySQL stops searching for rows once the LIMIT is satisfied,
as
> > long
> > > > as filesort isn't used for an ORDER BY. But your LIMIT 1500000,
20
> > will
> > > > take much longer (assuming filesort isn't used) than, say, LIMIT
> > 1000,
> > > > 20. This is because it has to scan over 1.5M rows first. It's
not
> > really
> > > > possible to just "start" at row 1500000.
> > > >
> > > > If you're not joining another table or something where it first
> > needs to
> > > > know how many rows from the table match, you can do something
like
> > this
> > > > with 2 queries in your code. The first just scans the index
which is
> > > > much faster than scanning the data file.
> > > >
> > > > SELECT id FROM table ORDER BY id LIMIT 1500000, 20;
> > > >
> > > > Then take the first and last of those ids and run this query to
get
> > the
> > > > other columns:
> > > >
> > > > SELECT * FROM table WHERE id BETWEEN @low_id AND @high_id ORDER
BY
> > id;
> > > >
> > > >
> > > > Hope that helps.
> > > >
> > > >
> > > > Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
