Geek Answers Handbook

What happened to this request? EXPLAIN looks great to me

I am browsing the application and trying to optimize some queries, and I am really struggling with some of them. Here is an example:

SELECT `Item`. *, `Source`. *, `Keyword`. *, `Author`. *
FROM `items` AS` Item`
JOIN `sources` AS` Source` ON (`Item`.`source_id` =` Source`.`id`)
JOIN `authors` AS` Author` ON (`Item`.`author_id` =` Author`.`id`)
JOIN `items_keywords` AS` ItemsKeyword` ON (` Item`.`id` = `ItemsKeyword`.`item_id`)
JOIN `keywords` AS` Keyword` ON (`Keyword`.`id` =` ItemsKeyword`.`keyword_id`)
JOIN `keywords_profiles` AS` KeywordsProfile` ON (`Keyword`.`id` =` KeywordsProfile`.`keyword_id`)
JOIN `profiles` AS` Profile` ON (`Profile`.`id` =` KeywordsProfile`.`profile_id`)
WHERE `KeywordsProfile`.`profile_id` IN (17)
GROUP BY `Item`.`id`
ORDER BY `Item`.`timestamp` DESC,` Item`.`id` DESC
LIMIT 0, 20;

It takes 10-30 seconds ... in the referenced tables there are about 500 thousand rows of the author and about 750 thousand elements and items_keywords rows. Everything else is less than 500 lines.

Here's the explanation explanation: http://img.skitch.com/20090220-fb52wd7jf58x41ikfxaws96xjn.jpg

EXPLAIN is relatively new to me, but I went this line by line and it all seems wonderful. Not sure what else I can do, as I have indexes to everything ... what am I missing?

The server on which it sits is only 256 pieces on slicehost, but nothing else works on it, and the processor is at 0% before it starts. And still he is distracted by this request. Any ideas?

EDIT: ; , , , , 0,1 . , , RESET QUERY CACHE , . , , 10-30 . MyISAM... , MySQL ?

EDIT 2: ... ... :

SELECT i.id
FROM items AS i
ORDER BY i.timestamp DESC, i.id DESC
LIMIT 0, 20;

5-6 , , 750 . . ORDER BY, . , , , :

SELECT i.id
FROM items AS i
JOIN items_keywords AS ik ON ( i.id = ik.item_id )
JOIN keywords AS k ON ( k.id = ik.keyword_id )
JOIN keywords_profiles AS kp ON ( k.id = kp.keyword_id )
WHERE kp.profile_id IN (139)
ORDER BY i.timestamp DESC
LIMIT 20;

10 ... ?

: items_keywords 1544, , profile_id . , ?

3: , :). ORDER BY, , temp/filesort . item.timestamp, - ? , - mysql, - ? , (source_id, author_id, timestamp ..)?

+5
13

, explain - . - filesort. order by , .

, , join, () , , .

, filesort, , explain ( ) - .

UPDATE # 1:

# 2 :

SELECT i.id
    FROM items AS i
    ORDER BY i.timestamp DESC, i.id DESC
    LIMIT 0, 20;

5-6 . . TIMESTAMP, ID , :

create index timestamp_id on items(timestamp,id);
select id from items order by timestamp desc,id desc limit 0,20;
select id from items order by timestamp,id limit 0,20;
select id from items order by timestamp desc,id desc;
select id from items order by timestamp,id;

(DB2 , ). - .

+3

, :

SELECT i.*, s.*, k.*, a.*
FROM items AS i
 JOIN sources AS s ON (i.source_id = s.id)
 JOIN authors AS a ON (i.author_id = a.id)
 JOIN items_keywords AS ik ON (i.id = ik.item_id)
 JOIN keywords AS k ON (k.id = ik.keyword_id)
WHERE k.id IN (SELECT kp.keyword_id
           FROM keywords_profiles AS kp
           WHERE kp.profile_id IN (17))
ORDER BY i.timestamp DESC, i.id DESC
LIMIT 0, 20;

, GROUP BY .

, i.id , , , profile_id 17.

filesort, EXPLAIN, , GROUP BY ORDER BY .

@ʞɔıu , , , .

+3

