Mysql count not working?

Question

I have a query

SELECT 
count(*) as count 
from matches 
WHERE team1 = 9 
ORDER BY data DESC, id ASC LIMIT 10

The max result I should expect isn't supposed to be 10? I am getting 15 as result, am I doing something wrong?

Answer 1

You need to do your limit in an inner select.

select count(*) from
(select * from matches WHERE team1 = 9 ORDER BY data DESC, id ASC LIMIT 10) ten_rows

sql fiddle

Answer 2

The LIMIT 10 clause applies after the SELECT ... part of the query. So it's counting the rows and putting that number into a single row before applying LIMIT 10.

If you were using the API and asking it how many rows there were in a resultset, then you would indeed get the reply of 10 from the query SELECT * FROM ... LIMIT 10.