Cause: MySQL reserves 4 bytes per UTF8 character. MyISAM allows keys of max. length 1000 bytes. UTF8 VARCHAR(255) will require 255 * 4 = 1020 bytes Solutions: There are several solutions for this issue: Changing the size of some columns. For example, if VARCHAR(300) is used to store last names, you [...]
Archive for the ‘MySQL’ Category
[MySQL] – ERROR 1071 : Specified key was too long; max key length is 1000 bytes
Posted in MySQL on November 5, 2011 | Leave a Comment »
Windows 7 – mysqldump access denied
Posted in MySQL on October 27, 2011 | Leave a Comment »
Need to give dump file an actual name. “mysqldump -u root -hlo calhost -p password –all-databases >%homepath%\Desktop\backup.sql” opposed to: “mysqldump -u root -h localhost -p password –all-databases >%homepath%\Desktop”.
Mysql – Finding second highest value of a column in a table.
Posted in MySQL, tagged MySQL on October 20, 2011 | Leave a Comment »
To get the 2 items; SELECT DISTINCT value FROM Table ORDER BY value DESC LIMIT 2 To get the second largest value of a column; SELECT MIN(value) FROM (SELECT DISTINCT value FROM Table ORDER BY value DESC LIMIT 2) AS t Please share i you know of any other better way.
Mysql – Calculate sum of distinct Rows
Posted in MySQL, tagged MySQL on October 3, 2011 | Leave a Comment »
We can use “DISTINCT” keyword with ‘count’ to acheive this; select index, count(DISTINCT number) from <table> group by index;
How mysql index works?
Posted in MySQL, tagged MySQL on September 26, 2011 | Leave a Comment »
The index makes query execution(where, order) faster because it is stored in memory, the corresponding index row can be looked at and it contains a pointer to where the actual data is stored. So MySQL can go to the exact location in the table without having to scan the table Basically an index is a [...]
When to use Indexes In MySQL
Posted in MySQL, tagged MySQL on September 26, 2011 | Leave a Comment »
Indexes are best used on columns that are frequently used in where clauses, and in any kind of sorting, such as “order by”. You should also pay attention to whether or not this information will change frequently, because it will slow down your updates and inserts. Since you wont frequently be adding employees, you don’t [...]
Basic useful Mysql commands
Posted in MySQL on September 22, 2011 | Leave a Comment »
To login; > mysql -u username -p Create a database; > create database [databasename]; List all databases; > show databases; Switch to a particular database. > use [db name]; To see all the tables in the db. > show tables; To see tables property/column details; > describe [table name]; To delete a db; > drop [...]
[Windows] Starting MySQL from the Windows Command Line
Posted in installations, MySQL, tagged installations, MySQL on August 20, 2011 | Leave a Comment »
The MySQL server can be started manually from the command line. This can be done on any version of Windows. To start the mysqld server from the command line, you should start a console window (or “DOS window”) and enter this command: shell> “C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld” The path to mysqld may vary depending on [...]
Import or Export A Database
Posted in MySQL on October 17, 2007 | Leave a Comment »
Export mysqldump -u username -p password database_name > FILE.sql Import mysqldump -u username -p password database_name < FILE.sql
Load Data into Table
Posted in MySQL on September 2, 2007 | Leave a Comment »
Import LOAD DATA INFILE ‘data.txt’ INTO TABLE table_name FIELDS TERMINATED BY ‘t’ LINES TERMINATED BY ‘n’; Export SELECT * INTO OUTFILE ‘data.txt’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’ FROM table_name;