:

  • : CREATE INDEX ix_timestamp_id ON items (timestamp, id)

    • , id sources, authors keywords .

    • MySQL NESTED LOOP :

      EXPLAIN EXTENDED SELECT Item. *, Source. *, Keyword. *, Author. * FROM items AS Item FORCE INDEX FOR ORDER BY (ix_timestamp_id) JOIN items_keywords AS ItemsKeyword FORCE INDEX (ix_item_keyword) (Item. id= ItemsKeyword. item_id ItemsKeyword. keyword_id IN ( SELECT keyword_idFROM keywords_profiles AS KeywordsProfile FORCE INDEX (ix_keyword_profile) KeywordsProfile. profile_id= 17 ) ) JOIN sources AS Source FORCE INDEX () (Item. source_id= Source. id) JOIN authors AS Author FORCE INDEX () (Item. author_id= Author. id) JOIN keywords AS Keyword FORCE INDEX () (Keyword. id= ItemsKeyword. keyword_id) ORDER BY Item.timestamp DESC, Item.id DESC LIMIT 0, 20

, GROUP BY, JOIN PRIMARY KEY .

, NESTED LOOPS items .

:

1, 'PRIMARY', 'Item',         'index',  '', 'ix_timestamp_id', '12', '', 20, 2622845.00, ''
1, 'PRIMARY', 'Author',       'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.Item.author_id', 1, 100.00, ''
1, 'PRIMARY', 'Source',       'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.Item.source_id', 1, 100.00, ''
1, 'PRIMARY', 'ItemsKeyword', 'ref', 'PRIMARY', 'PRIMARY', '4', 'test.Item.id', 1, 100.00, 'Using where; Using index'
1, 'PRIMARY', 'Keyword',      'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.ItemsKeyword.keyword_id', 1, 100.00, ''
2, 'DEPENDENT SUBQUERY',      'KeywordsProfile', 'unique_subquery', 'PRIMARY', 'PRIMARY', '8', 'func,const', 1, 100.00, 'Using index; Using where'

,

20 rows fetched in 0,0038s (0,0019s)

500k items, 600k items_keywords, 512 keywords 512 keywords_profiles ( 17).

+1

, , , . phpmyadmin, chekbox, , , , mysql. , , phpmyadmin, .

0

GROUP BY? SELECT , GROUP BY .

0

, :

  • , . ( ) , , -.
  • , filesort . , mysql, null, .
  • (timestamp, id) (id, timestamp). - ( ).
  • ? - ? ( ) , . , .
  • , , mysql , , . , , , , , , .
  • , , , , 0,1 . , — SQL_NO_CACHE SELECT, .
  • MyISAM... , MySQL awhile — MyISAM , . Innodb, .
0

, - - ? EXPLAIN , ItemsKeyword 1544 . , 10-15 (, , ). , . , 256 , , , , .

, , I/O , - pidstat -d 1 iostat 1 .

EDIT: , (ItemsKeyword. item_id, ItemsKeyword. keyword_id), , , ItemsKeyword.

0

MySQL , . , , .

, MySQL . , , , . , MySQL , . -, , MySQL ( temp - mysqlperformanceblogs.com ). :

  • , .

-, - .

, - . , , ( , ), . , , .

!

0

, , ,

WHERE kp.profile_id IN (139)


WHERE kp.profile_id = 139
0

:

SELECT i.id
FROM ((items AS i
   INNER JOIN items_keywords AS ik ON ( i.id = ik.item_id ))
   INNER JOIN keywords AS k ON ( k.id = ik.keyword_id ))
   INNER JOIN keywords_profiles AS kp ON ( k.id = kp.keyword_id AND kp.profile_id = 139)
ORDER BY i.timestamp DESC
LIMIT 20;
0

pastie.org :

  • items.source_id int(4) sources.id int(16)
  • items.id int(16) - itemskeywords.item_id int(11)

,

, , , INT, MySQL 6.0 :

, , MySQL , MySQL , .

, , , . , .

,

, , , , int(4) " ", " -2147483648 2147482647, 4 - , ,

authors.refreshed int(20) items.timestamp int(30) , 10 int. a bigint 20 . , , int(4) varchar(4)?

0

. , ...

, .

: , ...

0

, , where. 500 . , + , . JOINS LEFT JOINS USING().

-1

More articles:


All Articles