I came up with this query now when i tried to create a view it failed to create

SELECT aDate FROM (
  SELECT @maxDate - INTERVAL (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) DAY aDate FROM
  (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
   SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
  (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
   SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
  (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
   SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
  (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
   SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d,
  (SELECT @minDate := DATE_FORMAT(SYSDATE(), '%Y-01,01'), @maxDate := DATE_FORMAT(DATE_ADD(SYSDATE(), INTERVAL 1 YEAR), '%Y-01-01')) e
) f
WHERE aDate BETWEEN @minDate AND @maxDate

the error is this

Error Code : 1349
View's SELECT contains a subquery in the FROM clause

now my possible solution is to create views for every subquery, but if i do that it will give a big impact to its performance.

so i am asking how i can accomplish this without create new views for its subquery.

I will appreciate for any help will come.

thank you.

Unfortunately this is a restriction:

Subqueries cannot be used in the FROM clause of a view.

But you could try with procedures, first you install the tables range_numbers and range_dates:

delimiter //
drop procedure if exists range_tables//
create procedure `range_tables`()
    language sql
    deterministic
    sql security definer
    BEGIN
    DROP TABLE IF EXISTS `range_numbers`;
    CREATE TABLE range_numbers (a int(1)) ENGINE=MyISAM;
    INSERT INTO range_numbers (a) values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
    DROP TABLE IF EXISTS `range_dates`;
    CREATE TABLE `range_dates`(`aDate` DATE not null) ENGINE=MyISAM;
    END//

call range_tables//

Then you install range_dates procedure:

drop procedure if exists range_dates//
create procedure `range_dates` ()
    language sql
    deterministic
    sql security definer
    comment 'dates'
    begin
    TRUNCATE `range_dates`;
    INSERT INTO `range_dates` (aDate) SELECT @aDate := (@maxDate - INTERVAL (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) DAY) FROM
      (SELECT a FROM range_numbers) a,
      (SELECT a FROM range_numbers) b,
      (SELECT a FROM range_numbers) c,
      (SELECT a FROM range_numbers) d,
      (SELECT @minDate := DATE_FORMAT(SYSDATE(), '%Y-01-01'), @maxDate := DATE_FORMAT(DATE_ADD(SYSDATE(), INTERVAL 1 YEAR), '%Y-01-01')) e;
    end//

delimiter ;

Go to populate the table range_dates:

call range_dates;

And finally you execute the query:

SELECT aDate FROM range_dates WHERE aDate BETWEEN @minDate AND @maxDate;

Since the tables are MyISAM you need to run only call range_dates; before of the query, this will set the variables and the new rows in range_dates table.

Disclaimer: I'm not sure it's the best solution, it's more a test.

is it safe to mix MyISAM with InnoDB? becuase i am using InnoDB write now.

BTW thank you for the responce.

Sure you can, you can also convert the CREATE statements to InnoDB or to Memory, it will work. The only problem comes if you decide to use TEMPORARY tables because:

You cannot refer to a TEMPORARY table more than once in the same query.

Which is what happens in range_dates procedure with range_numbers table.

do you know if there is a tool(free) for benchmarking for mysql query, becuase i want to try which is faster the solution you provide or i will create a views for each subquery.

There is an internal tool since MySQL 5.1.4, it is mysqlslap: http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html

From a terminal you can try something like:

mysqlslap -uUSERNAME -pPASSWORD -h localhost --create-schema="DATABASE NAME" --query="SELECT aDate FROM range_dates WHERE aDate BETWEEN @minDate AND @maxDate;" --concurrency=50 --iterations=200

An example of output:

Benchmark
Average number of seconds to run all queries: 0.098 seconds
Minimum number of seconds to run all queries: 0.094 seconds
Maximum number of seconds to run all queries: 0.164 seconds
Number of clients running queries: 50
Average number of queries per client: 1

While with MySQL 5.5+ there are more options: http://dev.mysql.com/doc/refman/5.5/en/optimize-benchmarking.html

Thank you very much for the helpp