select t.*, (SELECT count(*) FROM torgall_comments WHERE message_id = t.id) comments_count 
from (
    SELECT d.id
    FROM torgall_db as d 
    WHERE d.status = 1 and d.catfull like 'Zhivotnye-pticy-ryby-tovary-dlya-zhivotnyh%' 
    ORDER by d.catfull desc, d.raised desc, d.date_add desc, d.id desc 
    LIMIT 78000, 30
) tt
left join torgall_db t on t.id = tt.id

SHOW PROFILE Result (ordered by duration)
state                           duration (summed) in sec    percentage
Sending data                    0.14633                     99.30103
freeing items                   0.00041                     0.27823
statistics                      0.00016                     0.10858
Opening tables                  0.00011                     0.07465
checking query cache for query  0.00010                     0.06786
init                            0.00005                     0.03393
executing                       0.00004                     0.02714
cleaning up                     0.00004                     0.02714
preparing                       0.00004                     0.02714
optimizing                      0.00003                     0.02036
checking permissions            0.00001                     0.00679
starting                        0.00001                     0.00679
removing tmp table              0.00001                     0.00679
closing tables                  0.00001                     0.00679
System lock                     0.00001                     0.00679
Total                           0.14736                     100.00000


Change Of STATUS VARIABLES Due To Execution Of Query
variable                            value   description
Bytes_received                      265     Bytes sent from the client to the server
Bytes_sent                          9272    Bytes sent from the server to the client
Com_select                          1       Number of SELECT statements that have been executed
Created_tmp_tables                  1       Count of temporary tables created in memory
Handler_commit                      1       Number of internal commit statements
Handler_read_key                    61      Number of requests to read a row based on a key
Handler_read_prev                   78029   Number of requests to read the previous row in key order. Also indicates that ORDER BY ... DESC was optimized
Handler_read_rnd_next               31      Number of requests to read the next row in the data file. A high value indicates that a full table scan was required
Handler_write                       30      Number of requests to insert a row in a table
Innodb_buffer_pool_read_requests#   10809   The number of logical read requests Innodb has done
Innodb_rows_read#                   78060   The number of rows read from Innodb tables
Qcache_not_cached*                  353     Number of queries that were not cached by the Query Cache. They are not cacheable or was not cached due to the query_cache_type setting
Questions                           1       Number of statements executed by the server
Select_range                        1       Tables were read from the disk only in the necessary places to satisfy a limited range of conditions
Select_scan                         1       Number of full table scans of the first table in the query
Table_locks_immediate               3       The number of requests for table locks that could be granted immediately

EXPLAIN Result
id  select_type         table             type    possible_keys   key      key_len  ref    rows    Extra
1   PRIMARY             <derived3>        ALL     (NULL)          (NULL)   (NULL)  (NULL)  78030   (NULL)
1   PRIMARY             t                 eq_ref  PRIMARY         PRIMARY  4       tt.id   1       (NULL)
3   DERIVED             d                 range   idx             idx      767     (NULL)  124135  Using where; Using index
2   DEPENDENT SUBQUERY  torgall_comments  ref     idx_m           idx_m    4       t.t.id  1       Using index

Общее время исполнение ~0.16 сек

_____________
Рекламка / ad.pesow.com Хрень / mr-1.ru