Mysql Tips

Many times, slow access to a mysql database can be the result of Badly defined or non-existent indexes and fixing these can often lead to better performance. Here is an

example table:

CREATE TABLE address_book (

contact_number char(10) NOT NULL,
firstname varchar(40),
surname varchar(40),
address text,
telephone varchar(25)
);

example query: SELECT firstname FROM address_book WHERE contact_number = '12312′;

This will retrieve the firstname of a person added to the address_book table, based on the contact number.

Without any kind of indexes added to this table, mysql will have to search through each row to find the item that you would like to find, which is very inefficient.

Optimizing your table

There is a built-in command called explain, that can show you what, if any, indexes that are being used to retrieve results.

example:

EXPLAIN SELECT firstname FROM address_book WHERE contact_number = '12312′;

This will return a set of results that will tell you how myql is processing the results

table: The table the output is about (will show multiple if you have joins)
type: The type of join is being used.best to worst the types are: system, const, eq_ref, ref, range, index, all
possible_keys: Shows which possible indexes apply to this table
key: And which one is actually used
key_len: The length of the key used. The shorter that better.
ref: The column, or a constant, is used
rows: The number of rows mysql believes it must examine to get the data
extra: You don't want to see "using temporary" or "using filesort"

and index can be added to the above example table using the following command:

ALTER TABLE address_book ADD INDEX(contact_number);

you can also add an index on only part of a varchar. In the following, I will add an index on only 8 of the 10 characters.

ALTER TABLE address_book ADD INDEX(contact_number(8));

Why would you want to do this?

Indexes do increase performance in the right situations, but they are also a tradeoff between speed and space. The bigger an index is, the more space it will consume on your harddrive.

Using the query optimizer/analyzer

the following command can analyze your table key distribution to find out the best indexes to use:

analyze table *tablename*

also, another thing to keep in mind is the fact that over time, update and delete operations leave gaps in the table, which will cause un-needed overhead when reading data from your tables.

from time to time, it is a good idea to run the following (which will fix the above issue):

optimize table *tablename*
Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
5 mysql tips

By Justin Silverton

These are some tips that may help you out when dealing with mysql tables (known in 4.1 and below).

1) char and varchar are case sensitive

example:

if you have a table that contains the following:

table newtable (
name varchar(32)
)

name contains the name "John Smith".

the following statement: "SELECT * from newtable where name='john smith' will return our record.

to stop this from happening, use the following when you create your table:

CREATE TABLE newtable (
name VARCHAR(32) BINARY
)

2) Varchar type is limited to 255 characters

3) Varchar trailing spaces are stripped

example: insert into newtable values('Test with no spaces ');

select concat(name, 'no spaces') FROM newtable;

output will be: Test with no spacesnospaces

Varchar works this way, because it saves space by stripping the spaces.

if you need to keep the trailing spaces in the data you are adding to a varchar type,
you need to use the text or blob types.

4) operator

The (or) operater is a logical operator

example: select 'string1′ 'string2′ will not return 'string1string2′

5) function parameters

This issue has caused me many headaches in the past, and I am not sure why this issue was never fixed. If there is a space
between the paramater list and an internal function that you want to execute, it will return an error.

example: select min (my_field) from mytable wil return an error, while select min(my_field) from my_table will not