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