Hi James.
Thanks for the tips. I tried your below SQL call of quering just one
column. The query didn't speed up.
I think I am running out of RAM and thus caching the temp table to disk. My
server is currently using the default my.cnf file. I will try the large and
huge example .cnf files to increate table cache limits.
Like I mentioned before, I am tweaking the .cnf files by blind trial and
error. I would appreciate anyone with experience looking over my current
setup and proposed setup. I'm not sure how much RAM to allocate to each
server variable.
Here is a link to my current server variables:
http://retailretreat.com/mysql/server_variables.php.htm
Here is a link to my current my.cnf file:
http://retailretreat.com/mysql/my.cnf.txt
Here is a link to my proposed my.cnf file. I'm not sure if there are
any errors or mistakes in the file.
http://retailretreat.com/mysql/my-new.cnf.txt
Thanks,
Grant
James Harvard <[EMAIL PROTECTED]> wrote:I've recently been doing some big
table query optimisation, but after getting the query tweaked to hit the right
index the query time is under very livable-with, despite the fact that MySQL
seems to be examining more rows for my query than for yours. However the
'rows' column of thhe explain output is just an estimate.
I'm not an expert on this subject, but I do have a couple of ideas that
should only take you a couple of minutes to test, until a big-table-guru steps
in. :-)
You could try starting MySQL using the 'mysql/support-files/my-huge.cnf'
example config file to see if that makes any difference. "This is for a large
system with memory of 1G-2G where the system runs mainly MySQL." It may be
you're running into a limit on the size of temporary table MySQL will build in
RAM, so it's doing it on disc instead. I would try this first.
Also - and this really is just a guess - I wonder whether part of the problem
is that you're retrieving a lot of data (lots of columns) and then sorting the
resulting huge temporary table, only to use the first 10 rows. Try just
selecting just one row to see if that helps. If it does then you could maybe
use a sub-query or application code to feed the list of 10 prod_id values into
a query that gets all the columns you need.
SELECT pn_pricecompare_product.prod_id FROM pn_pricecompare_catprod,
pn_pricecompare_product
WHERE ((pn_pricecompare_catprod.category = '283155') AND
(pn_pricecompare_catprod.asin = pn_pricecompare_product.asin)) ORDER BY
pn_pricecompare_product.salesrank ASC LIMIT 0,10
It might be the case that you could create an index that MySQL could use to
optimise the 'order by', retrieving the rows in sorted order, but I'm not up
on that sort of optimisation and don't know for sure - you'd have to check
out the relevant manual section.
Another factor may be the table format - fixed versus dynamic row length. If
you need to come back on this maybe we could see a 'show create table' for the
two tables?
Also if you enable the slow query log, it tells you the actual number of rows
examined - might be useful if you continue to have trouble.
HTH & good luck,
James Harvard
> For the first time, I'm working with a really large database. I have 1 SQL
> statement that brings my server to it's knees. This setup is currently on my
> home development PC, and not in production. The server is running apache,
> samba, and mysql under gentoo linux. I'm the only user, so there is no
> vitually load on the server. The server has 1 Gig of ram.
>
> I've got 2 tables, one that holds a list of product, the other holds a list
> of categories that the product is associated with. My SELECT statment just
> grabs 10 products that are associated with a specific category. The product
> table has 650,000 rows and the category table has 8,150,000 rows.
>
> My SELECT statement is:
>
> SELECT pn_pricecompare_catprod.category, pn_pricecompare_catprod.asin,
> pn_pricecompare_product.title, pn_pricecompare_product.prod_id,
> pn_pricecompare_product.image_small, pn_pricecompare_product.brand,
> pn_pricecompare_product.manufacturer, pn_pricecompare_product.mpn,
> pn_pricecompare_product.model, pn_pricecompare_product.artist,
> pn_pricecompare_product.author, pn_pricecompare_product.binding,
> pn_pricecompare_product.label, pn_pricecompare_product.audiencerating,
> pn_pricecompare_product.studio, pn_pricecompare_product.releasedate,
> pn_pricecompare_product.numberofpages, pn_pricecompare_product.pubdate,
> pn_pricecompare_product.publisher, pn_pricecompare_product.searchindex,
> pn_pricecompare_product.lowest_price, pn_pricecompare_product.num_merchants
> FROM pn_pricecompare_catprod, pn_pricecompare_product WHERE
> ((pn_pricecompare_catprod.category = '283155') AND
> (pn_pricecompare_catprod.asin = pn_pricecompare_product.asin)) ORDER BY
> pn_pricecompare_product.salesrank ASC
> LIMIT 0,10
>
> Sometimes this takes 10 minutes to execute. When this occurs, I can hear
> the hard drive thrashing.
>
> If I do an EXPLAIN, I get:
>
> table type possible_keys key key_len ref
> rows Extra
> pn_pricecompare_catprod ref PRIMARY,asin PRIMARY 4 const 355416 Using
> where; Using index; Using temporary; Using f...
> pn_pricecompare_product eq_ref asin asin 10
> pn_pricecompare_catprod.asin 1
>
>
> When the query executes, and I check the processes, I see "Copying to tmp
> table on disk"
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
