MySQL Forums
Forum List  »  Performance

Re: I have queries that sporadically take a very long time
Posted by: Stephan Tual
Date: February 22, 2009 09:48AM

Thank you Rick

Now the query is:
SELECT     	sites_news.ID
FROM        sites_news
INNER JOIN 	sites_news_categories ON sites_news.frn_news_categories_id = sites_news_categories.id
INNER JOIN	core_system ON sites_news.frn_site_id = core_system.site_id
INNER JOIN 	core_administration on core_administration.frn_site_id = core_system.site_id
WHERE 		
sites_news.publication_date_time <= '2009-02-21 23:34:46'
		
AND (FIND_IN_SET('sometag', sites_news.tags)
OR sites_news_categories.name = 'mycategory')
AND 		sites_news.enabled = 1
AND 		sites_news_categories.enabled = 1
AND			sites_news.frn_user_id != 0
AND			core_system.community_member = 1
AND		core_system.frn_language_id = 142
ORDER BY 	sites_news.publication_date_time DESC 
LIMIT 400;

When I run the query through a query tool like the mysql query analyser, AND when this query executes hundreds of time a day through our coldfusion mx server, it takes around 1.2second (1200ms) to execute.

Here's a dump from AquaDataStudio after running this query:

400 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 47/ms] 
Executed: 22/02/09 15:32:46 GMT Execution: 1203/ms

I see the same performance when running it through the webserver.

So, now let's look at the explain:

 id     select_type     table                  type     possible_keys                key      key_len     ref                                               rows     Extra                                        
 -----  --------------  ---------------------  -------  ---------------------------  -------  ----------  ------------------------------------------------  -------  -------------------------------------------- 
 1      SIMPLE          core_system            range    PRIMARY,speed5,speed6        speed5   4           (null)                                            19193    Using where; Using temporary; Using filesort 
 1      SIMPLE          core_administration    eq_ref   PRIMARY                      PRIMARY  4           whipper_cms_db.core_system.site_id                1        Using where; Using index                     
 1      SIMPLE          sites_news             ref      speed2,speed1,speed3,speed5  speed2   4           whipper_cms_db.core_system.site_id                3        Using where                                  
 1      SIMPLE          sites_news_categories  eq_ref   PRIMARY                      PRIMARY  4           whipper_cms_db.sites_news.frn_news_categories_id  1        Using where                                  

 4 record(s) selected [Fetch MetaData: 31/ms] [Fetch Data: 16/ms] 

 [Executed: 22/02/09 15:32:30 GMT ] [Execution: 0/ms]

That's an issue I see on a lot of my queries - the order by is required for what I want to do yet it triggers a filesort, not so great. I also have an issue with optimizing my joins - I realize I could do better but I don't know how - I've put indices on all the primary keys and it still always find one table it needs a fullscan for, and if I remove the table from the query it will run the full scan on the next table etc.

Regardless, None of this really matters - because my problem is that the slow query log records the following for this query at sporadic intervals:

# Query_time: 31  Lock_time: 0  Rows_sent: 400  Rows_examined: 119637

Notice it took 31 second to run this. The other entries are very similar - the query time sometimes sky rockets to 120 second, and the number of rows examined is always WAY higher than the explain shows. I can wait for the problem to occur (by monitoring the slow log), quickly run that query again and it will take 1.2seconds to run again. I can also go on my webserver and ctrl+tab pages in quick succession to trigger dozens of that same query, and all will run in 1.2 seconds.


Now for the tables themselves:

CREATE TABLE sites_news_categories ( 
    sort_order 	int(4) NOT NULL DEFAULT '0',
    name       	varchar(100) NOT NULL,
    enabled    	tinyint(1) NOT NULL DEFAULT '1',
    frn_site_id	int(10) NOT NULL DEFAULT '0',
    id         	int(10) AUTO_INCREMENT NOT NULL,
    PRIMARY KEY(id)
)
GO
CREATE INDEX speed1
    ON sites_news_categories(frn_site_id, enabled, sort_order)
GO


--------------------------------
--------------------------------


