How to count the number of rows when using SQL joins and group by
I have the following query:
SELECT a.HotelID,a.Hotelname,GROUP_CONCAT(DISTINCT b.OperatorName) AS Operators
FROM hotels AS a
INNER JOIN operators AS b
ON a.HotelID = b.HotelID
GROUP BY a.HotelID
ORDER BY a.HotelID
LIMIT 100
I need this query for a simple search function. The result 开发者_如何学CTable should contain Paging. So what I did was I runned this query (without LIMIT) to get the number of rows (which I need to calculate the pages and so on) and then I rerun that query with the LIMIT.
In fact the query itself takes 4-5sec (against 300k table, with indexes on all the fields) which means it currently takes 10sec to load because it runs two times.
I am wondering if there is a SQL Statement I can simply use to get the number of rows and which might be faster. I thought I can use COUNT(a.HotelID) but this not works.
give this a try:
SELECT *
FROM (
SELECT a.HotelID,a.Hotelname,GROUP_CONCAT(DISTINCT b.OperatorName) AS Operators, COUNT(a.HotelID) AS total
FROM hotels AS a
INNER JOIN operators AS b
ON a.HotelID = b.HotelID
GROUP BY a.HotelID
) AS a
ORDER BY a.HotelID
LIMIT 100
also, for the speed you should make sure your indexes are in order.
update
select count(*) from (
SELECT distinct b.HotelID
FROM hotels AS a
INNER JOIN operators AS b
ON a.HotelID = b.HotelID
)
can this be faster?
Clearly described in the manual:
SQL_CALC_FOUND_ROWS
tells MySQL to calculate how many rows there would be in the result set, disregarding anyLIMIT
clause. The number of rows can then be retrieved withSELECT FOUND_ROWS()
. See Section 11.13, “Information Functions”.
If you follow the link to Section 11.13, there's then an example:
FOUND_ROWS()
A
SELECT
statement may include aLIMIT
clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without theLIMIT
, but without running the statement again. To obtain this row count, include aSQL_CALC_FOUND_ROWS
option in theSELECT
statement, and then invokeFOUND_ROWS()
afterward:mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name -> WHERE id > 100 LIMIT 10; mysql> SELECT FOUND_ROWS();
The second
SELECT
returns a number indicating how many rows the firstSELECT
would have returned had it been written without theLIMIT
clause.In the absence of the
SQL_CALC_FOUND_ROWS
option in the most recent successful SELECT statement,FOUND_ROWS()
returns the number of rows in the result set returned by that statement. If the statement includes aLIMIT
clause,FOUND_ROWS()
returns the number of rows up to the limit. For example,FOUND_ROWS()
returns10
or60
, respectively, if the statement includesLIMIT 10
orLIMIT 50, 10
.
Please, use the documentation as your first port of call.
精彩评论