Translate MSSQL to MySQL - Delete Top 10 Percent If Table Too Large?

Here's the MSSQL code I've been using successfully. Need MySQL equivalent. It  of records if a db table grow too large. Help...

Declare @left int
Declare @MBSize float
Select @left =100-(sum(size)* CONVERT(float,8)/1024)*100/(4096), @MBSize=(sum(size)* CONVERT(float,8)/1024)from sys.database_files
if @left<=10
Begin
Delete top (10) percent From tbl_Backups;
DBCC shrinkdatabase(MyDatabaseName, 10)
End
pointemanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Here are some handy MySQl scripts I came across last year : http://www.novell.com/communities/node/8706/check-mysql-database-size-using-sql-query  to help determine various sizes. Basically use the information_schemas as your data source for size.

Essentially you will end up with something like :

SELECT table_schema "DB Name", sum( data_length + index_length ) / 1024 / 1024 "DB Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;

Is it just the size that is a problem ? Are you OK with the rest ?




Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pointemanAuthor Commented:
Yes, DB size correction is the issue. I need the complete MySQL equivalent above.
Mark WillsTopic AdvisorCommented:
Well to check free space you can do the similar thing as above except use the column data_free

SELECT table_schema "DB Name", sum( data_length + index_length ) / 1024 / 1024 "DB Size in MB",  sum( data_free )/ 1024 / 1024 "DB Free in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;


But, the reorganisation to release free space is NOT the same as ms sql server. You can use OPTIMISE to release space from a table... Have a look at : http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html

Apart from that, you basically have to go through a backup / restore process. have a look at : http://forums.mysql.com/read.php?35,121880,121886#msg-121886
And worthwhile following that link down the bottom for the mulitple-tablespaces.

So, it is not directly translated to the point of actually shrinking the physical file size. Sorry about that...


10 Holiday Gifts Perfect for Your Favorite Geeks

Still have some holiday shopping to do for the geeks in your life? While toys, clothing, games, and gift cards are still viable options for your friends and family, there’s more reason than ever to consider gadgets and software.

pointemanAuthor Commented:
Very good, I'll study the links and try the code tomorrow. Thank you...
pointemanAuthor Commented:
SELECT table_schema 'DB Name',sum( data_length + index_length )/1024/1024 'DB Size MB',
sum( data_free )/1024/1024 'Free Space MB' FROM information_schema.TABLES
where table_schema = 'mydbnamehere'
GROUP BY table_schema
My problem continues to be using this returned information in an 'if' statement like MSSQL does. MSSQL declairs the var @left then uses it to make a decision. So far you helped my with approx half the answer I need to continue. Thanks for all your help so far...
--> Declare @left int
Declare @MBSize float
Select @left =100-(sum(size)* CONVERT(float,8)/1024)*100/(4096), @MBSize=(sum(size)* CONVERT(float,8)/1024)from sys.database_files
--> if @left<=10
Begin
Delete top (10) percent From tbl_Backups;
DBCC shrinkdatabase(MyDatabaseName, 10)
End
Mark WillsTopic AdvisorCommented:
OK,

You can use IF statement, so long as you wrap it up inside a function or procedure....

There is a pretty good example, also shows declare, and the IF statement in : http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html

The delete statement uses the LIMIT clause and that is a row count not a percent, so, you will need to get the count(*) / 10 from your table to get 10 percent

DELETE FROM tbl_backups LIMIT row_count

You might want to wrap that up in a while statement...

Now there is an interesting strategy for deletes on really large tables. That is select into a smallertable of deletable rows, then use the "USING" clause - it is supposed to shrink the table at the same time.

Have a look at the example : DELETE FROM LargeTable USING LargeTable INNER JOIN TemporarySmallTable ON LargeTable.ID = TemporarySmallTable.ID;
down the bottom of the delete : http://dev.mysql.com/doc/refman/5.0/en/delete.html

pointemanAuthor Commented:
Still working on IF statement, I'm still unsure of MySQL syntax...
Mark WillsTopic AdvisorCommented:
Have to wrap it up in a stored procedure. That link above has a pretty good example.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.