MySQL Syntax Explanation

mysql syntax explanation

If you look at the FROM clause, you see this:

FROM #__k2_items as a

And in the LEFT JOIN clause, you see this:

LEFT JOIN #__k2_categories c ON c.id = a.catid

That aliases the #__k2_items table (whatever it's really called) to the name a, and #__k2_categories to c, respectively. In this case it's just to save typing and improve the readability of the query, really.

The dot associates a column name to a table name so MySQL knows which tables to look in, in case you have columns of the same name in more than one table involved in the query. That is, it resolves column ambiguity.

explain command in mysql

Basically explain is used to give you information regarding how the database goes about getting data using a query you specified. Typically you would use it if you have a slow query that you want to analyze.

As far as I know, explains really only apply to statements that are doing data retrieval. So, assuming the table in your create statement exists, a better example would be...

explain select * from user where user='steve'

What you'll get back from this is a table containing some information on how the data was retrieved, not the data itself. In the real world you would probably only use explains with much more complicated queries.

You should try Googling "mysql explain", it turns up some pretty good results that explain the data you will get back when you run an explain query. For example, the information here seems pretty good.

Running EXPLAIN on MySQL query is giving a syntax error

You must semicolon the end of the sql command.

Change to

drop table sp_weights_holding_table;
CREATE TABLE sp_weights_holding_table engine=myisam;
EXPLAIN SELECT 'K' AS STAT,
....

Explain Vs Desc anomalies in mysql

• Explain will give you more
  information about a query,
• describe will give you more
  information about tables or columns.

You can also use EXPLAIN on a table name, in which case it will behave exactly like DESCRIBE.

EXPLAIN SELECT * 
FROM `customer`

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra 
1 SIMPLE customer ALL NULL NULL NULL NULL 2 

vs.

DESCRIBE `customer`
Field  Type  Null  Key  Default  Extra 
CustomerID varchar(2) NO      
Cx varchar(3) NO   

Trying to understand EXPLAIN results of query in mySQL

Here is the fiddle for you: http://sqlfiddle.com/#!2/a6224/2/0

As @Daniel already said, MySQL takes into account not only indices, but also the number of rows in each table. The number of rows is low both in my fiddle and in your database - so it is hard to blame MySQL.

Note that even though STRAIGHT_JOIN will make the order of joins seem logical to you, it will not however make the execution plan prettier (I mean Using temporary; Using filesort red flags)

MySQL explain Query understanding

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't able to use an index for sorting and therefore can'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.

Explain plan MySQL on empty database

It depends on the query, but yes, I often recommend that you need to fill the tables with some sample data to get accurate EXPLAIN reports.

Even if you fill with 1 row so you avoid the "impossible where" note, that's not good enough because a table with very few rows is likely to be treated specially by the optimizer. That is, the optimizer knows that such a small table will fit on a single page of storage anyway, and the minimum unit the storage engine will retrieve is one page, so it may do a "table scan" instead of using an index, whereas it would be important to use an index for the same query if the table had a few thousand rows.

You don't necessarily need to fill the table with real, sensitive data. It's common to write a script to generate meaningless data that is similar to your real data, enough to fill the tables so you can test.

You don't necessarily need to fill 100k rows of data to get EXPLAIN to show you the same optimizer plan it would use for that much data. It's good enough if you can fill the tables with a few hundred or a few thousand rows of artificial data.

explain the USING command

USING is a variation of the ON keyword in join syntax. This link explains it in detail. In the example, the query

SELECT C.First_Name, C.Last_Name, O.title
FROM Employee AS C
LEFT JOIN job as O USING (ID);

is identical to

SELECT C.First_Name, C.Last_Name, O.title
FROM Employee AS C
LEFT JOIN job as O ON C.ID = O.ID;

explain command usage

Key - The key column indicates the key
(index) that MySQL actually decided to
use. The key is NULL if no index was
chosen. To force MySQL to use or
ignore an index listed in the
possible_keys column, use FORCE
INDEX, USE INDEX, or IGNORE INDEX in
your query

From your query result we can see that you have no Index defined for your table because possible_keys column is also NULL.

A database index is a data structure
that improves the speed of data
retrieval operations on a database
table at the cost of slower writes and
increased storage space.

Take a look at this page to see the syntax used to create an index in MySQL:

CREATE INDEX Syntax

This page details each column of the explain plan:

Looking at the MySQL Explain Plan

---

Related Topics