?
Solved

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

Posted on 2009-02-16
8
Medium Priority
?
1,291 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

0
Comment
Question by:webshark79
  • 3
  • 3
  • 2
8 Comments
 
LVL 10

Expert Comment

by:mahome
ID: 23649922
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.
0
 
LVL 10

Expert Comment

by:mahome
ID: 23649955
If approved is selective try an combined index: cid, approved.
0
 

Author Comment

by:webshark79
ID: 23650794
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
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
LVL 41

Expert Comment

by:Sharath
ID: 23650862
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

0
 

Author Comment

by:webshark79
ID: 23650969
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

0
 
LVL 10

Accepted Solution

by:
mahome earned 2000 total points
ID: 23651793
>>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.


0
 

Author Comment

by:webshark79
ID: 23651846
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.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 23652844
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

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question