An optimization dilemma

[Anon]

I want to show the total number of records that matched a select query AND only records n to n+m to my user.

The way I do it now in MySQL is via two queries:

Query1: select count (*) from table 1 where <condition>;

Query2: select * from table1 where <condition> limit n, n+m;

The alternative to the above two query solution is to pull all records, without using limit keyword and then script out only the records that I want to show by looping🙁. The drawback of this method is that i am forced to pull out all the data (consumes my memory) and then I loop through the data (something that MySQL can do much better than me).

Is there a better alternative??

Thanks in advance,
Vik

[brandonschnell]

I'd stay with how you're doing this now. the only change i'd make is to change count() to use a single column with an index.
Also don't use SELECT , instead specify the column order.

[crislewis]

If you merely want to cut down the db queries you can do it using:

select * from table 1 where <condition>;

Then mysql_numrows will give you the number of records and you can use mysql_data_seek() to move to row n and then loop through for m rows.

Cheers,

Cris

p.s. I agree with Brandon - using * in a select is lazy. You rarely need all fields and hardly ever display data in the table set order.