MySQL
Get flash to fully experience Pearltrees
MyISAM is the default storage engine for MySQL database. MyISAM table gets corrupted very easily. In this article, I’ll explain how to use myisamchk to identify and fix table corruption in MyISAM. When a table is created under MySQL, it creates three different files: *.frm file to store table format, *.MYD (MyData) file to store the data, and *.MYI (MyIndex) to store the index. I prefer to use InnoDB as the storage engine for bigger database, as it resembles Oracle and provides commit, rollback options.
How To Repair Corrupted MySQL Tables Using myisamchk
Compressing mysqldump output
The result of mysqldump is a flat text file containing the sql commands used to restore the mysql databases/tables that were dumped. Normally the result of mysqldump is compressed (in regular backups) because the size of the resulted file is normally big and good compression rates are achieved on such text files. To do this manually using gzip we just run:
Re: Cursor not iterating correctly ...
Re: Cursor not iterating correctly ... Thanks kolja, You're right ...
How To Repair MySQL Replication
Version 1.0 Author: Falko Timme <ft [at] falkotimme [dot] com> Last edited 05/29/2008 If you have set up MySQL replication, you probably know this problem: sometimes there are invalid MySQL queries which cause the replication to not work anymore. In this short guide I explain how you can repair the replication on the MySQL slave without the need to set it up from scratch again.
How to debug InnoDB lock waits at Xaprb
This article shows you how to use a little-known InnoDB feature to find out what is holding the lock for which an InnoDB transaction is waiting. I then show you how to use an undocumented feature to make this even easier with innotop . Background
To list the average salary of employees in different departments (titles), we use the GROUP BY clause, as in: select title, AVG(salary) from employee_data GROUP BY title; +----------------------------+-------------+ | title | AVG(salary) | +----------------------------+-------------+ | CEO | 200000.0000 | | Customer Service Manager | 70000.0000 | | Finance Manager | 120000.0000 | | Marketing Executive | 77333.3333 | | Multimedia Programmer | 83333.3333 | | Programmer | 75000.0000 | | Senior Marketing Executive | 120000.0000 | | Senior Programmer | 115000.0000 | | Senior Web Designer | 110000.0000 | | System Administrator | 95000.0000 | | Web Designer | 87500.0000 | +----------------------------+-------------+ 11 rows in set (0.00 sec) Now, suppose you want to list only the departments where the average salary is more than $100000, you can't do it, even if you assign a pseudo name to AVG(salary) column. Here, the HAVING clause comes to our rescue.
mysql development tutorial - having clause - mysql clauses - mysql statements
Do we still need innodb_thread_concurrency?
Baron wrote this in a comment to a recent blog post . I consider innodb_thread_concurrency a vestigial tail of the “built-in InnoDB” that ships by default with MySQL 5.0 or 5.1, and should generally be set to 0 with recent versions of XtraDB or the InnoDB Plugin. Can this be? I cannot wait for innodb_thread_concurrency to be made obsolete. I run a lot of CPU-bound benchmarks on 8 and 16 core servers and I always set it to 0 in my benchmark framework.How to select the first/least/max row per group in SQL at Xaprb
Here are some common SQL problems, all of which have related solutions: how do I find the most recent log entry for each program? How do I find the most popular item from each category? How do I find the top score for each player?