MySQL Forums
Forum List  »  Performance

Re: Utilize maximum system resources for better performance
Posted by: Rick James
Date: August 08, 2015 11:49AM

Use utf8 if you need it; don't use it you don't. In particular, you _probably_ should not use it for `hash`. CHAR(40) in utf8 always takes 120 bytes; in latin1 or ascii, it takes only 40 bytes.

Do you have other tables that JOIN to this one via `record_num`?

Is the 6.5M rows a complete replacement? (In which case, I will give one recommendation.)
Or is it some new, some changed data? (In which case, another.)
Are you using LOAD DATA? INSERT one row at a time? Other?

The Query Cache is useful only for relatively static tables. Perhaps this is static except for once a day?

SELECT count(*) as total_results FROM content WHERE content.enabled = 1 ;
requires scanning part of one of the indexes, as you can see from EXPLAIN (e_v).

Oh, pagination. My blog on that covers many of your problems:
http://mysql.rjweb.org/doc.php/pagination
WHERE enabled = 1 ORDER BY upload_date DESC LIMIT 0,25
is an excellent candidate for what my blog suggests.

There are over 6M 'enabled' records. Simply recompute that daily, then format the response as "> 6 million", not "exactly 6,686,811".

SELECT count(*) as total_results
    FROM content
    WHERE content.enabled = 1
      AND category BETWEEN 400 AND 420
      AND upload_date >= '2014-08-08'
      AND verified = 1
[/c0de]
can be sped up slightly by putting the "= const" fields first.  That is:
INDEX(enabled, verified, category, upload_date)
(The first two columns can be in either order.  It's unclear what the better order for the last two is.)
If you turn the category into an IN, _and_ your version has MRR, you _might_ get more speed.

See my indexing cookbook:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Queries 1,2,3 from pt-query-digest smell like manually run things, not production queries.  Some involve reading 6M rows, which seems unlikely.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Utilize maximum system resources for better performance
1023
August 08, 2015 11:49AM


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.