Queries returning large number of rows
I have a MySQL 4.1 database with one table, defined thus:
CREATE TABLE `sentences` (
`id` bigint(20) NOT NULL auto_increment,
`previous_entry` varchar(255) default NULL,
`current_entry` varchar(255) default NULL,
`next_entry` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `current_previous` (`current_entry`(31),`previous_entry`(31))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Very simple, as you can see. The table contains 10 million rows and will eventually contain a lot more. Selects on ID are effectively instantaneous, but where I definitely need more speed is on queries which return a large number of rows (or a COUNT thereof). I did a SHOW INDEX to see the cardinality and I get a figure of 5199196 showing for both subparts of the compound index, which would suggest a decent spread of values (although I'm not certain how relevant cardinality is with such a compound index).
The problem is that I need to do a lot of 'begins with' queries, such as:
SELECT COUNT(*) FROM sentences WHERE previous_entry LIKE 'whatever%'
So however many distinct values there are in the index as a whole, a part match is always likely to return lots of rows (sometimes 50,000 upwards in my testing). Such queries really seem to take a long time (and I've tried with MyISAM tables as well as InnoDB). Given the need to do execute queries on millions of rows which are often going to return large numbers of rows, I am obviously not getting the best out of indexes and I'm wondering how I can best optimize this. Have I run into an inherent quality of BTREE indexes which there's no getting around? Or should I be designing this differently?