More MySQL queries · flogiston

Calculate table size


select table_name, data_length, data_length/1024/1024 as sizeinmb from information_schema. tables where table_schema='yourschemaname' order by sizeinmb desc;

Calculate ranking (rownum)

SET @rownum:=0; -- initialize row counter

SELECT @rownum:=@rownum+1 rownum, t.*
FROM  yourtable  t
where yourfield = 'somevalue';

OR you could do the initialization within the (sub) query:


SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, yourtable  t
where yourfield = 'somevalue';

Autogenerate some records using this technique


set @nrken:=0;

insert into [yourtable] (email, firstname,lastname,status)
select  concat('john.doe+',@nrken:=@nrken+1,'@gmail.com'), concat('john',@nrken), concat('doe',@nrken),'VALID' 
from [any table with more records than you wish to generate] .
where  @nrken&lt;88;

to select every 100th row


set  @counter=0;
select *, @counter  rownum from tbl having  (@counter:=@counter+1)%100=0 order by id;

source for user variable queries: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Update: 20121125

List constraints on a table

show create table yourtable;

select * from information_schema.table_constraints where table_schema = schema() and table_name = 'yourtable';