Having Apache + PHP + MySQL on Fedora 17 Guest on VirtualBox for Mac OSX Lion

Coming soon. The end result will be

  1. web server and db server will be on guest virtual
  2. PHP IDE will be in Host
  3. Access web server and db server via host
Advertisements

MySQL EXPLAIN Select

Explaining EXPLAIN

i have SELECT * FROM SMSPROXY WHERE STATUS =10
explain this select of course will get me the possible keys to use index and rows return is 1, using where. seems ok that can minimize the CPU usage.

i did accidentally change mysql select to SELECT * FROM SMSPROXY WHERE STATUS =10 AND MOD(ID,10)=1 , and doing that EXPLAIN , i still get the same possible keys and the xtra shows using where

again i changed my select to SELECT * FROM SMSPROXY WHERE STATUS =10 AND MOD(ID,10)=1 ORDER BY RAND()
now i get “Using where; Using temporary; Using filesort”. This means MySQL will create a temporary table (hopefully in memory) to hold the results. based on hackmysql.com , A filesort is not desirable either because it means “MySQL will need to do an extra pass to find out how to retrieve the rows in sorted order.”. Therefore i remove the RAND() functions . Seems everything went back to normal and the CPU move down to 24% from 98%

MySQL – no my.cnf when installing in Linux

I just found out here on how to have the file. By default, no my.cnf will be created during installation. Therefore some SERVER VARIABLES may be set up during session initialized.
Before creating the file, make sure check if any my.cnf exists in the system. If we want to overide existing my.cnf which we don’t know which one is using, simply create my.cnf in /etc/my.cnf

Creating a daily backup MySQL database in Windows

You may using Windows as your database server, but don’t know how to backup it, and some of you may use tools. You can actually do it by running this command

“C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump.exe” –host=localhost -uroot -ppassword –opt mydatabase > “C:\helmy\backup\%date:~10,4%_%date:~4,2%_%date:~7,2%_backup.sql”

It will create a file 2008_04_04_backup.sql

If you want to schedule it, put it on a backup_db.bat and ask Windows Task Scheduler to run it