[Last Call] Learn how to a build a cloud-first strategyRegister Now



Posted on 2005-05-12
Medium Priority
Last Modified: 2008-02-01
It seems that doing a LIMIT 1000 on a SELECT COUNT(*) query has no affect, is this expected behaviour?
Question by:thepadders
  • 3
  • 2

Author Comment

ID: 13989239
I instead did

SELECT table.primarykey and then did a count on the rows returned in PHP. Query went from 1.2 seconds (counting 100,000 rows which was not usefl) to 0.028 seconds counting a max of 1000 rows, all I need.

Accepted Solution

mpf1748 earned 2000 total points
ID: 13989588
Yes, I would think it would be expected behavior because SELECT Count(*) actually only returns 1 row. When you put a limit 1000 (or any number), MySQL only returns that many records.

Expert Comment

ID: 13989797
mpf1748 is right ... count(*) only returns one row, so LIMIT has no effect. If you are trying to determine how long it takes to select 1000 rows, try this:

SELECT primarykey FROM table LIMIT 1000

So ... is there still a question in there somewhere? Are you just trying to judge server response time by query size? And while counting, are you doing so in a loop, or using the mysql_num_rows() function?

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


Author Comment

ID: 13990032
I am running a search on data. I am a) returing the number of results and then b) returin a subset of those results to actually show. So the count(*) was to find how many total matched the query and afterwards I have another query that shows that page's results.

However, there is very few times a need to count beyond 1000. If someone has 1,000 results they likely need to run a new search, so what I wanted was to just count up to 1,000 and then stop counting.

It seems I answered my own question though, instead of a COUNT(*) i just return the primary key and use a LIMIT. This works perfectly and almost halves the time the entire page of 25 queries takes to run!

Thanks for explaning why count(*) wastn't working.

Expert Comment

ID: 13990840
I know you found your answer, but just some advice:

Do a single query, limiting it to 1000, or whatever, and then use mysql_num_rows($result) to check against the number of rows returned.  That way you only have to run one query ... it might be faster for you.


- Shane

Author Comment

ID: 13991265
The 2nd query does a lot of joins that are not relevant in the WHERE part, it is quicker to do a quick WHERE query to find matches and then another with complicated joins. The 2nd query is slowish, but thats unavoidable, it was this very slow count(*) query that was the problem. But fixed now :)

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question