Problem with MySQL query speed

[mcavic]

I'm working with a 4 million row MyISAM table in MySQL 5.0 under Fedora, indexed on the non-unique integer mycolumn. The following query returns immediately:

select * from mytable
order by mycolumn desc
limit 0, 10;

But the following takes several seconds before it starts giving results:

select * from mytable;

So it seems that MySQL is buffering the results internally before giving them. Is there any way around this? It's slowing down a routine I'm running where I'm repeatedly running different select statements and only actually reading a fraction of the number of rows returned by the statements.

Maybe the problem is that the server has to complete the query as quickly as possible so that it's freed up for other queries.

Thanks!

[FalseDawn]

If you are doing this:

I'm repeatedly running different select statements and only actually reading a fraction of the number of rows returned by the statements.

Then I can't see why you would ever need to issue this:

select * from mytable;

_especially_ on such a large table.

You need to include the row limiting criteria in your SQL statement, otherwise, yes - the whole table will probably be scanned and buffered before transfer starts.

[mcavic]

I can't see why you would ever need to issue this:

I'm not, it was just an example to show that the problem was the amount of data being returned, not the selection criteria.

But I knew if I posted I'd think of a solution. I was able to modify the routine so it only requests the data that it needs to see, which is a small number of rows. It flies now.

Thanks!