Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Not being a full blooded member of the DBA clan, but more of a dev clan, I find myself trying to figure out some slow running queries.

The subject of my question however is not 'Why my queries are slow' , but one of wanting to know how the 'LIMIT' command in MySql works.

At the moment, I'm still trying to troubleshoot the server performance itself, so as a way of giving it a little SQL to chew on (Just to make it do some work) I'm using the following:

SELECT * FROM <table> LIMIT 10

The table I'm using is a live table with an excess of 50Mil rows in it, and as I'm only conducting tests at the moment my expectation is this will return the first 10 records off the top of the table, and would easily do so in under 10 minutes.

However...

I'm seeing this SQL still running after 30 mins or more, and appearing not to do anything.

What I want to clarify specifically is this:

Does MySql attempt to fetch the entire table from a DB before then just returning the first 10 rows

OR

Does MySql fetch rows as it receives them and stop after it counts that it's recieved 10 rows

Cheers Shawty

share|improve this question
5  
EXPLAIN SELECT * FROM <table> LIMIT 10 and post it here... –  Remus Rusanu Apr 3 at 11:17
1  
And is that <table> a real, base table or a view? Or some complex derived table? –  ypercube Apr 3 at 11:28
    
@ypercube I just put <table> in as a place holder, imagine any table name in there that you wish. –  shawty Apr 3 at 20:36
    
@remus 60 minutes later and the explain still hadn't finished so I canceled it. –  shawty Apr 3 at 20:37
1  
@shawty my question was not about what I imagine but what you run the actual query against. In my imagination, queries and EXPLAINs run in subzero time. Would this solve your problem? –  ypercube Apr 3 at 20:43

3 Answers 3

up vote 2 down vote accepted

If the query has not got any specific ordering, it returns the first 10 rows it receives. But if there is a "WHERE ..." or "ORDER BY ..." clause, it must first get the full resultset and then fetch the first 10 rows.

If a simple "SELECT..." with no "WHERE ..." or "ORDER BY ..." clause takes that long, I would probably suggest to ANALYZE the table http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html. Also you should check if this DB table has a primary key. If not, setting one would probably speed up the query.

share|improve this answer
    
It has an integer column in the first field that's an auto_increment primary key, and yea that's what I thought RE: it should only do a full table scan if "WHERE" or "Order by" etc was used. –  shawty Apr 3 at 20:39

mysql should optimize a query like your example and not do a full table scan just to retrieve 10 records. Adding an order by without an index before the limit 10 would perform a full table scan.

Are you sure that your "table" is really a table and not a view? You might be selecting from a slow view.

share|improve this answer
    
+1. The view suggestion is very good –  Alexandros Apr 3 at 10:49
    
Nope, absolutely sure it's a table, I created the schema in the first place, plus everything that I put against it that tells me things like types, say it's a table too. –  shawty Apr 3 at 20:38

I should have answered this sooner but it slipped my mind, so apologies for that.

Ok, well I'd like to give you all an answer tick, because there was some great answers, with alexanderos giving the actual answer I was speaking which was clarification on how limit itself actually worked.

The actual reason for the slow down was a server issue, in so much that it had been set up incorrectly by the cloud provider and really wasn't performing how it should have been.

How ever I've left that project behind now, but I have learned a few good things in the process on how to deal with a mis behaving server.

SO thanks all for your input, I'm marking alexanderos's answer as the most appropriate.

Shawty

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.