Thanks Hank. I'll try to split up the query into 2 separate queries. I think
that the reason the original query is so slow is that I don't have enough RAM
allocated to mysql. When the original query takes place, I see a process
Copying to tmp table on disk. I believe it's writing all
The reason I suggested that you just use the alternative my-huge.cnf file is
because that is a ready-prepared config file optimised for systems with lots of
RAM for MySQL to use. You don't need to know which variable to change - it's
already done for you. You may want/need to tweak stuff later,
Thanks James. I'll definately give that a try. My test server has 1 gig of
ram. In the my-huge.cnf example it says that it's mainly for servers that
have mysql as the main process. On my production server, I have 1 gig of ram,
but it also runs apache, mutliple webstites, mysql, DNS, ftp
Based on the size (and # of colums) of your result set, I'm not sure
any amount of RAM would allow 360,000 records to be stored and sorted
entirely in memory. After some point, mysql just decideds to use a
temp table.
That's the limit of my tweaking skills, so someone else is going to
have to
Now that I know what's causing the slow queries, what can I do to fix it?
The only thing I can suggest is breaking up the query into two parts -
the first part to retrieve just the product codes and salesrank, and
sort and limit that.. save in a temp table or use application code to
retrieve and
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
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
I don't think the problem is going to be solved with the my.cnf file.
Here's what I would try..
1- run and time the original query without the ORDER BY or LIMIT clauses
2- run and time the following breakdown queries, to see if the
indexes are at least working correctly:
-- test catprod
Thank Hank. I will try this. When you say the table descriptions, do you
mean for me to post my CREATE TABLE syntax of how I created the table?
Thanks,
Grant
Hank [EMAIL PROTECTED] wrote: I don't think the problem is going to be
solved with the my.cnf file.
Here's what I would
That's why I suggested simply using the supplied my-huge.cnf config file,
because I assume it was contructed by some MySQL uber-guru who does know how
much RAM to allocate to each variable!
James Harvard
Like I mentioned before, I am tweaking the .cnf files by blind trial and
error. I
Grant,
You can just to a desc pn_pricecompare_catprod and desc
pn_pricecompare_product and post the results. The CREATE TABLE
statements would be OK, but the describes are better.
The flush the query cache, I think if you do a flush tables.
-Hank
--
MySQL General Mailing List
For list
Hank [EMAIL PROTECTED] wrote on 12/21/2005 09:39:50 AM:
Grant,
You can just to a desc pn_pricecompare_catprod and desc
pn_pricecompare_product and post the results. The CREATE TABLE
statements would be OK, but the describes are better.
The flush the query cache, I think if you do a
OK guys,
Here is are my tables and test SELECT queries. Before every query I did a
FLUSH TABLES command. Before starting these test, I did and ANALYIZE and
OPTIMIZE on all the tables. Hopefully the formatting of this email isn't too
bad.
product table:
show create table
Ok, so the next step would be to try the original query with just the
LIMIT clause, and then just the ORDER BY (but not both).
The results of select count(*) query would be helpful to know just
how many records mysql is trying to sort and limit.
And do you really need a separte index key on
It'll be a few hours before I get home and can do this query with the
variations you mentioned. I don't know if I need the separate index key on
`salesrank` or not. I thought it would speed up my query be having it.
In the previous email I included the results from doing an EXPLAIN.
OK,
I tried the following queries:
1. Use the ORDER BY and not the LIMIT -- still slow
2. Use the LIMIT and not the ORDER BY -- fast
So it looks like it's building a whole temp table with all the items and then
doing the ORDER BY.
Just as a test, I removed the INDEX
Hi,
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
17 matches
Mail list logo
