database Partitioning

[Ammu]

hi friends,
I heard about Improving Database Performance with Partitioning. But I dont have a clear idea about that. what is the concept behind that and how to do that.

[Luuk]

One of the thing is that, if you have a lot of data,
and your queries are mostly resulting in data from last month,
than partitioning could be an improvement. Your database would only have to look in a smaller piece of your data to retrieve result.

And occasionally you are still able to find all old data.

You can read more on this here:

http://dev.mysql.com/tech-resources/articles/partitioning.html

[Ammu]

thank you @Luuk for your response.
I have gone thru that site.
I got an idea about that.
But I failed to run this code. Procedure is not created.

CREATE PROCEDURE load_part_tab()
    begin
      declare v int default 0;
              while v < 8000000
     do
      insert into part_tab
      values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
     set v = v + 1;
     end while;
   end

can you pls help me to sove this issue

error
MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

[univercel]

Print the SQL query and post it, you might have made small synatx mistake.

[Luuk]

[test]> delimiter //
[test]>
[test]>
[test]>     CREATE PROCEDURE load_part_tab()
    ->         begin
    ->           declare v int default 0;
    ->                   while v < 8000000
    ->          do
    ->           insert into part_tab
    ->           values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
    ->          set v = v + 1;
    ->          end while;
    ->        end
    ->
    -> //
Query OK, 0 rows affected (0.06 sec)

[test]> delimiter ;

hmm, seems ok, so let's execute it:

[test]> call load_part_tab();
ERROR 1146 (42S02): Table 'test.part_tab' doesn't exist
[test]>

i dont't have the table --> test OK

[Ammu]

@Luuk still not working. showing the same error.
I got this code from that reference site you have given to me. why its not working for me..

[Ammu]

hi now it works.... procedure created. thank you so much for your help @luuk

thank you @univercel

[Luuk]

@Ammu (and others!):
Please remember that it is of almost no use to say:
"showing the same error"

It's far better to copy/paste the exact error message here.

For example if you claim that you get an error when using the 'DIR' command, you should post someting like this:

C:\temp>dir *.notfound
 Volume in drive C is MyDrive
 Volume Serial Number is 1234-1234

 Directory of C:\temp

File Not Found

C:\temp>

(the answer to this is too Obvious to explain ;) )