Hiya Tris,
Hi there, I've built my first shooping basket.. yey me!
Congrats! :)
However the search results page takes ages.
the query checks stock levels, and gender (mens, womens etc) and
fetches the brand title... it then returns any items that have stock
in that gender.
Anyhoo, here's my query and table structure... I've been using MySQL
for a few years now, but never anthing this 'complicated'
<snip>
Yup, that's a pretty hairy query :)
Little tip - I find it much easier to read SQL queries by breaking
them up a bit... I'd reformat your query to something like:
SELECT
distinct products.id,
products.title,
brands.title as brandTitle,
products.thumb2,
products.price,
products.salePrice,
products.saleStatus
FROM
products,
stock,
prodcatmatch,
categories as parent,
categories as child,
brands
WHERE
parent.gender = '1' AND
stock.productId = products.id AND
stock.count != "0" AND
brands.id = products.brand AND
parent.type = '2' AND
parent.id = stock.size
LIMIT 0,24
Takes up more space, but is much easier to read.
You'll probably want to try adding some indexes to the tables on the
columns you're using in the WHERE clauses.
MySQL has a useful 'EXPLAIN' command which will tell you how it
prepares and runs the query... just give it the query with EXPLAIN
before it, so "EXPLAIN SELECT....".
Here's two examples.
This query is efficient, it's going to use an index. The
possible_keys column shows the indexes it could have used, and the
'key' column shows the index it chose to use. The 'rows' figure
indicates how many rows of the table it will have to look at in order
to fulfil this query. In this case, since there's a good index, it
only needs to look at one row.
mysql> explain select * from Taglines WHERE ID = 3 \G
*************************** 1. row **************************:
id: 1
select_type: SIMPLE
table: Taglines
type: const
possible_keys: PRIMARY,FullIDX
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
Now, a query which is not efficient - there's no index it can use for
this one (there doesn't need to be as I'd never query this table by
the tagline field)... notice the 'key' column is NULL, and the 'rows'
column now shows that it will have to examine 1033 rows (all the rows
in the table) to satisfy this query.
mysql> explain select * from Taglines WHERE Tagline = 'foo' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Taglines
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1033
Extra: Using where
1 row in set (0.00 sec)
The output of that should give you some idea what you can do to
improve the situation.
Also, when you have spare time you could have a read up on database
normalisation... some parts of your table, like:
`img1` varchar(255) NOT NULL default '',
`img2` varchar(255) NOT NULL default '',
`img3` varchar(255) NOT NULL default '',
`img4` varchar(255) NOT NULL default '',
`thumb1` varchar(255) NOT NULL default '',
`thumb2` varchar(255) NOT NULL default '',
`thumb3` varchar(255) NOT NULL default '',
`thumb4` varchar(255) NOT NULL default '',
....
`relatedProd1` int(6) NOT NULL default '0',
`relatedProd2` int(6) NOT NULL default '0',
`relatedProd3` int(6) NOT NULL default '0',
`relatedProd4` int(6) NOT NULL default '0',
`relatedProd5` int(6) NOT NULL default '0',
`relatedProd6` int(6) NOT NULL default '0',
... could be better done using another table, for example
related_products, with one row for each link. Likewise for the
images, maybe:
CREATE TABLE product_images (
id INT(6) AUTO_INCREMENT PRIMARY KEY,
product_id INT(6) NOT NULL,
image_title VARCHAR(250),
image VARCHAR(250)
);
That way you could have arbritary numbers of images per product.
Now, wait for Rudy the SQL God to turn up and suggest a 1-line query
that does everything you need, and also takes care of the ordering,
provisioning and shipping of the tshirts, all whilst defragmenting the
hard drive and working out the meaning of life :)
Cheers
Dave Presh
http://www.preshweb.co.uk/
____ The WDVL Discussion List from WDVL.COM ____
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: [email protected]
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
________________ http://www.wdvl.com _______________________
You are currently subscribed to wdvltalk as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
