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,
$store_data_column[prod_name]
VARCHAR(127),
$store_data_column[thumb_url]
VARCHAR(127),
$store_data_column[msrp] DECIMAL(12,2) NOT
NULL,
$store_data_column[price] DECIMAL(12,2)
NOT NULL,
$store_data_column[disc] DECIMAL(12,2) NOT
NULL,
$store_data_column[cat1] VARCHAR(63) NOT
NULL,
$store_data_column[cat2] VARCHAR(63) NOT
NULL,
$store_data_column[cat3] VARCHAR(63) NOT
NULL,
$store_data_column[cat4] VARCHAR(63) NOT
NULL,
INDEX (`cat1`,`cat2`,`cat3`,`cat4`))";
I do lots of queries like selecting the categories
like:
$sql = "SELECT DISTINCT $store_data_column[cat1]
FROM $store_data_table
WHERE $store_data_column[cat1] != 'N/A'
ORDER BY $store_data_column[cat1] ASC";
and
$sql = "SELECT DISTINCT $store_data_column[cat2]
FROM $store_data_table
WHERE $store_data_column[cat1] =
'$url_params[cat1]' AND
$store_data_column[cat2] !=
'N/A'
ORDER BY $store_data_column[cat2]
ASC";
and
$sql = "SELECT DISTINCT $store_data_column[cat3]
FROM $store_data_table
WHERE
$store_data_column[cat1] = '$url_params[cat1]' AND
$store_data_column[cat2] = '$url_params[cat2]' AND
$store_data_column[cat3] != 'N/A'
ORDER BY
$store_data_column[cat3] ASC";
etc...
Also, I do selects like:
$sql = "SELECT $store_data_column[sku],
$store_data_column[prod_name],
$store_data_column[price],
$store_data_column[cat1],
$store_data_column[cat2],
$store_data_column[cat3],
$store_data_column[cat4]
FROM $store_data_table
WHERE $store_data_column[cat1] =
'$url_params[cat1]
ORDER BY $order_by_clause
LIMIT $item_offset, $max_items";
all the way to:
$sql = "SELECT $store_data_column[sku],
$store_data_column[prod_name],
$store_data_column[price],
$store_data_column[cat1],
$store_data_column[cat2],
$store_data_column[cat3],
$store_data_column[cat4]
FROM $store_data_table
WHERE $store_data_column[cat1] =
'$url_params[cat1]' AND
$store_data_column[cat2] =
'$url_params[cat2]' AND
$store_data_column[cat3] =
'$url_params[cat3]' AND
$store_data_column[cat4] =
'$url_params[cat4]
ORDER BY $order_by_clause
LIMIT $item_offset, $max_items";
Originally I didn't have the INDEX on categories.
Adding the index I have now sped up the queries
greatly, but I was wondering if there were any other
tips so squeeze some more performance out of the selects.
__________________________________
Do you Yahoo!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
