On Thu, Sep 4, 2008 at 10:38 AM, mos <[EMAIL PROTECTED]> wrote 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 -------- I concur. The SELECT time is going to resemble something like: K_1 * F_1(number_of_records_in_database) + K_2 * F_2(number_of_records_selected) If the indices are effective, F_1 = log(N), but if the indices are not effective, F_1 = N. One thing you may want to try to narrow down the problem is just retrieving 100 records (the COUNT clause of a query) and see how that affects the speed, then try the full set and see how it is different. If they aren't very different, then it is a F_1 problem. But if they are different, then it is a K_2 / F_2 problem. As far as K_2 or F_2 problems ... Another possibility is that you are using ORDER BY on a large result set that isn't indexed for an effective sort. Try dropping the ORDER BY and see what happens. My view of how MySQL might work internally is perhaps naive. But sorting can be worst case O(N**2). Dave. 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] > >
