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 > > > > > > > > > ----- Original Message ----- > > > From: "avenger" > > > Sent: Monday, October 27, 2003 7:57 PM > > > Subject: Limit Optimization?? > > > > > > > > > > Does mysql do any optimization for then one use > > > > > > > > `select ... limit x,y`? > > > > > > > > For example, I have table with 2000000 records and want to do page > web > > > > interface to this table. > > > > > > > > When i use `select ... from table limit 1500000, 20 `, it will > need > > > more and > > > > more times (on my here is more than 60 sec). > > > > > > > > well,that is sooooo slowly for the web interface. can any case > make it > > > > quickly.... > > > > > > > > help,plz. Thx for all.... > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
