Too much Joins performed without indexes MySQL

ler · 30 Янв 2015

Вот что выдает mysql tuner

Код:

# perl mysqltuner.pl

>>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[OK] Currently running supported MySQL version 5.5.38-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 80M (Tables: 602)
[--] Data in MRG_MYISAM tables: 868K (Tables: 1)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 39

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 0h 44m 22s (24M q [35.481 qps], 3M conn, TX: 16B, RX: 2B)
[--] Reads / Writes: 52% / 48%
[--] Total buffers: 640.0M global + 1.3M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.3G (68% of installed RAM)
[OK] Slow queries: 0% (355/24M)
[OK] Highest usage of available connections: 42% (214/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/30.4M
[OK] Key buffer hit rate: 100.0% (844M cached / 41K reads)
[OK] Query cache efficiency: 40.3% (2M cached / 6M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 5% (68K temp sorts / 1M sorts)
[!!] Joins performed without indexes: 8149
[OK] Temporary tables created on disk: 18% (288K on disk / 1M total)
[OK] Thread cache hit rate: 99% (346 created / 3M connections)
[!!] Table cache hit rate: 2% (1K open / 62K opened)
[OK] Open file limit used: 42% (1K/4K)
[OK] Table locks acquired immediately: 98% (10M immediate / 10M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    join_buffer_size (> 640.0K, or always use indexes with joins)
    table_cache (> 2048)

1. Просветите как узнать для каких полей нужны индексы в базе?
2. И будет ли базе плохо если я для всех полей сделаю индексы?
3. Кто хорошо знаком с базой, подскажите в каких полях индексы обязательны, а где вовсе не нужны

ler · 30 Янв 2015

Вот что в логах mysql-slow-query. Что не так с запросами?

Код:

# Query_time: 21.978230  Lock_time: 0.000172 Rows_sent: 0  Rows_examined: 18317238
use mytracker;
SET timestamp=1421393797;
INSERT INTO tmp_sync_topics
                SELECT
                    t.topic_id,
                    COUNT(p.post_id) AS total_posts,
                    MIN(p.post_id) AS topic_first_post_id,
                    MAX(p.post_id) AS topic_last_post_id,
                    MAX(p.post_time) AS topic_last_post_time,
                    IF(MAX(a.attach_id), 1, 0) AS topic_attachment
                FROM      bb_topics t
                LEFT JOIN bb_posts p ON(p.topic_id = t.topic_id)
                LEFT JOIN bb_attachments a ON(a.post_id = p.post_id)
                WHERE t.topic_status != 2
                   
                GROUP BY t.topic_id;
# Time: 150116 12:12:26

# Query_time: 10.002995  Lock_time: 0.000195 Rows_sent: 5  Rows_examined: 106810
SET timestamp=1421395946;
SELECT t.user_id, u.username, u.user_rank, SUM( t.u_up_total ) sum
        FROM bb_bt_users t
        LEFT JOIN bb_users u ON ( t.user_id = u.user_id )
        GROUP BY t.user_id
        ORDER BY sum DESC
        LIMIT 0 ,5;
# Time: 150117  3:43:16

# Query_time: 18.079576  Lock_time: 0.000236 Rows_sent: 0  Rows_examined: 18338883
SET timestamp=1421456429;
INSERT INTO tmp_sync_topics
                SELECT
                    t.topic_id,
                    COUNT(p.post_id) AS total_posts,
                    MIN(p.post_id) AS topic_first_post_id,
                    MAX(p.post_id) AS topic_last_post_id,
                    MAX(p.post_time) AS topic_last_post_time,
                    IF(MAX(a.attach_id), 1, 0) AS topic_attachment
                FROM      bb_topics t
                LEFT JOIN bb_posts p ON(p.topic_id = t.topic_id)
                LEFT JOIN bb_attachments a ON(a.post_id = p.post_id)
                WHERE t.topic_status != 2
                   
                GROUP BY t.topic_id;
# Time: 150117  6:48:27

=======================================================================================
# Query_time: 11.523787  Lock_time: 0.000358 Rows_sent: 27  Rows_examined: 32535
SET timestamp=1421932987;
select ct.id,
(select count(*) from ii_post ps where (ps.category REGEXP concat( '^(' ,ct.id, ',)' ) or ps.category REGEXP concat( '(,' ,ct.id, ',)' ) or ps.category REGEXP

concat( '(,' ,ct.id, ')$' ) or ps.category=ct.id) AND (DATE_FORMAT(date, '%Y%m%d')=CURDATE()) AND approve = '1') as new_,
(select count(*) from ii_post ps where (ps.category REGEXP concat( '^(' ,ct.id, ',)' ) or ps.category REGEXP concat( '(,' ,ct.id, ',)' ) or ps.category REGEXP

concat( '(,' ,ct.id, ')$' ) or ps.category=ct.id) AND approve = '1') as allnews_
from ii_category ct;

Exile · 30 Янв 2015

судя по тексту ошибок - в некоторых из стандартных таблиц движка нет индексов. Советовал бы дампы для начала сравнить в отношении индексов полей. ii_post - и в этой таблице явно индексов нет.

[!!] Total fragmented tables: 39

Оптимизацию всех таблиц еще сделать надо.

ler · 31 Янв 2015

таблица ii_post не с торрентпира, она не интересна.

Вот еще кое-что

Выборка 20 комментариев. 0.3сек многовато

Too much Joins performed without indexes MySQL

ler

ler

Exile

ler