From: Thomas Thomas
Date: November 25 2008 8:12pm
Subject: Optimizing query question, EXPLAIN SELECT ...
List-Archive: http://lists.mysql.com/mysql/215369
Message-Id: <111056830811251212i775cab7ax6236aff942df5fac@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; 
	boundary="----=_Part_14560_18770682.1227643979635"

------=_Part_14560_18770682.1227643979635
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 possible_keys key                  rows Extra
ref index_blockid index_blockid     2638 Using where; Using filesort

If I add an index(blockid,timestamp)
EXPLAIN will display the following:

type possible_keys                               key
       rows Extra
ref index_blockid,index_blockid_timestamp    index_blockid_timestamp    8248
Using where; Using index


The index(blockid,timestamp) avoid the filesort + returns the result from
index ! (Using where; Using index)
But why for the index(blockid) 2638 rows are returned and for a more
specific index(blockid,timestamp) 8248 rows are returned ?

Thank you for any answer !

------=_Part_14560_18770682.1227643979635--