Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

MySQL - Speeding up large query

1:11 pm on Oct 4, 2007 (gmt 0)

New User

10+ Year Member

joined:Mar 12, 2007
posts: 26
votes: 0

I have two tables with approximately 5 mio rows each, on which I'm trying to execute the following query:

SELECT field1
FROM main_table USE INDEX (sort_field)
LEFT JOIN second_table l USING (primary_key_field)
WHERE l.field2 = "abcde"
ORDER BY sort_field ASC LIMIT 5000, 3000

I already optimized the query by adding "USE INDEX (sort_field)" to avoid temporary tables for sorting, but when I execute it, it still locks the table for around 10 minutes. In the process list the state of the query is 'Sending data', but I doubt that the data transfer is actually what takes so long, since both servers have a pretty fast connection.

Any ideas what I could change to speed this query up? I assume the biggest problem right now is the WHERE condition, since it doesn't use an index, but I don't know how I could fix that without losing the index on sort_field.

11:51 pm on Oct 4, 2007 (gmt 0)


WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
votes: 237

i would try the following and see if it fixes the problem:

ADD INDEX field2_index ON second_table (field2);

you might also want to limit the index length if field2 is CHAR or VARCHAR:

ADD INDEX field2_index ON second_table (field2(5));

if field2 is TEXT type, the length is required.

11:57 pm on Oct 4, 2007 (gmt 0)

Senior Member

joined:Nov 12, 2005
votes: 0

You might also want to take a look at the optimization documentation [dev.mysql.com] at mysql.com. There are some very nice ideas in there that make major improvements.
2:43 pm on Oct 6, 2007 (gmt 0)

Preferred Member

10+ Year Member

joined:Dec 30, 2003
posts: 428
votes: 0

How many rows will be coming into the sort, compared with the number of rows not having field2="abcde", or the number of rows having to be joined?

Add two indexes, one on primary_key_field (on each table, so 3 indexes I guess), and one on l.field2. Try getting rid of the forced index and run EXPLAIN on your query to see what MySQL is planning on doing.

If you want to avoid disk sorts you can always throw memory at it (tmp_table_size and max_heap_table_size)


(edit: clarity in the first para)

[edited by: SeanW at 2:44 pm (utc) on Oct. 6, 2007]

10:23 pm on Oct 7, 2007 (gmt 0)

New User

10+ Year Member

joined:Mar 12, 2007
posts: 26
votes: 0

Thanks for the replies!

I ended up removing the join completely and adding the field from the second table to the main table as well. It will probably cost me some disk space, but the query only takes a few seconds now.


Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members