CREATE TABLE core_system ( 
    site_url                      	varchar(100) NOT NULL,
    maintain_displayProcessingTime	tinyint(1) NOT NULL DEFAULT '0',
    site_name                     	varchar(100) NOT NULL,
    site_company                  	varchar(100) NOT NULL,
    site_tagline                  	varchar(100) NOT NULL,
    maintain_siteDown             	tinyint(1) NOT NULL DEFAULT '0',
    maintain_displaySplashPage    	tinyint(1) NOT NULL DEFAULT '0',
    maintain_displayDebug         	tinyint(1) NOT NULL DEFAULT '0',
    maintain_hideCFErrorMessages  	tinyint(1) NOT NULL DEFAULT '1',
    searchEngines_keywords        	text NOT NULL,
    searchEngines_description     	varchar(120) NOT NULL,
    email_info                    	varchar(100) NOT NULL,
    layout_pageContent_thumbWidth 	int(4) NOT NULL DEFAULT '200',
    layout_imageGallery_thumbWidth	int(4) NOT NULL DEFAULT '148',
    extension_datasource_name     	varchar(100) NOT NULL,
    extension_datasource_login    	varchar(20) NOT NULL,
    extension_datasource_password 	varchar(20) NOT NULL,
    site_template                 	varchar(100) NOT NULL,
    layout_shop_thumbWidth        	int(4) NOT NULL DEFAULT '150',
    extension_has_custom_reg      	tinyint(1) NOT NULL DEFAULT '0',
    original_css                  	varchar(50) NULL,
    site_id                       	int(10) AUTO_INCREMENT NOT NULL,
    fckEditorWidth                	int(4) NULL DEFAULT '536',
    community_member              	tinyint(1) NOT NULL DEFAULT '1',
    show_user_info                	tinyint(1) NOT NULL DEFAULT '1',
    frn_language_id               	int(10) NOT NULL DEFAULT '142',
    frn_timezone_id               	int(10) NOT NULL DEFAULT '13',
    frn_activity_id               	int(10) NOT NULL DEFAULT '13',
    time_offset                   	varchar(3) NOT NULL DEFAULT '+0',
    home_module                   	varchar(25) NOT NULL,
    bandwidth_total               	int(11) NOT NULL DEFAULT '0',
    frn_reseller_id               	int(11) NULL,
    valid_site                    	tinyint(1) NOT NULL DEFAULT '0',
    VAT_registration              	varchar(50) NULL,
    general_header                	text NOT NULL,
    general_footer                	text NOT NULL,
    featured_description          	text NULL,
    featured_date                 	datetime NULL,
    is_featured                   	tinyint(1) NULL DEFAULT '0',
    PRIMARY KEY(site_id)
)
GO
ALTER TABLE core_system
    ADD CONSTRAINT speed1
	UNIQUE (site_url)
GO
CREATE INDEX speed2
    ON core_system(frn_reseller_id)
GO
CREATE INDEX speed5
    ON core_system(frn_language_id)
GO
CREATE INDEX speed4
    ON core_system(original_css)
GO
CREATE INDEX speed6
    ON core_system(community_member)
GO
CREATE INDEX speed3
    ON core_system(is_featured)
GO
CREATE UNIQUE INDEX speed1
    ON core_system(site_url)
GO

---------------------------------------
-----------------------------------------


CREATE TABLE core_administration ( 
    is_company                	tinyint(1) NOT NULL DEFAULT '0',
    frn_site_id               	int(10) NOT NULL DEFAULT '0',
    frn_country_id            	int(10) NOT NULL DEFAULT '0',
    suspended                 	tinyint(1) NOT NULL DEFAULT '0',
    date_joined               	datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    last_logged_in            	datetime NOT NULL DEFAULT '1970-01-01 00:00:01',
    login_count               	int(10) NOT NULL DEFAULT '0',
    page_views_count          	int(10) NOT NULL DEFAULT '0',
    frn_subscription_service  	int(11) NOT NULL,
    frn_subscription_storage  	int(11) NOT NULL,
    frn_subscription_bandwidth	int(11) NOT NULL,
    cache_page_views          	int(11) NOT NULL DEFAULT '0',
    cache_hits                	int(11) NOT NULL DEFAULT '0',
    signed_up_ip              	varchar(15) NOT NULL,
    PRIMARY KEY(frn_site_id)
)
GO
CREATE INDEX speed2
    ON core_administration(date_joined)
GO
CREATE INDEX speed4
    ON core_administration(frn_country_id)
GO
CREATE INDEX speed3
    ON core_administration(cache_page_views)
GO


---------------------------------------
--------------------------------------


CREATE TABLE sites_news ( 
    title                 	varchar(255) NOT NULL,
    teaser                	text NOT NULL,
    body                  	text NOT NULL,
    last_updated          	datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    first_entered         	datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    enabled               	tinyint(1) NOT NULL DEFAULT '0',
    publication_date_time 	datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    frn_site_id           	int(10) NOT NULL DEFAULT '0',
    id                    	int(10) AUTO_INCREMENT NOT NULL,
    frn_news_categories_id	int(10) NOT NULL DEFAULT '0',
    tags                  	varchar(255) NULL,
    frn_user_id           	int(11) NOT NULL DEFAULT '0',
    is_import             	tinyint(4) NOT NULL DEFAULT '0',
    is_sticky             	tinyint(1) NOT NULL DEFAULT '0',
    is_scheduled          	tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY(id)
)
GO
CREATE INDEX speed2
    ON sites_news(frn_site_id, frn_news_categories_id, enabled, publication_date_time)
GO
CREATE INDEX speed5
    ON sites_news(publication_date_time)
GO
CREATE INDEX speed3
    ON sites_news(frn_news_categories_id)
GO
CREATE INDEX speed1
    ON sites_news(frn_site_id, enabled, publication_date_time)
GO

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: I have queries that sporadically take a very long time
2012
February 22, 2009 09:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.