Typo3 and Joomla in china

typo3,joomla,drupal,php

 

Archive for the ‘ Database ’ Category

Mysql index engine

Aug 20, 2010 Database

In order to improve the performance of sql, sometimes we should create the index to fields we want.
For example , these are 10000 datas in my consumer table.
If i want to get this result :
select c1.id from consumer c1,consumer c2, consumer c3
where c3.password=c2.password
and c2.password = c1.password
and c2.id-c1.id>0
and c2.id-c1.id<5 and c3.id-c2.id>0
and c3.id-c2.id<5
and c1.pest is null
and c2.pest is null
and c3.pest is null
and c1.password!=”96e79218965eb72c92a549dd5a330112″
order by c1.id desc

when no index in password field ,it will cost 18047 ms,but
after creating the index on this field, it will just cost 484 ms.
The comparison is very obvious.
the command to create and drop index on the field is like this :
create index idx_pass on consumer(password);
drop index idx_pass on consumer;

Query Performance Optimization

Jul 23, 2010 Database

1.Slow Query Basics:Optimize Data Access

  • Are you asking the database for data you do not need?
  • Is MySQL examing too much data?(index solution)

2.Ways to restructure queries

  • Complex queries Versus Many queries(few queries)
  • Chopping up a query
  • Join Decomposition(
    • When application joins may be more efficient
    • you cache and reuser a lot of data from earlier queries
    • you use multiple MyISAM tables
    • you distribute data across multiple severs
    • you replace joins with in() lists on larger tables
    • a  join refers to the same table multiple times
    • )

Notes on Storage Engine

Jul 23, 2010 Database

1.The MyISAM Storage Engine

  • Table locks
  • No automated data recovery
  • No transactions
  • Only indexes are cached in memory
  • Compact storage

2.The memory storage Engine

  • Table locks
  • No dynamic rows(no BLOB  and TEXT , even VARCHAR)
  • Hash indexes are the default index type
  • No index statistics
  • Content is lost on restart

3.The InnoDB Storage Engine

  • Transactional
  • Foreign keys
  • Row-level locks
  • Multiversioning
  • Clustering by primary key
  • All indexes contain the primary key columns
  • Optimized caching
  • Unpached indexes
  • Slow data load
  • Blocking AUTO_INCREMENT
  • No cached COUNT(*) value

Order Note

Mar 11, 2010 Database, PHP

In php programming, we may notice that  if we sort  a group of datas by a string field storing numbers , maybe we can not get the correct order we want .For example:

image1.jpg,image2.jpg,image10.jpg,image20.jpg,

after using function sort(),得到：

image1.jpg,image10.jpg,image2.jpg,image20.jpg,

this may be not what we want, so we can use natsort() to fix it .

But in the database if the field is varchar or text,how should we fix it ?

We can use this function :CAST.For example

select *  from  image  order by CAST(`image_order` AS SIGNED INT)

just to convert string to int, that is all. Anyway , if noticing it before , we can change  the type of image_order with SIGNED INT, that is better.