If you’re working on heavy duty websites, knowing your database and how to use it best can make a world of difference in terms of performance, and thus you should always optimize the database. That’s pretty much obvious.
The tricky part is how you do the optimization? Often it requires a lot of reading up on [...]
Posts Tagged Databases
Substring magic with mysql
Jan 24
Mysql is a wonderful database, and while many use it, most people only scratch the surface of what the database can do. One of the practical functions available is the substring_index function, and an imaginary mailing list example is a nice way to show how to use it.
Let imagine we have a mailinglist in a [...]
Mysql metadata
Nov 17
If you’re a developer and use mysql, I’m sure you’re aware that it’s a database and it quite good at storing data, but one of the neat things about Mysql (and most other databases) is also their ability to provide meta-data on the contents of the database.
Most people know how to use the meta-data queries [...]
It seems there are two camps when it comes to SQL and how to do database optimizations – the “generic camp” and “the specialist camp”. While I don’t consider myself an extremist, I am absolutely in the specialist camp and this little post is an explanation of why.
SQL is a generic database langauge . There [...]
Mysql: Random dice
Jan 1
Getting a random roll of the dice:
CREATE TABLE dice (
d_id int(11) NOT NULL auto_increment,
roll int,
[...]
Assume you have the table “exams” in the database “test”.Then, the following will give you XML output if executed from the shell prompt with the “-X” option. For html output use the “-H” option.
$ mysql -X -e “select * from exams” test
Finding records that do not match between two tables.
CREATE TABLE bookreport (
b_id int(11) NOT NULL auto_increment,
s_id int(11) NOT NULL,
[...]
Loading Data into Tables from Text Files.
Assume you have the following table.
CREATE TABLE loadtest (
pkey int(11) NOT NULL auto_increment,
[...]
To dump data into a comma separated file use this:
SELECT * INTO OUTFILE ‘tablename.csv’
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘n’
FROM tablename;
Replace tablename with the tablename of the table you which to dump to a file.
Through the mysql client:
update mysql.user set password=password(‘NEW_PASSWORD’) where user=’USERNAME’ and host=’HOSTNAME’;
flush privileges;
Through the command line:
mysqladmin -u USERNAME -p CURRENT_PWD password NEW_PWD
Replace USERNAME, CURRENT_PWD and NEW_PWD with appropriate values.