Depending on the query, adding a limit clause can have a huge effect on performance. If you want only one row (or know for a fact that only one row can satisfy the query), and are not sure about how the internal optimizer will execute it (for example, WHERE clause not hitting an index and so forth), then you should definitely add a LIMIT clause.

As for optimized queries (using indexes on small tables) it probably won&#39;t matter much in performance, but again - if you are only interested in one row than add a LIMIT clause regardless. 

Limit can affect the performance of the query (see comments and the link below) and it also reduces the result set that is output by MySQL. For a query in which you expect a single result there is benefits. 

Moreover, limiting the result set can in fact speed the total query time as transferring large result sets use memory and potentially create temporary tables on disk. I mention this as I recently saw a application that did not use limit kill a server due to huge result sets and with limit in place the resource utilization dropped tremendously. 

Check this page for more specifics: [MySQL Documentation: LIMIT Optimization][1]


  [1]: http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html

**The answer, in short, is yes.** If you limit your result to 1, then even if you are &quot;expecting&quot; one result, the query will be faster because your database wont look through all your records. It will simply stop once it finds a record that matches your query.

If there is only 1 result coming back, then no, LIMIT will not make it any faster.   If there are a lot of results, and you only need the first result, and there is no GROUP or ORDER by statements then LIMIT will make it faster.

If you really only expect one single result, it really makes sense to append the LIMIT to your query. I don&#39;t know the inner workings of MySQL, but I&#39;m sure it won&#39;t gather a result set of 100&#39;000+ records just to truncate it back to 1 at the end..

