hrm.. but i tried
SELECT * FROM post ORDER BY postdate DESC LIMIT 771297, 30 postdate is not primary key but just an INDEX it still examined 771297 rows
From: Michael Stassen <[EMAIL PROTECTED]>
To: tinys xuefer <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
Subject: Re: what does Rows_examined mean exactly?
Date: Sun, 25 Jul 2004 10:32:49 -0400
MIME-Version: 1.0
Received: from out014.verizon.net ([206.46.170.46]) by mc4-f19.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Sun, 25 Jul 2004 07:32:49 -0700
Received: from verizon.net ([68.163.178.105]) by out014.verizon.net (InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP id <[EMAIL PROTECTED]>; Sun, 25 Jul 2004 09:32:49 -0500
X-Message-Info: JGTYoYF78jFocj+u73FHpy/MHvLpLYvD
Message-ID: <[EMAIL PROTECTED]>
User-Agent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; en-US; rv:1.4) Gecko/20030624 Netscape/7.1
X-Accept-Language: en-us, en
References: <[EMAIL PROTECTED]>
In-Reply-To: <[EMAIL PROTECTED]>
X-Authentication-Info: Submitted using SMTP AUTH at out014.verizon.net from [68.163.178.105] at Sun, 25 Jul 2004 09:32:49 -0500
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 25 Jul 2004 14:32:50.0020 (UTC) FILETIME=[43016240:01C47254]
tinys xuefer wrote:
slow.log:
# Query_time: 14 Lock_time: 0 Rows_sent: 30 Rows_examined: 771327
SELECT * FROM `post` LIMIT 771297, 30;
i dp have privmary key on table `post`
does 'Rows_examined: 771327' means mysqlserver read through those 771327 rows to get 30 rows?
it takes 14 seconds!
possible to show 'Rows_examined' in a explain or other commands? slow log is hard to debug..
and possible to optimize?
But you didn't use the primary key! In fact, you didn't ask for any order at all. Mysql does not try to guess that you meant to order by the primary key, it simpply does what you tell it. Your query, in effect, tells mysql to pick 771327 rows from post in any order and send you the last 30. Assuming your primary key column is named id, you need to change this query to
SELECT * FROM post ORDER BY id LIMIT 771297, 30;
With the explicit ORDER BY on the primary key, mysql will use the index to quickly find the 30 rows you want.
Michael
_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
