MySQL Forums
Forum List  »  Performance

Re: I have queries that sporadically take a very long time
Posted by: Rick James
Date: February 22, 2009 12:44PM

Alas, it's the classic query.
http://forums.mysql.com/read.php?24,248637,248866
http://forums.mysql.com/read.php?10,247761,247850

More specifically to your case...

Details missing:
* MyISAM or InnoDB? (would have been visible in SHOW CREATE TABLE; otherwise your CREATE is fine.)
* Sizes of the tables? (SHOW TABLE STATUS)

Schema comments:

* "int(4)": Use TINYINT, SMALLINT, etc. INT(N) always takes 4 bytes, regardless of N; it displays N digits. TINYINT or SMALLINT would take only 1 or 2 bytes.

* searchEngines_keywords text and tags varchar(255) -- consider FULLTEXT index.

* Move TEXT fields into a parallel table. Why? When doing the query in question, it has to step over these large fields (and/or gather them up for eventual display). This takes effort. Better to find the rows first, then JOIN for TEXT fields if any are needed.

Shrinking the data -> improved cachability -> improved speed (and maybe fewer 30-second glitches??)

Most of the WHERE clauses cannot use any index.
* FIND_IN_SET has to look at each row.
* ...flag = 0/1 has such poor cardinality that it tends to be filtered last.

The final ORDER BY & LIMIT cannot make use of any index, unless _all_ of the WHERE clause is in the same one-table index. Ok, this is not totally true; in some cases, it might decide to use an index on sites_news.publication_date_time.

[core]
AND sites_news.enabled = 1
AND sites_news_categories.enabled = 1
AND sites_news.frn_user_id != 0
AND core_system.community_member = 1
[/core]
These smell like flags of things that you don't want to display. Get them out of the table. Move items failing these tests to a similar table. This will simplify the query for displayable stuff. When you need to see both sets of stuff, do a UNION. (This recommendation, by itself, won't fix much. But without it, you may never achieve a speedy query.)

Note in the EXPLAIN how it picked core_systems as the first table. And it did a table scan. It might help (some) to add
INDEX (frn_language_id, community_member)
But I suspect everything you have (today) is language=142 and most are community_members. In which case it would skip the index and still do a table scan. :(

OR is another killer
   FIND_IN_SET('sometag', sites_news.tags)
OR sites_news_categories.name = 'mycategory'
Turn into UNION (see below)

Contrary to popular wisdom, it is not "filesort" that is the problem, it is the shear bulk of stuff carried around by the sort (file or otherwise) that makes it slow. EXPLAIN estimates 19193 * 1 * 3 * 1 rows to haul around before sorting and delivering 400. The slowlog confirms this with 119637 -- though can be divided by some number between 1 and 4, since 4 tables are probed. As it walks thru the 4 tables, it is pruning its original 19K rows based on WHERE clauses that come into effect.

Perhaps you could speed things up with
SELECT (most of the stuff)
   FROM (existing list of tables),
       ( SELECT id FROM sites_news WHERE FIND_IN_SET('sometag', tags)
         UNION
         SELECT id FROM sites_news_categories WHERE name = 'mycategory' ) u
   WHERE a.id = u.id
     AND (the rest of the WHERE stuff)
   ORDER BY ...
   LIMIT ...
The goal if this change is two-fold:
* Turn the OR into UNION;
* Trick it into seriously shrinking the list of candidates as fast as possible (FULLTEXT would be even faster).

To further enhance the above restructure:
* Split out tags and id into a separate (parallel) table so that the size of the table scan is less.
* Add to sites_news_categories
INDEX(name, id)
so it can run "using index". -- very efficient in your case.

Tuning -- Available RAM; values of key_buffer_size or innodb_buffer_pool_size?

I bring up all these issues because I have seen too many news-like sites get slower and slower, and finally fall down -- even without the 30-sec glitch. Your 1.2s will become 2.4s when you have twice the data. Or it could degrade even faster if you get into I/O thrashing. If you had less RAM for caching, you would already be at 30s for _all_ such queries!

Sorry, I have not spotted the cause of the 30-sec glitch. MyISAM/InnoDB and the tuning values might lead to some other thoughts. They do cache management radically differently.

Oh, a possibility -- Did you do anything that might have flushed cache just before the 30sec? With everything cached, 1.2s is reasonable for hitting 120K rows; with nothing cached, 30s is likely.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: I have queries that sporadically take a very long time
2537
February 22, 2009 12:44PM


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.