Using temporary means that MySQL need to use some temporary tables for storing intermediate data calculated when executing your query.

Using filesort is a sorting algorithm where MySQL isn&#39;t able to use an index for sorting and therefore can&#39;t do the complete sort in memory. Instead it breaks the sort into smaller chunks and then merge the results to get the final sorted data.

Please refer to http://dev.mysql.com/doc/refman/5.0/en/explain-output.html.

I think you might be using an ORDER BY plus some derived table or sub-query. It would be great if you could paste your query and relevant tables/indexes information and the EXPLAIN output.

Syntax:     

    Explain `MySQL Query`

**Example:** `EXPLAIN SELECT * FROM categoriesG`

**Example:** `EXPLAIN EXTENDED SELECT City.Name FROM City
JOIN Country ON (City.CountryCode = Country.Code)
WHERE City.CountryCode = &#39;IND&#39; AND Country.Continent = &#39;Asia&#39;G`


Explain followed with your mysql query

&lt;a href=&quot;http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/&quot;&gt;Better explained in details here&lt;/a&gt;

I am looking into an Euler project. Specifically #18.  
To sum up, the idea is to find the max path from a triangle:  

       3
      7 4
     2 4 6  
    8 5 9 3

3 + 7 + 4 + 9 = 23.

Reading for this, most people indicate that this is solved correctly by working bottom to top instead of using an algorithm that works &quot;greedy&quot; from top to bottom.  

I can understand that starting from top and going down selecting the max you find is &quot;shortsighted&quot; and may not be the overall max.  

But why is the approach of going from bottom to top any better?  
It seems to me it suffers from the same problem.  

For example in the triangle in the example we would get (starting from bottom):  
9+6+4+3=22 &lt; 23  

So why start from bottom-up?

Euler project #18 approach

As we know the purpose of &quot;final&quot; keyword in java. While declaring a variable as final, we have to initialize the variable. like &quot;**final int a=10;&quot;**
   We can not change the value of &quot;a&quot;. But if we go for HashTable its possible to add some value even declaring the HashTable as final.
 
Example::

     private static final Hashtable&lt;String,Integer&gt; MYHASH = new Hashtable&lt;String,Integer&gt;() 
    {{     put(&quot;foo&quot;,      1);     
           put(&quot;bar&quot;,      256);     
           put(&quot;data&quot;,     3);     
           put(&quot;moredata&quot;, 27);     
           put(&quot;hello&quot;,    32);     
           put(&quot;world&quot;,    65536);  }}; 


Now I am declaring the MYHASH HashTable as final. If I try to add some more elements to this, its accepting.

    MYHASH.put(&quot;NEW DATA&quot;,      256);

Now the &quot;NEW DATA&quot; is added to the HashTable.
My questions is Why its allowing to add even its declaring as final????







Creating Hashtable as final in java

I&#39;ve read on some blogs and in some articles related to optimization, how to optimize queries. I read I need to use indexes and make sure all my primary key and foreign keys are set correctly using a good relational database schema.

Now I have a query I need to optimize and I get this on the `EXPLAIN`:

    Using where; Using temporary; Using filesort

I am using MySQL 5.5

I know I am using `WHERE` but not with my temporary table nor filesort? What does this mean? 



MySQL explain Query understanding

I've read on some blogs and in some articles related to optimization, how to optimize queries. I read I need to use indexes and make sure all my primary key and foreign keys are set correctly using a good relational database schema.
Now I have a query I need to optimize and I get this on the <code>EXPLAIN</code>:
<pre><code class="hljs language-sql">Using where; Using temporary; Using filesort
</code></pre>
I am using MySQL 5.5
I know I am using <code>WHERE</code> but not with my temporary table nor filesort? What does this mean?

I got a database server failure, says host is blocked because of many connection errors. It ask me to unblock with &#39;mysqladmin flush-hosts&#39;

how and where should I  run this command to our amazon rds database server?

thank you

how to run command &quot;mysqladmin flush-hosts&quot; on Amazon RDS database Server instance?

I have two tables `rooms` and `rooms facilities` and I have to select the rooms with desired facilities.

If I select a room with one facility (facility with id=4 - id_fu - ). using the following query Everything it&#39;s ok:

    SELECT u.* FROM rooms u 
    JOIN facilities_r fu 
    ON fu.id_uc = u.id_uc 
        AND fu.id_fu = &#39;4&#39; 
    WHERE 1 
        AND vizibility=&#39;1&#39; 
    GROUP BY id_uc 
    ORDER BY u_premium desc, id_uc DESC 

But if I want to select the room with more facilities, let&#39;s say facilities with id=4, and id=3 ..using the following query it doesn&#39;t work:

    SELECT u.* FROM room u 
    JOIN facilities_r fu 
    ON fu.id_uc=u.id_uc 
        AND fu.id_fu = &#39;4&#39; 
        AND fu.id_fu = &#39;3&#39; 
    WHERE 1 
        AND vizibility = &#39;1&#39; 
    GROUP BY id_uc 
    ORDER BY u_premium DESC, id_uc DESC 

I don&#39;t understand why it doesn&#39;t work, but I can&#39;t figure up how to put the condition.

Having issues with a MySQL Join that needs to meet multiple conditions

I&#39;m trying to connect to my DB in Ubuntu remotely but I receive error message when trying to `mysql -u root -p`:

&gt; Found option without preceding group in config file: /etc/mysql/my.cnf at line: 1

