We help IT Professionals succeed at work.

Problem with "Sorting result" - takes between 10-80 seconds to complete.

webshark79
webshark79 asked
on
Medium Priority
1,316 Views
Last Modified: 2012-05-06
I am running what seems to be a simple - 2 table join with a Sort by a primary index from the first table.

SELECT PenName, pennames.PenId, PenPhoto, articles.id, title, submitted, articles.summary
FROM articles, pennames
WHERE (articles.cid = '30' AND pennames.PenId = articles.p_id AND approved = '1')
ORDER BY articles.id DESC
LIMIT 0 , 30

id is a primary key on the table articles ( int(11), Auto Increment)
cid is an index, p_id is an index, approved is an index,
PenId is the primary key for the table pennames ( int(11), Auto Increment)

This resulted in this amount of time: 12.7847 sec

Based on the Profile I ran it got stuck on the "sorting results" for just about 12 seconds

Here is the explain of the Query:

id       select_type       table       type       possible_keys       key       key_len       ref       rows       Extra
1       SIMPLE       articles       ref       p_id,cid,approved       cid       4       const       10584       Using where; Using filesort
1       SIMPLE       pennames       eq_ref       PRIMARY       PRIMARY       4       abarticles_db.articles.p_id       1       

Most times this query runs very fast- but every 6-7 times suddenly it takes forever.

Its on a dedicated DB server (2x Quad core + 8gb memory + SCSI drives)

MySQL Server version: 5.0.45-log

Below is my my.cnf file.
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
skip-name-resolve
 
key_buffer_size = 1280M #was 256M
max_allowed_packet = 2M #was 64M
table_cache = 2048 #was 1000
sort_buffer_size = 3M
read_buffer_size = 3M
read_rnd_buffer_size = 3M
myisam_sort_buffer_size = 3M
thread_cache_size = 250
 
wait_timeout=45 #was 60
join_buffer_size=4M #was 3mb
 
query_cache_size=256M
query_cache_limit=4M
 
concurrent_insert = 4
 
interactive_timeout = 3600
 
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 16
 
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1
 
low_priority_updates=1
 
character_set_server=utf8
collation_server=utf8_general_ci
max_connections=500
 
long_query_time=5
log-slow-queries=/var/lib/mysql/slow-queries_0209.log
#log-queries-not-using-indexes
 
max_heap_table_size = 512M
tmp_table_size = 512M
 
skip-innodb
skip-bdb
 
[mysql]
no-auto-rehash
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Open in new window

Comment
Watch Question

Commented:
I can't see an obvious problem.

Ideas:
  • Increase sort_buffer_size
  • leave out order by to see if this is the problem
  • leave out the cid and/or approved restriction to see if this is the problem
Post your results (modification + time + query plan): perhaps we can find a solution.

Commented:
If approved is selective try an combined index: cid, approved.

Author

Commented:
Hi,

I didn't understand what you needed me to see "(modification + time + query plan)" ?

Without the Order by it works fine (as no sorting is needed) but as I am looking for the latest content from a specific category I have too have it.

I tried without the approved - didn't make a difference.

I have to have the 'cid' as that is the whole point of the query. *double checked - it does have an index.

I will try now with cid, approved INDEX
SharathData Engineer
CERTIFIED EXPERT

Commented:
can you try this? and check the run time.
SELECT PenName, pennames.PenId, PenPhoto, articles.id, title, submitted, articles.summary
FROM articles
JOIN pennames ON pennames.PenId = articles.p_id
WHERE (articles.cid = '30' AND approved = '1')
ORDER BY articles.id DESC
LIMIT 0 , 30

Open in new window

Author

Commented:
I just tried the above

Got sorting result of Sorting result 4.533136 (which is still very long)

Explain of the Query:

id       select_type       table       type       possible_keys       key       key_len       ref       rows       Extra
1       SIMPLE       articles       ref       p_id,cid,approved       cid       4       const       5209       Using where; Using filesort
1       SIMPLE       pennames       eq_ref       PRIMARY       PRIMARY       4       abarticles_db.articles.p_id       1       

From the explain it seems that the query is fine?

It uses all keys and is doing the sort of 5209.

Question is - does it create a temp table of 5209 rows with only the data I selected and then do a sort ?

Or does it sort the table articles first (based on the keys) and then do the select ?

as there is big field called 'content' which holds a fulltext article



SELECT articles.id, articles.title, articles.meta_desc, articles.submitted, pennames.PenRating, pennames.PenName,  pennames.PenId,  pennames.PenPhoto
FROM articles 
JOIN pennames ON (pennames.PenId = articles.p_id) 
WHERE (articles.cid='56' AND approved='1')
ORDER BY articles.id DESC 
LIMIT 0, 30;

Open in new window

Commented:
>>Without the Order by it works fine (as no sorting is needed) but as I am looking for the latest content from a specific >>category I have too have it.
I know that you need the sorting, I just wanted to get sure, that this is the problem.

>>It uses all keys and is doing the sort of 5209.
It only uses cid key, so the it can't use the p_id key for sorting.

I just checked the manual: http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html

You need an index on all 3 values to use the index for where restriction and sorting: cid, approved, id (id has to be the last). for the other to use the more selective first.

>>Or does it sort the table articles first (based on the keys) and then do the select ?
>>as there is big field called 'content' which holds a fulltext article
Really good question, the manual is not complete clear. It says that it reads each record but saves what is needed. So I'm unsure if the content table is a problem. But with the above index it shouldn't be a problem as only 30 rows are read from the table itself because the other info is in the index.


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Ok,

Ran this now :

    ALTER  TABLE  `articles`  ADD  INDEX  `cid_app_id` (  `cid` ,  `approved` ,  `id`  )    

It will take time has the table has over 700,00 rows.

I will update when complete and tested the queries again.

Thank you very much for your help.
SharathData Engineer
CERTIFIED EXPERT

Commented:
Also remove the quotes around 56 and 1. Both are integers and I assume that cid and approved columns are also of int type.
SELECT articles.id, articles.title, articles.meta_desc, articles.submitted, pennames.PenRating, pennames.PenName,  pennames.PenId,  pennames.PenPhoto
FROM articles 
JOIN pennames ON (pennames.PenId = articles.p_id) 
WHERE (articles.cid=56 AND approved=1)
ORDER BY articles.id DESC 
LIMIT 0, 30;

Open in new window

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.