0

I'm trying to make page number for my table and need to know row count.

for example my query is SELECT * FROM tbl and the result is 100rows.

I should limit query to 10rows for one page.

if I do that I cant get all rows count.

I want to know is there any different between limit with mysql our php in process speed.

please tell me if you have any idea.

mehran
  • 62
  • 1
  • 1
  • 9
  • The answer depends on your data, but in many cases, executing two SQL queries (one for COUNT, one for LIMIT) will be faster than retrieving and slicing the entire result set. Benchmarking is the way to determine the best solution for your situation. – George Cummins May 09 '13 at 20:11

4 Answers4

3

You shoud use LIMIT in your MySQL. Why?

*Because it will take you less time to fetch some amount of data and transmit it instead of getting everything.

*Because it will consume less memory.

*Because working with smaller arrays is generally faster.

As for me, I used PHP as a filter only when I could not perform the filtering in MySQL

Just to update my answer, as Joachim Isaksson already posted:

SQL_CALC_FOUND_ROWS will help you count all the rows, so you can perform a correct pagination.

sybear
  • 7,837
  • 1
  • 22
  • 38
  • 1
    That's the idea. For any table of non-trivial size, you should be fetching with a `LIMIT` clause. It is impractical to do otherwise. On a table with millions of rows, your application might exhaust all system memory and crash the system. – tadman May 09 '13 at 20:17
2

The best way is probably to use FOUND_ROWS() and combine them both;

SELECT SQL_CALC_FOUND_ROWS * FROM tbl LIMIT 10

...to get the first 10 rows as a result. Then you can just do;

SELECT FOUND_ROWS()

...to get the total number of rows you would have got if you hadn't used LIMIT.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
0

What happens if you will have 100k rows? Of course, you will get rows count, but performance significantly decrease (and memory usage significantly increase). Just use second query to obtain amount of rows and limit rows in mysql

Maxim
  • 1,209
  • 15
  • 28
0

As i understand your problem ,solution might be like this :Strictly by mysql

  • Use two function first for counting total record
  • second for getting result
  • select count(1) as total from table_name
  • select col1,col2.. from table_name limit $start , $slot_size

Now slot_size is number of record you want to show on page,say 10 start is value which will change according to page

Notepad
  • 1,659
  • 1
  • 12
  • 14