I have a new project I'm working for and I was
wondering if anyone could help me optimize my selects
for speed. I have a table with about 500,000 entries.
The table structure I'm using is (via my PHP
commands):
$sql = "CREATE TABLE $store_data_table (
$store_data_column[sku] VARCHAR(10) NOT
NULL PRIMARY KEY,
You could probably make this primary key smaller, using an INTEGER (4 bytes). Some of your data types could probably also be more "narrowly" defined.
[...]
INDEX (`cat1`,`cat2`,`cat3`,`cat4`))";
This index could be smaller, it _could_ improve performace to have something like
INDEX (cat1(10),cat2(10),cat3(10),cat4(10))
or maybe
INDEX (cat1(14),cat2(10),cat3(6))
or possibly
INDEX (cat1,cat2(10),cat3(10))
This very much depends on your data, you should try a few different variants. The major issue is to make the combined size of all indexes smaller than your total amount of available memory on the MySQL server. If possible, set the server parameter key_buffer_size to something bigger than the total size of your indexes. This way the server will load all indexes in RAM, and perform much faster.
<URL: http://dev.mysql.com/doc/mysql/en/MyISAM_key_cache.html > <URL: http://dev.mysql.com/doc/mysql/en/Data_size.html > <URL: http://dev.mysql.com/doc/mysql/en/Server_parameters.html >
These features are very much improved upon in the upcoming versions of mysql (4.1), see:
<URL: http://dev.mysql.com/doc/mysql/en/Multiple_key_caches.html > <URL: http://dev.mysql.com/doc/mysql/en/Index_preloading.html >
You should also be aware of prefix comression:
<URL: http://dev.mysql.com/doc/mysql/en/Key_space.html >
I have seen a case where indexing like this:
name varchar(32) not null, INDEX (name(12))
...consumed MORE index space compared to indexing like this:
name varchar(32) not null, INDEX (name)
The manual has a lot on optimization:
<URL: http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html >
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