my.cnf looks like:

    [mysqld]
    user		= mysql
    socket		= /var/run/mysqld/mysqld.sock
    port		= 3306
    basedir		= /usr
    datadir		= /var/lib/mysql
    tmpdir		= /tmp
    bind-address		=  0.0.0.0
    key_buffer		= 16M
    max_allowed_packet	= 16M
    thread_stack		= 192K
    thread_cache_size       = 8
    myisam-recover         = BACKUP
    query_cache_limit	= 1M
    query_cache_size        = 16M
    log_error                = /var/log/mysql/error.log
    expire_logs_days	= 10
    max_binlog_size         = 100M
    
    [client]
    port		= 3306
    socket		= /var/run/mysqld/mysqld.sock
    
    [mysqld_safe]
    socket		= /var/run/mysqld/mysqld.sock
    nice		= 0
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet	= 16M
    
    [mysql]
    
    [isamchk]
    key_buffer		= 16M

MySQL my.cnf file - Found option without preceding group 

Alright so I have a project in NodeJS where I&#39;m utilizing Sequelize for a MySQL ORM. The thing works fantastically however I&#39;m trying to figure out if there is a way to specify what fields are being returned on a query basis or if there&#39;s even a way just to do a .query() somewhere. 

For example in our user database there can be ridiculous amounts of records and columns. In this case I need to return three columns only so it would be faster to get just those columns. However, Sequelize just queries the table for everything &quot;*&quot; to fulfill the full object model as much as possible. This is the functionality I&#39;d like to bypass in this particular area of the application.

Specifying specific fields with Sequelize (NodeJS) instead of *

How can I execute an SQL command through a shell script so that I can make it automated?

I want to restore data I have collected in a SQL file using a shell script. I want to connect to a server and restore data. The command works when executed separately via SSH command line.

This is the command I use:

    mysql -h &quot;server-name&quot; -u root &quot;password&quot; &quot;database-name&quot; &lt; &quot;filename.sql&quot;


This is the shell script code that creates the file `ds_fbids.sql` and pipes it into mysql.

    perl fb_apps_frm_fb.pl
    perl fb_new_spider.pl ds_fbids.txt ds_fbids.sql
    mysql -h dbservername -u username -ppassword dbname &lt; ds_fbids.sql

What is the correct way to do this?

How to execute a MySQL command from a shell script?

I have a SQL table that all of a sudden cannot return data unless I include `with (nolock)` on the end, which indicates some kind of lock left on my table.  

I&#39;ve experimented a bit with [sys.dm_tran_locks][1] to identify that there are in fact a number of locks on the table, but how do I identify *what* is locking them (ie the request element of the [sys.dm_tran_locks][1])?

EDIT: I know about [sp_lock][2] for pre SQL 2005, but now that that sp is deprecated, AFAIK the right way to do this is with [sys.dm_tran_locks][1].  I&#39;m using SQL Server 2008 R2.


  [1]: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-2017
  [2]: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql?view=sql-server-2017

How to find out what is locking my tables?

I have this cross-database query...

    SELECT
                `DM_Server`.`Jobs`.*,
                `DM_Server`.servers.Description AS server,
                digital_inventory.params,
                products.products_id,
                products.products_pdfupload,
                customers.customers_firstname,
                customers.customers_lastname
            FROM `DM_Server`.`Jobs`
            INNER JOIN `DM_Server`.servers ON servers.ServerID = Jobs.Jobs_ServerID
            JOIN `cpod_live`.`digital_inventory` ON digital_inventory.jobname = Jobs.Jobs_Name
            JOIN `cpod_live`.`products` ON products.products_pdfupload = CONCAT(digital_inventory.jobname, &quot;.pdf&quot;)
            JOIN `cpod_live`.`customers` ON customers.customers_id = products.cID
            ORDER BY `DM_Server`.`Jobs`.Jobs_StartTime DESC LIMIT 50

it runs fine until I make them `LEFT JOIN`s.  I thought that by not specifying a type of join it was assumed to be a `LEFT JOIN`.  Is this not the case?

Difference in MySQL JOIN vs LEFT JOIN

To quote the [docs][1]:

&gt; When creating an index, the number associated with a key specifies the
&gt; direction of the index, so it should always be 1 (ascending) or -1
&gt; (descending). Direction doesn&#39;t matter for single key indexes or for
&gt; random access retrieval but is important if you are doing sorts or
&gt; range queries on compound indexes.

However, I see no reason why direction of the index should matter on compound indexes. Can someone please provide a further explanation (or an example)?

  [1]: http://www.mongodb.org/display/DOCS/Indexes

Why does direction of index matter in MongoDB?

I have a table that has columns like this for example:

    id,col1,col2,col3,col4

Now, I want to check if `ANY` of `col1, col2, col3, col4` have the passed in value.

The long way to do it would be..

    SELECT * FROM table WHERE (col1 = 123 OR col2 = 123 OR col3 = 123 OR col4 = 123);

I guess it&#39;s the opposite version of `IN`.

Is there an easier way to do what I want?

Checking multiple columns for one value

I got a following table:


    col1 | col2 | col3
    -----+------+-------
    1    | a    | 5
    5    | d    | 3
    3    | k    | 7
    6    | o    | 2
    2    | 0    | 8

If a user searches for &quot;1&quot;, the program will look at the `col1` that has &quot;1&quot; then it will get a value in `col3` &quot;5&quot;, then the program will continue to search for &quot;5&quot; in `col1` and it will get &quot;3&quot; in `col3`, and so on. So it will print out:

    1   | a   | 5
    5   | d   | 3
    3   | k   | 7

If a user search for &quot;6&quot;, it will print out:

    6   | o   | 2
    2   | 0   | 8

How to build a `SELECT` query to do that?


Content Type	Original Author	Original Content on Stackoverflow
Question	Ken	View Question on Stackoverflow
Solution 1 - Mysql	Abhay	View Answer on Stackoverflow
Solution 2 - Mysql	Aditya P Bhatt	View Answer on Stackoverflow

MySQL explain Query understanding

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Creating Hashtable as final in java

Euler project #18 approach

Attributions