Hello,
For two days I'm trying to optimize a SELECT query that don't use the proper table indeces.
Here are the 3 tables definitions:
Code:
mysql> DESCRIBE links;
+-------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------------------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| url | varchar(255) | | | | |
| title | varchar(255) | | | | |
| description | text | | | | |
| hash | varchar(32) | | MUL | | |
| date | datetime | | MUL | 0000-00-00 00:00:00 | |
+-------------+--------------+------+-----+---------------------+----------------+
mysql> SHOW INDEX FROM links;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| links | 0 | PRIMARY | 1 | id | A | 230738 | NULL | NULL | | BTREE | |
| links | 1 | hash | 1 | hash | A | 230738 | NULL | NULL | | BTREE | |
| links | 1 | date | 1 | date | A | 76912 | NULL | NULL | | BTREE | |
| links | 1 | id_date | 1 | id | A | 230738 | NULL | NULL | | BTREE | |
| links | 1 | id_date | 2 | date | A | 230738 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> DESCRIBE feeds;
+-----------------+--------------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------------------+------+-----+------------+----------------+
| id | int(4) | | PRI | NULL | auto_increment |
| title | varchar(100) | | | | |
| description | text | | | | |
| url_mainsite | varchar(50) | | | | |
| url_feed | varchar(150) | | UNI | | |
| category_id | smallint(6) | | MUL | 0 | |
| keyword | varchar(255) | | | | |
| country_id | smallint(6) | | MUL | 0 | |
| language | smallint(6) | | MUL | 0 | |
| hash | varchar(32) | | MUL | | |
| userID | int(11) | | | 0 | |
| access | enum('public','private') | | | public | |
| comment | tinytext | YES | | NULL | |
| date_added | date | | | 0000-00-00 | |
| date_last_check | datetime | YES | | NULL | |
| ip | varchar(16) | | | | |
| is_active | enum('Y','N') | | | Y | |
| time_taken | smallint(6) | | | -1 | |
+-----------------+--------------------------+------+-----+------------+----------------+
mysql> SHOW INDEX FROM feeds;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| feeds | 0 | PRIMARY | 1 | id | A | 965 | NULL | NULL | | BTREE | |
| feeds | 0 | url_feed | 1 | url_feed | A | 965 | NULL | NULL | | BTREE | |
| feeds | 1 | hash | 1 | hash | A | 965 | NULL | NULL | | BTREE | |
| feeds | 1 | language | 1 | language | A | 9 | NULL | NULL | | BTREE | |
| feeds | 1 | country_id | 1 | country_id | A | 7 | NULL | NULL | | BTREE | |
| feeds | 1 | category_id | 1 | category_id | A | 80 | NULL | NULL | | BTREE | |
| feeds | 1 | id_access | 1 | id | A | 965 | NULL | NULL | | BTREE | |
| feeds | 1 | id_access | 2 | access | A | 965 | NULL | NULL | | BTREE | |
| feeds | 1 | category_id_2 | 1 | category_id | A | 80 | NULL | NULL | | BTREE | |
| feeds | 1 | category_id_2 | 2 | country_id | A | 160 | NULL | NULL | | BTREE | |
| feeds | 1 | category_id_2 | 3 | language | A | 193 | NULL | NULL | | BTREE | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> DESCRIBE owners;
+------------+------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+------------+-------+
| owner_feed | bigint(20) | | MUL | 0 | |
| owned_link | bigint(20) | | MUL | 0 | |
| date | date | | | 0000-00-00 | |
+------------+------------+------+-----+------------+-------+
mysql> SHOW INDEX FROM owners;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| owners | 1 | owner_feed | 1 | owner_feed | A | 949 | NULL | NULL | | BTREE | |
| owners | 1 | owned_link | 1 | owned_link | A | 177641 | NULL | NULL | | BTREE | |
| owners | 1 | owner_feed_2 | 1 | owner_feed | A | 949 | NULL | NULL | | BTREE | |
| owners | 1 | owner_feed_2 | 2 | owned_link | A | 177641 | NULL | NULL | | BTREE | |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
I pumped a lot of indeces hoping it will take some of them, but with no success...
Here is the query itself:
Code:
SELECT l.id as lid, l.date as ldate, l.title as ltitle, l.description as ldesc, feeds.id as fid, feeds.title as ftitle, feeds.description as fdesc, feeds.url_feed as rss_feed, feeds.category_id as cat, feeds.country_id as country, feeds.language as lang
FROM links l, owners o, feeds
WHERE l.id = o.owned_link AND o.owner_feed = feeds.id AND feeds.access = 'public'
ORDER by l.date DESC LIMIT 0,30
And here is what I get from EXPLAIN SELECT:
Code:
mysql> EXPLAIN SELECT l.id as lid, l.date as ldate, l.title as ltitle, l.description as ldesc, feeds.id as fid, feeds.title as ftitle, feeds.description as fdesc, feeds.url_feed as rss_feed, feeds.category_id as cat, feeds.country_id as country, feeds.language as lang FROM links l, owners o, feeds WHERE l.id = o.owned_link AND o.owner_feed = feeds.id AND feeds.access = 'public' ORDER by l.date DESC LIMIT 0,30;
+----+-------------+-------+--------+------------------------------------+--------------+---------+-----------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------------+--------------+---------+-----------------------------+------+----------------------------------------------+
| 1 | SIMPLE | feeds | ALL | PRIMARY,id_access | NULL | NULL | NULL | 724 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | o | ref | owner_feed,owned_link,owner_feed_2 | owner_feed_2 | 8 | ivan_rss2mysql.feeds.id | 187 | Using where; Using index |
| 1 | SIMPLE | l | eq_ref | PRIMARY,id_date | PRIMARY | 4 | ivan_rss2mysql.o.owned_link | 1 | Using where |
+----+-------------+-------+--------+------------------------------------+--------------+---------+-----------------------------+------+----------------------------------------------+
There are so many possible keys and none being used.
The storage engine is MyISAM, I'm thinking of changing it to InnoDB and using foreign keys as inter-tables indeces. Will it speed it up?
If you have other ideas, I'd be glad to hear.
