MySQL Forums
Forum List  »  Performance

Queries returning large number of rows
Posted by: Jan Hansen
Date: May 22, 2006 06:21AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Queries returning large number of rows
3980
May 22, 2006 06:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.