MySQL – Why is LIMIT 0 Allowed?

MySQL

I've just tried running SELECT * FROM tbl LIMIT 0, which unsurprisingly, returns 0 results.

I could see an argument for making it be an error, because it will never return any result, but then again it isn't invalid like LIMIT 'HelloWorld'.

Are there any actual real-world use cases for LIMIT 0, or is it just allowed because there's no real reason to not to allow it?

Best Answer

Perhaps a matter of convenience

I once answered a post Table JOIN and CREATE new table extremely slow, mentioning how CREATE TABLE AS SELECT will create a table and then perform INSERT INTO SELECT to populate the table

There are ways to use LIMIT 0 to create a temp table without indexes

CREATE TABLE mytable SELECT * FROM othertable LIMIT 0;

In another post, MySQL Locks while CREATE TABLE AS SELECT, I mentioned under OPTION #2 using WHERE 1=2 as a way to create a blank temp table with no indexes

CREATE TABLE 1_temp_foo SELECT * FROM crm_companies WHERE 1=2;
ALTER TABLE 1_temp_foo ENGINE=InnoDB;

In terms of end result, using LIMIT 0 and WHERE 1=2 and even WHERE 0 are synonymous.

I agree with your assumption that it was allowed for no particular reason (because there's no real reason to not allow it, as you say), but it sure comes in handy for such occasions.

Related Solutions

Grant Table Privileges to a MySQL User Connecting from Any Host

When you execute GRANT SELECT ON store.catalog TO 'wordpress'@'%';, mysqld wants to insert a row into the grant table mysql.tables_priv. Here is mysql.tables_priv:

mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
       Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)

mysql>

Since you want to insert a row into mysql.table_priv where user='wordpress' and host='%', there has to exist a row in mysql.user where user='wordpress' and host='%'.

You also mentioned that you are using MySQL Workbench. You must be using 'root'@'localhost'. That would usually have all rights and a password.

If you want to just allow anonymous SELECT against that table, first run this:

GRANT USAGE ON *.* TO 'wordpress'@'%';

This will place wordpress@'%' into mysql.user. Afterwards, GRANT SELECT ON store.catalog TO 'wordpress'@'%' should run just fine.

You will have to see what other wordpress entries are in mysql.user. This should show what SQL GRANT commands you need:

SELECT CONCAT('GRANT SELECT ON store.catalog TO ',userhost,';') GrantCommand
FROM
(
    SELECT CONCAT('''',user,'''@''',host,'''') userhost
    FROM mysql.user WHERE user='wordpress'
) A;

Mysql – Understanding optimisation of complex SELECT queries

Why there are massive differences in performance between the queries?
(assuming that you have at least defined indexes for the columns used in the subquery criteria and in the joining conditions)

Basically because MySQL optimizer is not smart enough to figure out that all these queries are equivalent. So, it probably produces different execution plans for the different queries. If you do not have properly defined FOREIGN KEY constraints, the optimizer may be actually right, there is no guarantee that the queries return identical results.

So, what to do to increase performance and have a standard way of writing this type of query variants?

Several things that affect MySQL queries performance:

Do not use id IN (SELECT subquery) if you can avoid it. It's not very well optimized in most MySQL versions (see point 6 below). Use joins if you can.
Replace UNION with UNION ALL if that doesn't change the result set (you could do that in EG3 query).

Don't use implicit joins (with commas in the FROM clause and the joining condition in the WHERE clause). Use explicit JOIN syntax. As an example, your EG1 is actually the same as (this is not for performance but for consistence):

SELECT people.*
  FROM people
       LEFT JOIN (criterion1 SELECT) c1 ON people.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON people.id=c2.pid,
       JOIN (criterion3 SELECT) c3 ON people.id=c3.pid
 WHERE ( c1.pid IS NOT NULL OR c2.pid IS NOT NULL );

The above query has an OR condition that is relevant to 2 tables (and their joins to the people, so 3 tables actually). This is usually not very good perfomance-wise.

You can try rewriting using EXISTS. This will make your queries easier to write and it may help with performance, too:

SELECT people.*
  FROM people
 WHERE EXISTS 
         (criterion3 SELECT modified with `people.id = some_table.pid`)
   AND ( EXISTS 
           (criterion1 SELECT modified with `people.id = some_table.pid`)
      OR EXISTS 
           (criterion2 SELECT modified with `people.id = some_table.pid`) 
       ) ;

If your host allows it, try/test MariaDB (it's a MySQL fork-replacement) that has introduced several improvements in queries execution in its latest versions. The optimizer will be a bit smarter identifying equivalent queries and certainly smarter as it has some new algorithms implemented that affect queries will joins, subqueries among other things.

MySQL 5.6 has also a few improvements in the optimizer but it's not available yet as a stable release.

Best Answer

Related Solutions

Grant Table Privileges to a MySQL User Connecting from Any Host

Mysql – Understanding optimisation of complex SELECT queries

Related Question