Tryangled Solutions web designing tips tricks

Frequently we query our database for a value but then have to always do the same manipulation on it before we can use it. Well it can often be useful to do this “once” in the database and then store the manipulated value in a separate column of the database.

Here is a very simple example
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*3.14159);

Where col2 is set to the value of col1*pi

You could also do more complex calculation/filtering/shortening in your code eg php,ASP etc

We are so frequently required to create date based queries, eg how many orders last week, last month, how many orders between two dates. It is necessary to avoid the trap of treating date as a number, instead convert the date into a handy unit such as days

# select all records from today
select * from tbl_orders where TO_DAYS(NOW()) = TO_DAYS(dtmdevdate);

# select all records from last three days
select * from tbl_orders where (TO_DAYS(NOW())-3) > TO_DAYS(orderdate);

MySQL provides a whole range of date functions become familiar with them here

Before you start coding your application with PHP or whatever remember to test out your MySQL in your MySQL GUI such as SQLYOG or even the CLI

What are the advantages?

* You don’t have to worry about the Script/MySQL syntax clashes (quoting etc)
* You only have to think MySQL and not say PHP as well
* You can see the data produced or not produced
* You can test your concepts

Columns with identical information in different tables should be declared to have identical data types so that joins based on the corresponding columns will be faster.

Use multiple-row INSERT statements to store many rows with one SQL statement.

* Use INSERT LOW_PRIORITY when you want to give SELECT statements higher priority than your inserts.
* Use SELECT HIGH_PRIORITY to get retrievals that jump the queue. That is, the SELECT is executed even if there is another client waiting.

Use insert delayed when you do not need to know when your data is written. This reduces the overall insertion impact because many rows can be written with a single disk write.

For MyISAM tables that change frequently, you should try to avoid all variable-length columns (VARCHAR, BLOB, and TEXT). The table uses dynamic row format if it includes even a single variable-length column.

his command defragments a table after you have deleted a lot of rows from it.

* Remove unnecessary parentheses
* COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM  and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.
* If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table