wordpress 3.5 released

word press 3.5 had been released it has lot of option like gzip compression for pages, optimise table with repair & check table.

find free space & database size

In mysql, to find used space & free space this query will be used

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

chk max(id) & insert in single query

In MYSQL, we need to query for finding max(id) & value is stored  in a variable, we need to use that variable in the query or for stored procedure.

but, we can use these both in a single query.

create table ty(i int,u varchar(20));
insert into ty(i,u) select (select ifnull(max(i),0)+1 from ty),'A';
insert into ty(i,u) select (select ifnull(max(i),0)+1 from ty),'B';
select * from ty;
drop table ty;

extractvalue: Only constant XPATH queries are supported

In mysql extractvalue is used in xml column to extract the values from it.


extractvalue cannot be implemented directly into query for attribute matching in joining a table.

for attribute matching join two tables with the coulmns with extractvalue using concat function. concat function is used to assign the variable in xml.

it can be used as a function.

CREATE FUNCTION `exv`(ky text,xpath text) RETURNS text CHARSET latin1
BEGIN
 return cast(extractvalue(ky,xpath) as char);
END $$

create table tbl_xml (u_id int,t_xml text)
insert into tbl_xml(u_id,t_xml) values(4,'<a o_id="197" amt="20.00"/><a o_id="198" amt="10.00"/>')


create table tbl_order(u_id int,o_id int,o_amt int);


insert into tbl_order(u_id,o_id,o_amt) values(1,11,20),(2,20,40),(4,197,60),(4,198,20);

select *,exv(A.t_xml,concat('sum(//a[@o_id="',B.o_id,'"]/@amt)')) from tbl_xml A join tbl_order B on A.u_id=B.u_id;


select *,exv(A.t_xml,concat('sum(//a[@o_id="',B.o_id,'"]/@amt)')) from tbl_xml A join tbl_order B on exv(A.t_xml,concat('(//a[@o_id="',B.o_id,'"]/@amt)'));