mytable is an InnoDB table with
(a, b) as the primary key.
Does this query check all table?
Select * from mytable where ( b='2' and c='3')
I want to find out if 'cluster' structure helps this query not check all table. (when I don't have all part of primary key in my where clause)
Would it be faster if I used MyISAM and a secondary index? Because In full table scan, It seems that Innodb caches a page for every record and so its not fast. I've read about designing composite primary keys in an article: ".....For the sake of example let’s assume two rows were added for a user whose user_id is 50 and they have been assigned with id=10000000 and id=10001000, where id is the primary key in this table. What sort of work the following query has to perform in order to return results?
SELECT * FROM messages WHERE user_id = 50
It finds index entires where user_id is 50. With the primary key values from the secondary index, it can then locate the physical rows. So InnoDB first seeks a row where id is 10000000. But since it does not operate on individual rows at this point, but rather on the blocks of records called pages, it locates the proper page on disk and caches it in the buffer pool. Only then it can extract the actual row, return it, and move on to the next one where id is 10001000. Since the distance between the two records is significant, it turns out that it is not on the same page that was just pulled into the cache and which holds the first row. InnoDB has to go back to disk to fetch another page, cache it, extract the row and return it.
In the above example at least two I/Os were issued. In a pessimistic case for a few hundred matching rows, when a user has that many messages, that could result in a few hundred I/O operations. Random I/O. Assuming as little as a few milliseconds for each operation, the query may need as much as one second to execute. It would use index properly, it would only pull a few hundred rows, but would still perform poorly. With the exception for flash technology any typical storage is rather slow, and also very difficult to scale, so such scenario should be avoided. ......"