Databases
StackOverflow2013
Postsdbo
POHow can I improve this query depending on the explain results
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I improve this query depending on the explain results
    primarykey
    data
    text
    <p>I have The Following query:</p> <pre class="lang-sql prettyprint-override"><code>SELECT DISTINCT f1.match_static_id, f2.comments_no, f2.maxtimestamp, users.username, users.id, matches_of_comments.localteam_name, matches_of_comments.visitorteam_name, matches_of_comments.localteam_goals, matches_of_comments.visitorteam_goals, matches_of_comments.match_status, new_iddaa.iddaa_code FROM comments AS f1 INNER JOIN ( SELECT match_static_id, MAX( TIMESTAMP ) maxtimestamp, COUNT( match_static_id ) AS comments_no FROM comments GROUP BY match_static_id ) AS f2 ON f1.match_static_id = f2.match_static_id AND f1.timestamp = f2.maxtimestamp INNER JOIN users ON users.id = f1.user_id INNER JOIN matches_of_comments ON matches_of_comments.match_id = f2.match_static_id LEFT JOIN new_iddaa ON new_iddaa.match_id = matches_of_comments.match_id WHERE matches_of_comments.flag =1 ORDER BY f2.maxtimestamp DESC </code></pre> <p>This is the EXPLAIN plan for that query : </p> <pre><code>+----+-------------+---------------------+--------+-----------------------------------+-----------+---------+------------------------------------------+-------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +----+-------------+---------------------+--------+-----------------------------------+-----------+---------+------------------------------------------+-------+------------------------------------------------+ | 1 | PRIMARY | &lt;derived2&gt; | ALL | NULL | NULL | NULL | NULL | 542 | Using temporary; Using filesort | | 1 | PRIMARY | f1 | ref | timestamp,match_static_id,user_id | timestamp | 4 | f2.maxtimestamp | 1 | Using where | | 1 | PRIMARY | users | eq_ref | PRIMARY | PRIMARY | 4 | skormix_db1.f1.user_id | 1 | | | 1 | PRIMARY | matches_of_comments | ALL | match_id | NULL | NULL | NULL | 20873 | Range checked for each record (index map: 0x8) | | 1 | PRIMARY | new_iddaa | ref | match_id | match_id | 4 | skormix_db1.matches_of_comments.match_id | 1 | | | 2 | DERIVED | comments | ALL | NULL | NULL | NULL | NULL | 933 | Using temporary; Using filesort | +----+-------------+---------------------+--------+-----------------------------------+-----------+---------+------------------------------------------+-------+------------------------------------------------+ </code></pre> <p>I use this query to get a match information if this match has at least one comment.<br> I get the names of the teams , the code (iddaa code), the number of comments, the timstamp of the last commrnt, the author of the last comment.<br> I have a big database and it is expected to be larger in the next few monthes and I am very new with the MySQL queries and I want to be sure that I am using the optimize queries from the beginning so I want to know how to read this explain information to make the query better and faster.</p> <p>I see that there are a lot of places in the table that does not use the indexes eventhough i built them.<br> I also see derived in the table column and I do not know how to make this query more fast and how to get rid of the filesort because I can not make indexes for the derived queries??</p> <p>I write down the structure of the using tables in the query with the indexes (keys) and I hope to get some hints or simple answers for my questions , thanks in advance .</p> <p>Comments (f1) table structure is :</p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE `comments` ( `id` int(25) NOT NULL AUTO_INCREMENT, `comments` text COLLATE utf8_unicode_ci NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `date` date NOT NULL, `time` time NOT NULL, `match_static_id` int(25) NOT NULL, `ip` varchar(255) CHARACTER SET latin1 NOT NULL, `comments_yes_or_no` int(25) NOT NULL, `user_id` int(25) NOT NULL, PRIMARY KEY (`id`), KEY `timestamp` (`timestamp`), KEY `match_static_id` (`match_static_id`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM AUTO_INCREMENT=935 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci </code></pre> <p>users table structure is:</p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE `users` ( `id` int(25) NOT NULL AUTO_INCREMENT, `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `gender` int(25) NOT NULL, `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `avatar` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `alert` int(25) NOT NULL, `daily_tahmin` int(25) NOT NULL, `monthly_tahmin` int(25) NOT NULL, `admin` int(25) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=995 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci </code></pre> <p>matches_of_comments_ structure is :</p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE `matches_of_comments` ( `id` int(25) NOT NULL AUTO_INCREMENT, `en_tournament_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `tournament_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `country_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `match_status` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `match_time` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `match_date` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `static_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `fix_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `match_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `localteam_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `localteam_goals` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `localteam_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `visitorteam_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `visitorteam_goals` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `visitorteam_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `ht_score` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `flag` int(25) NOT NULL, PRIMARY KEY (`id`), KEY `match_status` (`match_status`), KEY `match_date` (`match_date`), KEY `match_id` (`match_id`), KEY `localteam_id` (`localteam_id`), KEY `visitorteam_id` (`visitorteam_id`), KEY `flag` (`flag`) ) ENGINE=MyISAM AUTO_INCREMENT=237790 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci </code></pre> <p>new_iddaa table structure is :</p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE `new_iddaa` ( `id` int(25) NOT NULL AUTO_INCREMENT, `match_id` int(25) NOT NULL, `iddaa_code` int(25) NOT NULL, `tv_channel` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `skormix_tahmin` varchar(255) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`id`), KEY `match_id` (`match_id`) ) ENGINE=MyISAM AUTO_INCREMENT=8191 DEFAULT CHARSET=latin1 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSchmitzIT
    1. USBasel
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow can I improve this query depending on the explain results
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow can I improve this query depending on the explain results
      1. USBasel
      1. VTBountyStart
    3. VO
      singulars
      1. POHow can I improve this query depending on the explain results
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COCan you add some sample data into this sqlfiddle? http://sqlfiddle.com/#!2/9f4fb7 Then it will be helpful for others to check.
      singulars
      1. POHow can I improve this query depending on the explain results
      1. USDamodaran
    2. COFirst, you need an index on `(match_static_id, timestamp)` for the derived table to be efficient.
      singulars
      1. POHow can I improve this query depending on the explain results
      1. USypercubeᵀᴹ
    3. COThe column type in `matches_of_comments` for `match_id` does not match the other two table with that column. That is costing you heavily when you try to join on that index.
      singulars
      1. POHow can I improve this query depending on the explain results
      1. USWillem Renzema
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload