Best of MyISAM and InnoDB Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
The gen_clust_index (clustered index) under the hood of InnoDB houses entries of primary keys along with rowids. What is interesting about the use of the gen_clust_index is the fact that any non-unique indexes you create will always have a corresponding rowid for the gen_clust_index of a table. Thus, there are always double index lookups, one for the secondary index and one for the gen_clust_index.

Any attempts to improve the layout of a table or primary key gets nullified because of the gen_clust_index, or at least marginal results at best.

EXAMPLE

Some people attempt to sort a MyISAM in PRIMARY KEY order. According to MySQL Database Design and Tuning, Page 236 Paragraph 7, under the subheading "Storing a Table in Index Order":


  If you frequently retrieve large ranges of indexed data from a table
  or consistently sort results on the same index key, you might want to
  consider running myisamchk with the --sort-records option. Doing so
  tell MySQL to sort the table's data in the same physical order as the
  index, and can help speed these kinds of operations. Alternatively,
  you can combine the ALTER TABLE statement with an ORDER BY a
  particular column option to achieve the same results.


Granted, this works and works effectively FOR MyISAM. You could perform ALTER TABLE ... ORDER BY col1,col2,...,coln against InnoDB where the columns may or may not be that of the PRIMARY KEY. This will not produce faster results for InnoDB because ... that's right ... you must consult the gen_clust_index each time.

Some people can make the table's row format FIXED using ALTER TABLE mydb.mytb ROW_FORMAT=Fixed; and can get a 20% increase in read performance without any other changes. This works and works effectively FOR MyISAM. This will not produce faster results for InnoDB because ... that's right ... you must consult the gen_clust_index each time.

You could perform the following on an InnoDB table named mydb.mytb:

CREATE TABLE mydb.mytc LIKE mydb.mytb;
INSERT INTO mydb.mytc SELECT * FROM mydb.mytb ORDER BY col1,col2,...coln;
ALTER TABLE mydb.mytb RENAME mydb.mytd;
ALTER TABLE mydb.mytc RENAME mydb.mytb;
DROP TABLE mydb.mytd;


This will put the table in rowid order in the gen_clust_index. This may produce marginal results for InnoDB at best because ... that's right ... you must consult the gen_clust_index each time.

Now, let's get a little ridiculous. There is a NoSQL interface to query (SELECT only) MyISAM and InnoDB called the HandlerSocket (formerly called HANLDER) interface. This gives you access to data which lets you bypass all SQL, ACID, and MVCC protocols. Although it is possible, IMHO WAY TOO COMPLICATED TO CODE AND MAINTAIN. AFAIK there is nothing in print stating whether the HandlerSocket interface interacts with the gen_clust_index or not.

In summary, there are many ways to skin a cat. In this case, you cannot get a hold of the cat (the gen_clust_index). I guess this is why MyISAM continues to exist for its read performance, its pliability in table ordering, table row format, and the tools in support of it. InnoDB is will remain designed around its ACID-compliant nature until some brave soul takes the InnoDB source code and transforms it into something that has the best of both MyISAM and InnoDB.