Technology

MySQL Performance Tuning

Only Ask for What You Need — And Be Explicit

Use

 SELECT name, rank, description FROM products

NOT

 SELECT * FROM sometable

Use Indexes to Speed Up Queries

CREATE TABLE albums (
    id        INTEGER      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title     VARCHAR(80)NOT NULL,

    INDEX title_idx (title)
);

Use Replace Queries

If the album with an id of 6 was supposed to have the title “Shaking the Tree,” you might write this query:

REPLACE INTO albums VALUES (6, ‘Shaking the Tree’)

It is important to understand how REPLACE determines whether or not a record is already in the table. MySQL will use any PRIMARY KEY or UNIQUE INDEX on the table to perform the check. If neither exist, the REPLACE effectively becomes an INSERT .

Advertisements
Technology

MySQL error: 'Access denied for user' or 'failed to connect'

MySQL error: ‘Access denied for user’ or ‘failed to connect’
PROBLEM: You are running MySQL 4.1.x. As of MySQL 4.1.X there is a new password hashing system. PHP4 does not have builtin support for it as of yet, however PHP5 does. In order for your old PHP4 MySQL clients to be able to connect to the MySQL 4.1.X database you need to set an OLD_PASSWORD. You should be able to run the following Query to make it possible for PHP4 to access the MySQL 4.1.X database.
SOLUTION :
SET PASSWORD FOR ‘username’@’localhost’ = OLD_PASSWORD(‘password’);

Technology

MYSQL event_scheduler

//first thing to do, turn on the MYSQL event_scheduler
SET GLOBAL event_scheduler = ON;

//check the status man!
SHOW GLOBAL VARIABLES like ‘event_scheduler’;

//create the event man!…
CREATE EVENT e_hourly
ON SCHEDULE
EVERY 1 hour
COMMENT ‘Update Average Value every hour.’
DO
INSERT INTO data_avg_minute SELECT CURRENT_TIMESTAMP(), avg(temperature),avg(sunradiation) ,avg(voltage1),avg(current1) ,avg(voltage2) ,avg(current2),avg(tc) FROM data WHERE moment >= ‘CURRENT_TIMESTAMP() – interval 1 hour’ AND moment <='CURRENT_TIMESTAMP()';

//if you want to stop the event.. just drop it..
drop event e_hourly;