At 02:49 PM 9/3/2008, Jim Leavitt wrote:
Hi Mike,
Yes sometimes, the application is an online book selection tool with
about 1 million titles in it. Now the queries which return 100,000 rows
would be something like returning all titles from a given publisher. Most
of the common searches are fairly quick (1-5 sec). But this was a
specific example given to me. As you can imaging we're joining on many
tables to pull author data, publication data, etc and displaying it all on
a detail page. An example query is. (note: this is on a development box
with nothing else on it)
SELECT p.Title FROM products AS p LEFT JOIN productcontributors AS pc ON
p.RecordReference = pc.RecordReference WHERE pc.rowtype = "PR8" AND
p.feedid = "5" GROUP BY p.id LIMIT 0,10;
returns
10 rows in set (42.12 sec).
(Total of 194557 rows found.)
Now we've never dealt with anything like this before, but there are other
sites returning similar counts fairly quickly. The only thing I can think
of is hardware. What hardware upgrades would you recommend? Would it
even help? Would clustering be an option here?
Any advice is greatly appreciated.
Thanks much.
Jim,
The problem is likely your index is not defined properly. Use an
"Explain" in front of the query to see if it can use just one index from
each table.
I would try building a compound index on
Products: (RecordReference, FeedId)
ProductContributors: (RecordReference, RowType)
This should get it to execute the join and where clause using just one
index from each table. Give that a try and see if it speeds things up. :)
Mike
On 3-Sep-08, at 3:02 PM, mos wrote:
Jim,
Retrieving 100,000 rows will always take some time. Do you really
need to return that many rows? Are you selecting just the columns you
need? What are the slow queries?
Mike
At 12:05 PM 9/3/2008, Jim Leavitt wrote:
Greetings List,
We have a medium-large size database application which we are trying
to optimize and I have a few questions.
Server Specs
1 Dual Core 2.6 Ghz
2GB Ram
Database Specs
51 Tables
Min 100000 rows, Max 1000000 rows
Total size approx 2GB
My.cnf
[mysqld]
set-variable=local-infile=0
log-slow-queries=slow-queries.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
key_buffer = 512M
max_allowed_packet=4M
sort_buffer_size = 512M
read_buffer_size = 512M
read_rnd_buffer_size = 256M
record_buffer = 256M
myisam_sort_buffer_size = 512M
thread_cache = 128
query_cache_limit = 1M
query_cache_type = 1
query_cache_size = 32M
join_buffer = 512M
table_cache = 512
We are having trouble with certain queries which are returning
anywhere from 100000 - 300000 rows. Total query time is taking approx
1 - 2 mins depending on load. Is there anything in our conf file
which could improve our performance? Are there any hardware
recommendations that could help us improve the speed? Would more
memory help us? Any comments or recommendations are greatly
appreciated.
Thanks much.
Jim Leavitt
Developer
Treefrog Interactive Inc. (<http://www.treefrog.ca>www.treefrog.ca)
"Bringing the Internet to Life"
--
MySQL General Mailing List
For list archives: <http://lists.mysql.com/mysql>http://lists.mysql.com/mysql
To
unsubscribe:
<http://lists.mysql.com/[EMAIL PROTECTED]>http://lists.mysql.com/[EMAIL PROTECTED]
Jim Leavitt
Developer
Treefrog Interactive Inc. (<http://www.treefrog.ca/>www.treefrog.ca)
"Bringing the Internet to Life"
ph: 905-836-4442 ext 104
fx: 905-895-6561
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
