English
| Showing entries 1 to 4 |
Traditionally, the MySQL storage engine interface has taken a
minimalistic approach to data definition language. The only natively
supported operations were CREATE TABLE, DROP
TABLE and RENAME TABLE. Consider the following
example:
CREATE TABLE t(a INT); INSERT INTO t VALUES (1),(2),(3); CREATE INDEX a ON t(a); DROP TABLE t;
The CREATE INDEX statement would be executed roughly
as follows:
CREATE TABLE temp(a INT, INDEX(a)); INSERT INTO[Read more...]
drop table if exists logs;[Read more...]
create table logs (t mediumtext) engine=innodb;
drop function if exists exceeded_logs_quota ;
create function exceeded_logs_quota()
returns boolean
deterministic
return (
select CASE
WHEN (DATA_LENGTH + INDEX_LENGTH) > (25*1024)
MySQL keeps many different files, some contain real data, some contain meta data. Witch ones are important? Witch can your throw away?
This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them.
When I was working for Dell doing Linux support my first words to a customer where “DO YOU HAVE COMPLETE AND VERIFIED BACKUP?” Make one now before you think about doing anything I suggest here.
You should always try to manage your data through a MySQL client. If things have gone very bad this may not be possible. MySQL may not start. If your file system get corrupt you may have missing files. Sometimes people create other files in the MySQL directory (BAD). This should help you understand what is safe to remove.
Before you try to work with one of
[Read more...]A question I recently saw on Stack Overflow titled Faster way to delete matching [database] rows? prompted me to organize my thoughts and observations on the subject and quickly jot them down here.
Here is the brief description of the task: say, you have 2 MySQL tables a and b. The tables contain the same type of data, for example log entries. Now you want to delete all or a subset of the entries in table a that exist in table b.
DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);
DELETE a FROM a INNER JOIN b on a.id=b.id;
DELETE FROM a WHERE id IN (SELECT id FROM b)
| Showing entries 1 to 4 |
