mysql storage engine

searching for memory storage engine

storing this for future reference;
http://jonathanhui.com/mysql-storage-engine

mysql index technique

http://www.w3schools.com/sql/sql_create_index.asp

My idea; field index just for frequently used table for searching. since updating is slow, (see note) no index for frequently updated data .

Indexes allow the database application to find data fast; without reading the whole table.


Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

optimize mysql with index

if you want to join large table, indexing may help.

table1: 30,000 rows
table2: 40 rows

indexing for example user ID may speed up select query

SELECT name, phoneNo, address FROM table1 INNER JOIN table2 ON table1.userid = table2.userid

with no index, query takes about 8second
but with index query only 0.4second

mysql count if

http://forums.mysql.com/read.php?10,203876,204010#msg-204010

select book_name,     count(if(rating=1, 1, null)) as "POOR",     count(if(rating=2,1,null)) as "Average",     count(if(rating=3,1,null)) as "Good",     count(if(rating=4,1,null)) as "Great" from rating group by book_name order by book_name; +----------------+------+---------+------+-------+ | book_name      | POOR | Average | Good | Great | +----------------+------+---------+------+-------+ | Javascript     |    0 |       1 |    0 |     0 |  | SQL Cookbook   |    0 |       1 |    1 |     1 |  | Visual Basic 6 |    1 |       0 |    1 |     0 |  +----------------+------+---------+------+-------+

http://www.sitepoint.com/forums/mysql-182/conditional-count-mysql-216548.html

IF(expression, return this if expression is true, return this if expression is false)

fine tune mysql server

http://serverfault.com/questions/53266/how-to-fine-tune-our-mysql-server

bookmark this page for later reference

mysql tuning

this is good reference how to do about optimizing mysql setting

http://serverfault.com/questions/148926/mysql-optimizing

http://www.ibm.com/developerworks/library/l-tune-lamp-3.html

mysql troubleshooting

mysql> SHOW FULL PROCESSLIST\G
>>>> (ERROR 2006 (HY000): MySQL server has gone away)

http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html
http://dbaspot.com/forums/mysql/147301-error-2006-hy000-mysql-server-has-gone-away.html

mysql dump and restore

http://www.patrickpatoray.com/?Page=30

mysqldump --user=XXXXXXXX --password=XXXXXXXX --databases DB_NAME --tables TABLE_NAME > /PATH/TO/DUMPFILE.SQL

mysql --verbose --user=XXXXXXXX --password=XXXXXXXX DB_NAME < /PATH/TO/DUMPFILE.SQL

install ngnix + php + mysql on windows

i follow this:

http://eksith.wordpress.com/2010/11/07/nginx-php-mysql-windows/

Default path mysql kloxo at centos

/var/lib/mysql/

** spend 2hours to found this.. haiya..

SQL Inner Join

SELECT t1.username AS Pengguna, t2.username AS Upline
FROM reseller t1
INNER JOIN reseller t2 ON t2.user_id = t1.parentID
LIMIT 0 , 30

http://www.sqltutorial.org/sqlselfjoin.aspx

SELECT column_list FROM table_A A INNER JOIN table_A B ON A.column_name1 = B.column_name2…. WHERE row conditions