1

I have complex query which returns milions of row. I use pager like "results 1-20 of 1235416" and I select only the 20 rows per page to be quick. But the query "Select Count(ID) from " is very slow, because it resolves all the complex query even if is quite optimized. It would be fine for me to show to user:

result 1-20 of more than 1000... like the google.

How should the following query looks like?

Select Count(Id) but no more than 1000 from <complexQuery>.

It would be fine if it is compatible with mssql, mysql and sqlite.

Tomas Kubes
  • 23,880
  • 18
  • 111
  • 148

2 Answers2

1

You could do a "TOP 1000" (or its equivalent, depending on the type of database you're using) inside the ComplexQuery.

Eg:

SELECT COUNT(T.Id)
  FROM ( SELECT TOP 1000 Id FROM <ComplexQuery> ) as T

EDIT:

The above works in MsSQL (or TSQL). In MySql, this would be:

SELECT COUNT(T.Id)
  FROM ( SELECT Id FROM <ComplexQuery> LIMIT 1000 ) as T

I'm not sure, but I think the last one works for sqlite as well.

Tom Wuyts
  • 855
  • 1
  • 11
  • 27
  • Thank you, I have managed to make it run for mysql with this correction: http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias it means SELECT COUNT(Id) FROM ( SELECT Id FROM LIMIT 1000 ) as T. but even if I simplify the query as much as possible, I can't make it work on MSSQL, "SELECT COUNT(ID) FROM ( SELECT TOP 1000 ID FROM myTable)" give me error: "Incorrect syntax near ')'." – Tomas Kubes Aug 09 '13 at 11:35
  • Ah yes, you'll have to do the same fix for mssql. After the last ")", add eg "as T". I'll update my answer. – Tom Wuyts Aug 09 '13 at 12:55
0

MySQL 5.1 doesn't support the "but no more than" syntax.

Why not something like:

SELECT * from table LIMIT 0,20;

I am assuming that you really don't care about the count.

mjn
  • 63
  • 6
  • I really care about the count and I don't want to send 1000 items through the network. I like the solution from Tom Wuyts. – Tomas Kubes Sep 26 '13 at 19:29