Archive for June, 2009
The FEDERATED Storage Engine
on June 15, 2009
The FEDERATED storage engine is available beginning with MySQL 5.0.3. It is a storage engine that accesses data in tables of remote databases rather than in local tables. The FEDERATED storage engine is available beginning with MySQL 5.0.3. This storage engine enables data to be accessed from a remote MySQL database on a local server without using replication or cluster technology. When using …
How we can count duplicate entery in particular table against Primary Key ? What are constraints?
on June 15, 2009
The syntax in the previous answer (where count(*) > 1) is very questionable. suppose you think that you have duplicate employee numbers. there’s no need to count them to find out which values were duplicate but the followin SQL will show only the empnos that are duplicate and how many exist in the table: Select empno, count(*) from employee group by empno having …
What are the advantages of mysql comparing with oracle?
on June 15, 2009
MySql has many advantages in comparison to Oracle. 1 – MySql is Open source, which can be available any time 2 – MySql has no cost of development purpose. 3 – MySql has most of features , which oracle provides 4 – MySql day by day updating with new facilities. 5 – Good for small applications. 6 – easy to learn and to …
What’s the difference between query_cache_type 1 and 2?
on June 15, 2009
The second one is on-demand and can be retrieved via SELECT SQL_CACHE … If you’re worried about the SQL portability to other servers, you can use SELECT /* SQL_CACHE */ id FROM … – MySQL will interpret the code inside comments, while other servers will ignore it.
How do you prevent MySQL from caching a query?
on June 15, 2009
SELECT SQL_NO_CACHE
What’s the difference between PRIMARY KEY and UNIQUE in MyISAM?
on June 15, 2009
PRIMARY KEY cannot be null, so essentially PRIMARY KEY is equivalent to UNIQUE NOT NULL.
How do you index just the first four bytes of the column?
on June 15, 2009
ALTER TABLE customers ADD INDEX (business_name(4))
How do you convert to a different table type?
on June 15, 2009
ALTER TABLE customers TYPE = InnoDB
Which storage engines support transactions in MySQL?
on June 15, 2009
Berkeley DB and InnoDB.
What’s ACID?
on June 15, 2009
Automicity – transactions are atomic and should be treated as one in case of rollback. Consistency – the database should be in consistent state between multiple states in transaction. Isolation – no other queries can access the data modified by a running transaction. Durability – system crashes should not lose the data.