What is involved in opening the MYD file? puzzled..
Orignally posted here
http://forums.mysql.com/read.php?24,107160,107160#msg-107160 recommended to this forum for an answer.
I will try to summarise as best I can:
MyIsam table with 450k rows, mostly varchars and one blob. Several indexes with an auto increment primary key.
Count(*) from table1 where x=1, y=2, z=3 and a like '%something%'; comsistently takes <3 seconds over a range of values.
Select(*) from table1 where x=1, y=2, z=3 and a like '%something%'; can take anything from 58 seconds to 300 seconds.
My question is this:
I know that "count" is using an index, but both queries have to run the "where" clause, so the time difference (as indicated in the other thread), I can only assume, is due to "select" having to open the MYD file to retrieve the rows? the "select *" must(?) know the primary key in order to actually retrieve the rows, and as the primary key is indexed, then why should this take so long?
Really confused by this.
To add to this confusion, if zero rows are returned, i.e. count(*) =0, (0.5 seconds) then the select STILL takes 140 seconds - so I would imagine the select is not opening the MYD file in this instance at all? In this zero rows instance, and I am repeating myself here I suppose, the count and select have to execute the where clause, errr, find no rows.....dont get it.
One final note, this table has 17 indexes (!!!) would this have any impact?
Edited 1 time(s). Last edit at 08/09/2006 01:20AM by John .