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:
Here is a link to my current my.cnf file:
Here is a link to my proposed my.cnf file. I'm not sure if there are
any errors or mistakes in the file.
James Harvard <james.lists.tech@stripped> 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,
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
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,
> 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
> 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
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around