creating views

[ZER09]

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.

[cereal]

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.

[ZER09]

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

BTW thank you for the responce.

[cereal]

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.

[ZER09]

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.

[cereal]

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

[ZER09]

Thank you very much for the helpp