What is the best way to return the whole number part of a decimal (in c#)? (This has to work for very large numbers that may not fit into an int). 

    GetIntPart(343564564.4342) &gt;&gt; 343564564
    GetIntPart(-323489.32) &gt;&gt; -323489
    GetIntPart(324) &gt;&gt; 324

The purpose of this is: I am inserting into a decimal (30,4) field in the db, and want to ensure that I do not try to insert a number than is too long for the field. Determining the length of the whole number part of the decimal is part of this operation.

Best way to get whole number part of a Decimal number

Given an hypothetical utility class that is used only in program setup:

    class MyUtils {
       private static MyObject myObject = new MyObject();
       /*package*/static boolean doStuff(Params... params) {
           // do stuff with myObject and params...
       }
    }

will myObject be garbage collected when it is no longer being used, or will it stick around for the life of the program?

Are static fields open for garbage collection?

When I add LIMIT 1 to a MySQL query, does it stop the search after it finds 1 result (thus making it faster) or does it still fetch all of the results and truncate at the end?

Does adding &#39;LIMIT 1&#39; to MySQL queries make them faster when you know there will only be 1 result?

<p>When I add LIMIT 1 to a MySQL query, does it stop the search after it finds 1 result (thus making it faster) or does it still fetch all of the results and truncate at the end?</p>


Im runing mySQL in a server where i need to drop tons of databases (after some testing with the server). All databases that i need to drop have the same prefix &quot;Whatever_&quot;.

After the prefix, the names are random. So you have your Whatever_something, Whatever_232, Whatever_blabla, .... , Whatever_imthelast.

I will be doing this job quite some times so i was wondering what would be the best way to do this?


**EDIT:**
I can use any kind of language or plug in for mysql... so we CAN do this in some ways. Right now, i asked the guy that is generating the databases to give me a .txt with each name in a line... so im coding a quick php that will take a file and delete all the databases in it, later i will try the % answer(if it works, it takes the correct answer for sure its the easier way). Anyway i would like to do this the easier way coz i wont be able to support this code(other guys will and you know... )


**edit 2:**
The use of a wildcard didnt work: #1008 - Can&#39;t drop database &#39;whatever_%&#39;; database doesn&#39;t exist 

Drop MySQL databases matching some wildcard?

I know it&#39;s generally a bad idea to do queries like this:

    SELECT * FROM `group_relations`

But when I just want the count, should I go for this query since that allows the table to change but still yields the same results.

    SELECT COUNT(*) FROM `group_relations`

Or the more specfic

    SELECT COUNT(`group_id`) FROM `group_relations`

I have a feeling the latter could potentially be faster, but are there any other things to consider?

**Update**: I am using InnoDB in this case, sorry for not being more specific.

Should I COUNT(*) or not?

I&#39;m setting up a database using phpMyAdmin. I have two tables (`foo` and `bar`), **indexed on their primary keys**. I am trying to create a relational table (`foo_bar`) between them, using their primary keys as foreign keys.

I created these tables as MyISAM, but have since changed all three to InnoDB, because I read that MyISAM doesn&#39;t support foreign keys. All `id` fields are `INT(11)`.

When I choose the `foo_bar` table, click the &quot;relation view&quot; link, and try to set the FK columns to be `database.foo.id` and `database.bar.id`, it says **&quot;No index defined!&quot;** beside each column.

What am I missing?

## Clarification/Update
For the sake of simplicity, I want to keep using phpMyAdmin. I am currently using XAMPP, which is easy enough to let me focus on the PHP/CSS/Javascript, and it comes with phpMyAdmin.

Also, although I haven&#39;t been able to set up explicit foreign keys yet, I do have a relational table and can perform joins like this:

    SELECT * 
    FROM foo 
    INNER JOIN foo_bar 
    ON foo.id = foo_bar.foo_id 
    INNER JOIN bar
    ON foo_bar.bar_id = bar.id;

It just makes me uncomfortable not to have the FKs explicitly defined in the database.

Setting up foreign keys in phpMyAdmin?

When doing an ALTER TABLE statement in MySQL, the whole table is read-locked (allowing concurrent reads, but prohibiting concurrent writes) for the duration of the statement. If it&#39;s a big table, INSERT or UPDATE statements could be blocked for a looooong time. Is there a way to do a &quot;hot alter&quot;, like adding a column in such a way that the table is still updatable throughout the process?

Mostly I&#39;m interested in a solution for MySQL but I&#39;d be interested in other RDBMS if MySQL can&#39;t do it.

To clarify, my purpose is simply to avoid downtime when a new feature that requires an extra table column is pushed to production. Any database schema *will* change over time, that&#39;s just a fact of life. I don&#39;t see why we should accept that these changes must inevitably result in downtime; that&#39;s just weak.

ALTER TABLE without locking the table?

Are indices *(indexes)* defined as UNIQUE case sensitive in MySQL?

Are UNIQUE indices case sensitive in MySQL?

I want to write a function that returns the nearest next power of 2 number. For example if my input is 789, the output should be 1024. Is there any way of achieving this without using any loops but just using some bitwise operators?

Rounding up to next power of 2

How much of a bottleneck is memory allocation/deallocation in typical real-world programs?  Answers from any type of program where performance typically matters are welcome.  Are decent implementations of malloc/free/garbage collection fast enough that it&#39;s only a bottleneck in a few corner cases, or would most performance-critical software benefit significantly from trying to keep the amount of memory allocations down or having a faster malloc/free/garbage collection implementation?

Note:  I&#39;m **not** talking about real-time stuff here.  By performance-critical, I mean stuff where throughput matters, but latency doesn&#39;t necessarily.

Edit:  Although I mention malloc, this question is **not** intended to be C/C++ specific.

Memory Allocation/Deallocation Bottleneck?

How do you do &quot;inline functions&quot; in C#? I don&#39;t think I understand the concept. Are they like anonymous methods? Like lambda functions?

**Note**: The answers almost entirely deal with the ability to [inline functions][1], i.e. &quot;a manual or compiler optimization that replaces a function call site with the body of the callee.&quot; If you are interested in [anonymous (a.k.a. lambda) functions][2], see [@jalf&#39;s answer][3] or https://stackoverflow.com/questions/1085875/what-is-this-lambda-everyone-keeps-speaking-of/1086347#1086347.


  [1]: http://en.wikipedia.org/wiki/Inline_expansion
  [2]: http://en.wikipedia.org/wiki/Anonymous_function
  [3]: https://stackoverflow.com/a/473813/116891

Inline functions in C#?

I need to perform a query 2.5 million times. This query generates some rows which I need to `AVG(column)` and then use this `AVG` to filter the table from all values below average. I then need to `INSERT` these filtered results into a table. 

The only way to do such a thing with reasonable efficiency, seems to be by creating a `TEMPORARY TABLE` for each query-postmaster python-thread. I am just hoping these `TEMPORARY TABLE`s will not be persisted to hard drive (at all) and will remain in memory (RAM), unless they are out of working memory, of course.

I would like to know if a TEMPORARY TABLE will incur disk writes (which would interfere with the INSERTS, i.e. slow to whole process down)

PostgreSQL temporary tables

I found [this question][1] about which languages optimize tail recursion. Why C# doesn&#39;t optimize tail recursion, whenever possible?

For a concrete case, why isn&#39;t this method optimized into a loop ([Visual&amp;nbsp;Studio&amp;nbsp;2008][2] 32-bit, if that matters)?:

    private static void Foo(int i)
    {
        if (i == 1000000)
            return;

        if (i % 100 == 0)
            Console.WriteLine(i);

        Foo(i+1);
    }

  [1]: https://stackoverflow.com/questions/340762/which-languages-support-tail-recursion-optimization
  [2]: http://en.wikipedia.org/wiki/Microsoft_Visual_Studio#Visual_Studio_2008


Why doesn&#39;t .NET/C# optimize for tail-call recursion?

Does it matter how many files I keep in a single directory? If so, how many files in a directory is too many, and what are the impacts of having too many files? (This is on a Linux server.)

Background: I have a photo album website, and every image uploaded is renamed to an 8-hex-digit id (say, a58f375c.jpg).  This is to avoid filename conflicts (if lots of &quot;IMG0001.JPG&quot; files are uploaded, for example).  The original filename and any useful metadata is stored in a database.  Right now, I have somewhere around 1500 files in the images directory.  This makes listing the files in the directory (through FTP or SSH client) take a few seconds.  But I can&#39;t see that it has any effect other than that.  In particular, there doesn&#39;t seem to be any impact on how quickly an image file is served to the user.

I&#39;ve thought about reducing the number of images by making 16 subdirectories: 0-9 and a-f.  Then I&#39;d move the images into the subdirectories based on what the first hex digit of the filename was.  But I&#39;m not sure that there&#39;s any reason to do so except for the occasional listing of the directory through FTP/SSH.

How many files can I put in a directory?

    from f in CUSTOMERS
    where depts.Contains(f.DEPT_ID)
    select f.NAME

`depts` is a list (`IEnumerable&lt;int&gt;`) of department ids

This query works fine until you pass a large list (say around 3000 dept ids) .. then I get this error:

&gt; The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

I changed my query to: 

    var dept_ids = string.Join(&quot; &quot;, depts.ToStringArray());
    from f in CUSTOMERS
    where dept_ids.IndexOf(Convert.ToString(f.DEPT_id)) != -1
    select f.NAME

using `IndexOf()` fixed the error but made the query slow. Is there any other way to solve this? thanks so much.

Hitting the 2100 parameter limit (SQL Server) when using Contains()

Those &quot;fine&quot; RFCs mandate from every RFC-client that they beware of not using more than 2 connections per host...

Microsoft implemented this in WebClient. I know that it can be turned off with 

App.config:
   
    &lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot; ?&gt; 
    &lt;configuration&gt; 
     &lt;system.net&gt; 
      &lt;connectionManagement&gt; 
       &lt;add address=&quot;*&quot; maxconnection=&quot;100&quot; /&gt; 
      &lt;/connectionManagement&gt; 
     &lt;/system.net&gt; 
    &lt;/configuration&gt; 

(found on http://social.msdn.microsoft.com/forums/en-US/netfxnetcom/thread/1f863f20-09f9-49a5-8eee-17a89b591007 )

But how can I do it programmatically?

Accordin to
http://msdn.microsoft.com/en-us/library/system.net.servicepointmanager.defaultconnectionlimit.aspx

&quot;Changing the DefaultConnectionLimit property has no effect on existing 
ServicePoint objects; it affects only ServicePoint objects that are 
initialized after the change. If the value of this property has not been 
set either directly or through configuration, the value defaults to the 
constant DefaultPersistentConnectionLimit.&quot;


I&#39;d like best to configure the limit when I instanciate the WebClient, but just removing this sad limitation programmatically at the start of my programm would be fine, too.


The server I access is not a regular webserver in the internet, but under my control and in the local lan. I want to do API-calls, but I don&#39;t use webservices or remoting

How can I programmatically remove the 2 connection limit in WebClient

I&#39;m trying to do something like :

    SELECT * FROM table LIMIT 10,20

or

    SELECT * FROM table LIMIT 10 OFFSET 10

but using SQL Server

The only [solution I found][1] looks like overkill:

    SELECT * FROM ( 
      SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases 
     ) a WHERE row &gt; 5 and row &lt;= 10

I also [found][2]:

    SELECT TOP 10 * FROM stuff; 

... but it&#39;s not what I want to do since I can&#39;t specify the starting limit.

Is there another way for me to do that ? 

Also, just curious, is there a reason why doesn&#39;t SQL Server support the `LIMIT` function or something similar? I don&#39;t want to be mean, but that really sounds like something a DBMS needs ... If it does, then I&#39;m sorry for being so ignorant! I&#39;ve been working with MySQL and SQL+ for the past 5 years so...


  [1]: http://blogs.msdn.com/sqlserver/archive/2006/10/25/limit-in-sql-server.aspx
  [2]: http://vorg.ca/626-the-MS-SQL-equivalent-to-MySQLs-limit-command

LIMIT 10..20 in SQL Server

I recently had a Linux process which “leaked” file descriptors: It opened them and didn&#39;t properly close some of them.

If I had monitored this, I could tell – in advance – that the process was reaching its limit.

Is there a nice, Bash or Python way to check the FD usage ratio for a given process in a Ubuntu Linux system?

**EDIT:**

I now know how to check how many open file descriptors are there; I only need to know **how many file descriptors are allowed for a process**. Some systems (like Amazon EC2) don&#39;t have the `/proc/pid/limits` file.

Content Type	Original Author	Original Content on Stackoverflow
Question	Logan Serman	View Question on Stackoverflow
Solution 1 - Mysql	Eran Galperin	View Answer on Stackoverflow
Solution 2 - Mysql	rjamestaylor	View Answer on Stackoverflow
Solution 3 - Mysql	Max Alexander Hanna	View Answer on Stackoverflow
Solution 4 - Mysql	Kris Erickson	View Answer on Stackoverflow
Solution 5 - Mysql	driAn	View Answer on Stackoverflow

Does adding 'LIMIT 1' to MySQL queries make them faster when you know there will only be 1 result?

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

Solution 5 - Mysql

Are static fields open for garbage collection?

Best way to get whole number part of a Decimal number

Attributions