$30
How do I speed up this VIEW, that has behind it a JOIN?
mysql -V
mysql Ver 14.14 Distrib 5.1.37, for debian-linux-gnu (i486) using EditLine wrapper
We have a view that creates behind it a very large join that is grinding our database to a halt. We need to figure out how to speed this up. I'm guessing that part of the problem here is the way that views deal with the indexes on tables - I'm under the impression that these get thrown away, so a great loss in performance is suffered. I read somewhere that sub-queries take full advantage of indexes, even as views do not?
Any thoughts on how to speed this up? This is the view:
CREATE VIEW chocolate_content_by_topic AS
SELECT DISTINCT c.id, c.textid, c.title, c.title_link, c.content_date, c.issue_date,
c.protected as chocolate_protected, c.allow_comments, c.show_comments, c.content_hint,
c.live_date, c.state,
c.author1, c.author2, c.author3, c.author4, c.author_str, c.subtitle,
c.abstract as chocolate_abstract, c.direct_url, c.hp_text,
c.imgpath1, c.imgpath2, c.imgpath3, c.imgpath4, c.imgpath5,
c.imgcaption1, c.imgcaption2, c.imgcaption3, c.imgcaption4, c.imgcaption5,
c.content_type, c.topic_str, c.reference_link_1, c.alt_info_1,
b.blog_name, b.blog_id,
it.issue_text_date,
tx.topic_id as topic_id,
d.literal, d.alias, d.cdaobject, d.cdaaction, d.channel, d.adsite, d.adzone, d.indexable
FROM chocolate_content c
INNER JOIN chocolate_topic_xref tx ON ( tx.content_id = c.id AND tx.state = c.state )
LEFT OUTER JOIN chocolate_issue it ON c.issue_date = it.issue_date
LEFT OUTER JOIN chocolate_blogs b ON c.reference_link_1 = b.blog_id
LEFT JOIN chocolate_content_type d on c.content_type=d.id
ORDER BY content_date DESC , priority DESC , id DESCThese are the tables:
mysql> explain chocolate_content_type;
+------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| parent | int(11) | NO | MUL | NULL | |
| lft | int(11) | NO | | NULL | |
| rgt | int(11) | NO | | NULL | |
| literal | varchar(50) | NO | | NULL | |
| alias | varchar(50) | NO | MUL | NULL | |
| seq | int(11) | NO | | 50 | |
| assignable | char(1) | NO | | N | |
| searchable | char(1) | NO | | NULL | |
| default_protected | char(1) | NO | | NULL | |
| indexable | char(1) | NO | | Y | |
| default_show_comments | char(1) | NO | | N | |
| default_allow_comments | char(1) | NO | | N | |
| cmaobject | varchar(50) | NO | | NULL | |
| cdaobject | varchar(50) | NO | | NULL | |
| cdaaction | varchar(50) | NO | | NULL | |
| channel | varchar(50) | NO | | NULL | |
| adsite | varchar(50) | YES | | NULL | |
| adzone | varchar(50) | YES | | NULL | |
| mlc | varchar(500) | YES | | NULL | |
| bc_link | varchar(200) | NO | | NULL | |
| bc_text | varchar(100) | NO | | NULL | |
| srch_heading | varchar(200) | NO | | NULL | |
+------------------------+--------------+------+-----+---------+----------------+
23 rows in set (0.00 sec)
mysql> explain chocolate_blogs;
+---------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+----------------+
| blog_id | int(11) | NO | PRI | NULL | auto_increment |
| blog_type | char(1) | NO | | I | |
| blog_name | varchar(200) | YES | | NULL | |
| blog_short_name | varchar(200) | YES | | NULL | |
| blog_sort_name | varchar(50) | YES | | NULL | |
| blog_primary_author | int(11) | YES | | NULL | |
| blog_priority | float | NO | | 0 | |
| blog_is_active | char(1) | NO | MUL | Y | |
| blog_promo | varchar(1000) | YES | | NULL | |
| blog_intro | longtext | YES | | NULL | |
| blog_description | longtext | YES | | NULL | |
| blog_imgpath1 | varchar(200) | YES | | NULL | |
| blog_imgcaption1 | varchar(500) | YES | | NULL | |
| blog_imgcredit1 | varchar(200) | YES | | NULL | |
| blog_imgpath2 | varchar(200) | YES | | NULL | |
| blog_imgcaption2 | varchar(500) | YES | | NULL | |
| blog_imgcredit2 | varchar(200) | YES | | NULL | |
+---------------------+---------------+------+-----+---------+----------------+
17 rows in set (0.00 sec)
mysql> explain chocolate_issue;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| issue_date | date | NO | MUL | NULL | |
| issue_text_date | varchar(100) | NO | | NULL | |
| issue_title | varchar(300) | NO | | NULL | |
| issue_intro | longtext | YES | | NULL | |
| content_block_1 | longtext | YES | | NULL | |
| content_block_2 | longtext | YES | | NULL | |
| issue_live_date | datetime | NO | | NULL | |
| bg_live_date | datetime | NO | | NULL | |
| link_live_date | datetime | NO | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
mysql> explain chocolate_topic_xref;
+--------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| state | char(1) | NO | | NULL | |
| content_id | int(11) | NO | MUL | NULL | |
| content_type | int(11) | NO | | NULL | |
| topic_id | int(11) | NO | MUL | NULL | |
+--------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> explain chocolate_content;
+----------------------------+---------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------+------+-----+---------------------+-------+
| id | bigint(20) | NO | PRI | NULL | |
| state | char(1) | NO | PRI | W | |
| legacyid | varchar(50) | YES | MUL | NULL | |
| textid | varchar(200) | YES | MUL | NULL | |
| content_hint | char(1) | YES | | NULL | |
| aliastext | varchar(20) | YES | MUL | NULL | |
| package_tags | varchar(200) | YES | MUL | NULL | |
| priority | float | YES | | 0 | |
| reference_link_1 | int(11) | YES | | NULL | |
| alt_info_1 | varchar(200) | NO | | NULL | |
| alt_info_2 | longtext | NO | | NULL | |
| parent_content_id | int(11) | YES | | NULL | |
| created_at | datetime | NO | | NULL | |
| created_by | varchar(30) | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
| updated_by | varchar(30) | NO | | NULL | |
| published_at | datetime | YES | | 0000-00-00 00:00:00 | |
| published_by | varchar(30) | YES | | NULL | |
| content_type | int(11) | NO | MUL | NULL | |
| protected | char(1) | NO | | D | |
| override_channel | varchar(50) | YES | | NULL | |
| override_adsite | varchar(50) | YES | | NULL | |
| override_adzone | varchar(50) | YES | | NULL | |
| override_mlc | varchar(200) | NO | | NULL | |
| direct_url | varchar(250) | YES | | NULL | |
| pvar1 | varchar(100) | NO | | NULL | |
| pvar2 | longtext | NO | | NULL | |
| content_date | datetime | YES | | 0000-00-00 00:00:00 | |
| live_date | datetime | YES | | 0000-00-00 00:00:00 | |
| issue_date | date | YES | MUL | NULL | |
| issue_toc_id | int(11) | YES | | NULL | |
| issue_page | int(11) | YES | | NULL | |
| toc_title | varchar(500) | YES | | NULL | |
| toc_subtitle | longtext | YES | | NULL | |
| title | varchar(200) | NO | | NULL | |
| title_link | varchar(150) | YES | | NULL | |
| subtitle | varchar(300) | NO | | NULL | |
| author1 | int(11) | NO | | 0 | |
| author2 | int(11) | NO | | 0 | |
| author3 | int(11) | NO | | 0 | |
| author4 | int(11) | NO | | 0 | |
| author_ids | varchar(100) | NO | | NULL | |
| author_str | varchar(500) | NO | | NULL | |
| topic_ids | varchar(100) | NO | | NULL | |
| topic_str | longtext | NO | | NULL | |
| assoc1_ids | varchar(200) | NO | | NULL | |
| assoc1_str | longtext | NO | | NULL | |
| assoc2_ids | varchar(200) | NO | | NULL | |
| assoc2_str | longtext | NO | | NULL | |
| assoc3_ids | varchar(200) | NO | | NULL | |
| assoc3_str | longtext | NO | | NULL | |
| hp_include | char(1) | NO | | N | |
| hp_seq | int(11) | NO | | 0 | |
| hp_title | char(200) | YES | | NULL | |
| hp_text | longtext | YES | | NULL | |
| hp_include_image | char(1) | NO | | N | |
| hp_image | varchar(200) | YES | | NULL | |
| promo_text_1 | varchar(1000) | NO | | NULL | |
| promo_text_2 | varchar(1000) | NO | | NULL | |
| meta_desc | longtext | NO | | NULL | |
| meta_keywords | longtext | NO | | NULL | |
| meta_title | varchar(300) | NO | | NULL | |
| tags | longtext | NO | | NULL | |
| abstract | longtext | NO | | NULL | |
| pcontent | longtext | NO | | NULL | |
| scontent | longtext | YES | | NULL | |
| altcontent | longtext | YES | | NULL | |
| orig_content | longtext | YES | | NULL | |
| suppress_standard_sidebars | char(1) | NO | | N | |
| use_sidebar | char(1) | NO | | N | |
| sidebar_title | varchar(1000) | YES | | NULL | |
| sidebar_html | longtext | YES | | NULL | |
| sidebar_object_1 | varchar(50) | YES | | NULL | |
| sidebar_value_1 | varchar(200) | YES | | NULL | |
| sidebar_title_1 | varchar(150) | YES | | NULL | |
| sidebar_object_2 | varchar(50) | YES | | NULL | |
| sidebar_value_2 | varchar(200) | YES | | NULL | |
| sidebar_title_2 | varchar(150) | YES | | NULL | |
| sidebar_object_3 | varchar(50) | YES | | NULL | |
| sidebar_value_3 | varchar(200) | YES | | NULL | |
| sidebar_title_3 | varchar(150) | YES | | NULL | |
| imgpath1 | varchar(150) | YES | | NULL | |
| imgcaption1 | varchar(1000) | YES | | NULL | |
| imgcredit1 | varchar(500) | YES | | NULL | |
| imglink1 | varchar(150) | YES | | NULL | |
| imgpath2 | varchar(150) | YES | | NULL | |
| imgcaption2 | varchar(1000) | YES | | NULL | |
| imgcredit2 | varchar(500) | YES | | NULL | |
| imglink2 | varchar(150) | YES | | NULL | |
| imgpath3 | varchar(150) | YES | | NULL | |
| imgcaption3 | varchar(1000) | YES | | NULL | |
| imgcredit3 | varchar(500) | YES | | NULL | |
| imglink3 | varchar(150) | YES | | NULL | |
| imgpath4 | varchar(150) | YES | | NULL | |
| imgcaption4 | varchar(1000) | YES | | NULL | |
| imgcredit4 | varchar(500) | YES | | NULL | |
| imglink4 | varchar(150) | YES | | NULL | |
| imgpath5 | varchar(150) | YES | | NULL | |
| imgcaption5 | varchar(1000) | YES | | NULL | |
| imgcredit5 | varchar(500) | YES | | NULL | |
| imglink5 | varchar(150) | YES | | NULL | |
| aobject1 | varchar(50) | YES | | NULL | |
| aparams1 | longtext | YES | | NULL | |
| aobject2 | varchar(50) | YES | | NULL | |
| aparams2 | longtext | YES | | NULL | |
| aobject3 | varchar(50) | YES | | NULL | |
| aparams3 | longtext | YES | | NULL | |
| allow_comments | char(1) | NO | | D | |
| show_comments | char(1) | NO | | D | |
| stripped_search_1 | longtext | YES | | NULL | |
| stripped_search_2 | longtext | YES | | NULL | |
| rel_type_1 | varchar(50) | YES | | NULL | |
| rel_data_1 | longtext | YES | | NULL | |
| rel_type_2 | varchar(50) | YES | | NULL | |
| rel_data_2 | longtext | YES | | NULL | |
| rel_type_3 | varchar(50) | YES | | NULL | |
| rel_data_3 | longtext | YES | | NULL | |
| rel_type_4 | varchar(50) | YES | | NULL | |
| rel_data_4 | longtext | YES | | NULL | |
| rel_type_5 | varchar(50) | YES | | NULL | |
| rel_data_5 | longtext | YES | | NULL | |
+----------------------------+---------------+------+-----+---------------------+-------+
121 rows in set (0.44 sec)UPDATE:
I am now looking at the UNION syntax:
http://dev.mysql.com/doc/refman/5.0/en/union.html
Does anyone know if this is faster than JOIN syntax?
Lawrence Krubner | 09/23/10 at 12:55pm
| Edit
(4) Possible Answers Submitted...
-
Last edited:
09/29/10
11:32amJarret Minkler says:Is the DISTINCT required?
What is the output of
EXPLAIN EXTENDED
SELECT DISTINCT c.id, c.textid, c.title, c.title_link, c.content_date, c.issue_date,
c.protected as chocolate_protected, c.allow_comments, c.show_comments, c.content_hint,
c.live_date, c.state,
c.author1, c.author2, c.author3, c.author4, c.author_str, c.subtitle,
c.abstract as chocolate_abstract, c.direct_url, c.hp_text,
c.imgpath1, c.imgpath2, c.imgpath3, c.imgpath4, c.imgpath5,
c.imgcaption1, c.imgcaption2, c.imgcaption3, c.imgcaption4, c.imgcaption5,
c.content_type, c.topic_str, c.reference_link_1, c.alt_info_1,
b.blog_name, b.blog_id,
it.issue_text_date,
tx.topic_id as topic_id,
d.literal, d.alias, d.cdaobject, d.cdaaction, d.channel, d.adsite, d.adzone, d.indexable
FROM chocolate_content c
INNER JOIN chocolate_topic_xref tx ON ( tx.content_id = c.id AND tx.state = c.state )
LEFT OUTER JOIN chocolate_issue it ON c.issue_date = it.issue_date
LEFT OUTER JOIN chocolate_blogs b ON c.reference_link_1 = b.blog_id
LEFT JOIN chocolate_content_type d on c.content_type=d.id
ORDER BY content_date DESC , priority DESC , id DESC
Previous versions of this answer: 09/23/10 at 2:01pm
- 09/23/10 2:09pm
Jarret Minkler says:and also explain extended select * from chocolate_content_by_topic; just to verify it's not using the indexes.
- 09/23/10 3:20pm
Lawrence Krubner says:Good question! Here you go:
+----+-------------+-------+--------+-----------------------------+------------+---------+------------------------------------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+-----------------------------+------------+---------+------------------------------------+------+----------+---------------------------------+
| 1 | SIMPLE | tx | ALL | ByID | NULL | NULL | NULL | 4287 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY,Retr01,Retr02,Ref03 | PRIMARY | 11 | coco.tx.content_id,coco.tx.state | 1 | 100.00 | Using where |
| 1 | SIMPLE | it | ref | issue_date | issue_date | 3 | coco.c.issue_date | 1 | 100.00 | |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | coco.c.reference_link_1 | 1 | 100.00 | |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | coco.c.content_type | 1 | 100.00 | |
+----+-------------+-------+--------+-----------------------------+------------+---------+------------------------------------+------+----------+---------------------------------+
5 rows in set, 1 warning (0.00 sec)
- 09/23/10 3:24pm
Jarret Minkler says:Is that the explain of the original query or the select * from view ?
- 09/23/10 3:26pm
Lawrence Krubner says:Jarret, very good question. Here is the other EXPLAIN:
mysql> explain extended select * from chocolate_content_by_topic;
+----+-------------+------------+--------+-----------------------------+------------+---------+------------------------------------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+--------+-----------------------------+------------+---------+------------------------------------+------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4087 | 100.00 | |
| 2 | DERIVED | tx | ALL | ByID | NULL | NULL | NULL | 4287 | 100.00 | Using temporary; Using filesort |
| 2 | DERIVED | c | eq_ref | PRIMARY,Retr01,Retr02,Ref03 | PRIMARY | 11 | coco.tx.content_id,coco.tx.state | 1 | 100.00 | Using where |
| 2 | DERIVED | it | ref | issue_date | issue_date | 3 | coco.c.issue_date | 1 | 100.00 | Distinct |
| 2 | DERIVED | b | eq_ref | PRIMARY | PRIMARY | 4 | coco.c.reference_link_1 | 1 | 100.00 | Distinct |
| 2 | DERIVED | d | eq_ref | PRIMARY | PRIMARY | 4 | coco.c.content_type | 1 | 100.00 | Distinct |
+----+-------------+------------+--------+-----------------------------+------------+---------+------------------------------------+------+----------+---------------------------------+
6 rows in set, 1 warning (10.61 sec)
- 09/23/10 3:26pm
Lawrence Krubner says:The first explain that I posted was of your first suggested EXPLAIN
- 09/23/10 3:37pm
Jarret Minkler says:And lastly, a sample query against the view, also with its explain plan. :)
- 09/24/10 9:12am
Jarret Minkler says:I think at any rate you end up with a derived table, and therefore lose your indexes on the final query. I think I would try to avoid the VIEW in this scenario and create a separate concrete table that you need for this query.
Looking at the query, you don't really have any WHERE clause that would interfere. What I would do is query the chocolate_content c, get the data, then do the joins in code or as a stored procedure.
Most of your SELECT is from the "c" table, as well as your order by, so this view may be a bit of a waste, unless I am missing something.
Also, there is no need for the ORDER BY either since this is a view and there is no advantage to sort the data inside the view (same idea as the DISTINCT)
- 09/24/10 9:14am
Jarret Minkler says:"and create a separate concrete table that you need for this query. " <-- ignore this :)
I was thinking maybe you could use triggers to populate a new table, but that seems much to complex, and the stored procedure route seems best. - 09/24/10 10:33am
Lawrence Krubner says:"and therefore lose your indexes on the final query"
I think you are right. That is the main problem. - 09/24/10 10:57am
Jarret Minkler says:So, there really is no benefit for this view at all since it doesn't perform any transformations of the data in the select, so it seems like just a cosmetic ("I dont want to write all these joins in my code") kind of view.
What exactly do you use this view for? Is it an integral part of something, or just something that is run once in a while. How often is the data updated? Can you add date limiters? ie based on c.content_date, c.issue_date? What you need is a smaller subset of data to work with in "materialized" views (cron job that populates "real" tables daily with smaller subsets of the real tables)
- 09/24/10 11:02am
Lawrence Krubner says:"the stored procedure route seems best"
I am not sure I know what you are suggesting. Use a stored procedure to create the table? What is the speed advantage? I do not understand what this strategy entails. - 09/24/10 11:06am
Jarret Minkler says:Really all depends on how often data gets put in the tables, and what the query is used for on how to procede, and how many rows are in each table. Any insight as to any of this?
- 09/24/10 12:44pm
Lawrence Krubner says:Right now the above query gets back 4087 rows. This is a magazine, with new content going up almost daily.
Looking at the EXPLAIN, it was decided to remove the tables that only get back 1 row of data. A lot of that can be gotten via a separate query.
- 09/24/10 1:06pm
Jarret Minkler says:That's really what I was suggesting, something like
select fields_here from ( select * from chocolate_ where <put your where clause here that you would have queried against the view> ) c
-- put your smaller joins here
LEFT OUTER JOIN chocolate_issue it ON c.issue_date = it.issue_date
LEFT OUTER JOIN chocolate_blogs b ON c.reference_link_1 = b.blog_id
LEFT JOIN chocolate_content_type d on c.content_type=d.id
-- now move your order by here outside the inner query
ORDER BY c.content_date DESC , c.priority DESC , c.id DESC
You want to diminish all the rows you possibly can BEFORE you do the joins, distinct (if it's really required), and order by.
I assume here that the INNER query will only return 1-10 rows, since i assume this is for the web and sort of a blog. You WILL lose the indexes here, but there will be much fewer rows to work with to start and therefore will speed up the overall query.
- 09/24/10 1:13pm
Lawrence Krubner says:Jarret, do you know if the UNION syntax is any faster than JOIN syntax?
http://dev.mysql.com/doc/refman/5.0/en/union.html
And, also, to answer your question, we got rid of DISTINCT. I suspect it was copy-n-pasted from an earlier variation of this query. We get the same number of rows with DISTINCT as without it. - 09/24/10 1:16pm
Jarret Minkler says:Not sure how union would help you here, its something completely different. Union merges 2 queries together, while join merges tables.
- 09/23/10 2:09pm
-
Last edited:
09/23/10
3:13pmBill Hunt says:A couple ideas come to mind. First, you're missing indexes on chocolate_topic_xref.state and chocolate_content.reference_link_1.
Second, if the data is nonvolatile, or not changing too often, I'd create a table from the select statement (check out the INSERT ... SELECT syntax), which gives you a performance boost that you won't get from a view. Then you just update the table when the data changes. If this is only happening daily, you a huge performance boost - but you can't do that on constantly changing data.
Last, move any small relational data structures ( chocolate_topic_xref maybe? ) into separate queries and/or store them in memcache/etc to avoid the extra unnecessary load.- 09/23/10 3:23pm
Lawrence Krubner says:Bill, thanks much. For complicated reasons, we are trying to get rid of Memcache.
The data changes a little too often for us to dump it into some external table, though doing so from a cron script might be a possibility, if we can not find a direct way to fix the query itself.
- 09/23/10 3:23pm
-
Last edited:
09/24/10
1:04amRolando Edwards says:I know these was already suggested before.
1) You definitely want to ditch the DISTINCT
2) Add an index on chocolate_topic_xref
ALTER TABLE chocolate_topic_xref ADD INDEX state_content_id (state,content_id);
But here is new observation.
It looks like there is a lot of grief over getting the topic_id from the chocolate_topic_xref table since 4287 rows must be accessed just to get a single column in the join.
Maybe embedding topic_id in the chocolate_content table removes the need for JOINing the extra table. Here are the steps:
1) Create and index on state and content_id in the chocolate_topic_xref table.
ALTER TABLE chocolate_topic_xref ADD INDEX state_contentid_ndx (state,content_id);
2) Create and index on state and content_id in the chocolate_content table.
ALTER TABLE chocolate_content ADD INDEX state_id_ndx (state,id);
3) Create a topic_id column in chocolate_content table
ALTER TABLE chocolate_content ADD COLUMN topic_id int(11);
4) Populate chocolate_content.topic_id using an UPDATE JOIN.
UPDATE chocolate_content A INNER JOIN chocolate_topic_xref B ON A.state=B.state AND A.id=B.content_id SET A.topic_id = B.topic_id;
5) Drop the reference to chocolate_topic_xref from the query altogether.
CREATE VIEW chocolate_content_by_topic AS
SELECT c.id, c.textid, c.title, c.title_link, c.content_date, c.issue_date,
c.protected as chocolate_protected, c.allow_comments, c.show_comments, c.content_hint,
c.live_date, c.state,
c.author1, c.author2, c.author3, c.author4, c.author_str, c.subtitle,
c.abstract as chocolate_abstract, c.direct_url, c.hp_text,
c.imgpath1, c.imgpath2, c.imgpath3, c.imgpath4, c.imgpath5,
c.imgcaption1, c.imgcaption2, c.imgcaption3, c.imgcaption4, c.imgcaption5,
c.content_type, c.topic_str, c.reference_link_1, c.alt_info_1,
b.blog_name, b.blog_id,
it.issue_text_date,
c.topic_id as topic_id,
d.literal, d.alias, d.cdaobject, d.cdaaction, d.channel, d.adsite, d.adzone, d.indexable
FROM chocolate_content c
LEFT OUTER JOIN chocolate_issue it ON c.issue_date = it.issue_date
LEFT OUTER JOIN chocolate_blogs b ON c.reference_link_1 = b.blog_id
LEFT JOIN chocolate_content_type d on c.content_type=d.id
ORDER BY content_date DESC , priority DESC , id DESC;
This results in one less table to JOIN (and a troublesome table at that)
- 09/24/10 11:00am
Lawrence Krubner says:Ronaldo, I think the main problem is the one that Jarret mentioned - this is a view, so all of the Indexes are being ignored. This is one of the main flaws of MySql, I think.
- 09/24/10 4:26pm
Rolando Edwards says:In an earlier comment I saw this EXPLAIN plan
+----+-------------+-------+--------+-----------------------------+------------+---------+------------------------------------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+-----------------------------+------------+---------+------------------------------------+------+----------+---------------------------------+
| 1 | SIMPLE | tx | ALL | ByID | NULL | NULL | NULL | 4287 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY,Retr01,Retr02,Ref03 | PRIMARY | 11 | coco.tx.content_id,coco.tx.state | 1 | 100.00 | Using where |
| 1 | SIMPLE | it | ref | issue_date | issue_date | 3 | coco.c.issue_date | 1 | 100.00 | |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | coco.c.reference_link_1 | 1 | 100.00 | |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | coco.c.content_type | 1 | 100.00 | |
+----+-------------+-------+--------+-----------------------------+------------+---------+------------------------------------+------+----------+---------------------------------+
5 rows in set, 1 warning (0.00 sec)
Every level of the this EXPLAIN plan uses either a eq_ref or a ref (all returning one row). The notable exception is the first line which returns 4287 rows. The other parts of the query must be using indexes, but not the first part which does a full table scan right from the beginning.
If you remove the first part, which refers to the chocolate_topic_xref table, that is one less table involved in the cartesian-product style JOIN MySQL is doing.
To make matters worse, MySQL Views do not use WHERE clauses effeciently. WHERE clauses are used after the JOIN is complete. Maybe you can try this:
CREATE algorithm=merge VIEW ...
This will merge the WHERE clause conditions during the processing of the query, and not after. Not using this and just having
CREATE VIEW
will trigger total temp table creation for the view. Unfortunately, in some cases and depending on the query, MySQL may choose to ignore the 'algorithm=merge' (like when you do any aggregate SQL functions requiring a table scan for an unindexed column) and deteriorate into
algorithm=UNDEFINED or algorithm=TEMPTABLE
Try inserting 'algorithm=merge' between CREATE and VIEW and see it performance changes.
- 09/24/10 11:00am
-
Last edited:
09/25/10
12:11amWilliam Clark says:Lawrence: How many rows are in each table right now?
I don't think a union is the right way to go for this.
This question has expired.
Current status of this question: Completed
