LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (https://www.linuxquestions.org/questions/programming-9/)
-   -   Optimizing slow MySQL select construction (https://www.linuxquestions.org/questions/programming-9/optimizing-slow-mysql-select-construction-618791/)

ivanatora 02-05-2008 05:44 AM
Optimizing slow MySQL select construction
 
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.

Guttorm 02-05-2008 07:52 AM
Hi

The ouput of EXPLAIN SELECT shows that 3 tables are used, it will do a table scan on the table "feeds" but use the indeces on o (owners) and l (links). All the indeces you have created will make updates and inserts a lot slower, but it shouln't matter for the select.

The reason it will not use any index on feeds seems to be the clause. So it will basically do the following
- Table scan "feeds" and filter feeds.access = 'public'
- For all the rows, select from owner - o.owner_feed = feeds.id
- Then for all these rows, select from links - l.id = o.owned_link

That would probably make a lot of rows. Now sort all those by links.date and return the 30 first rows.

Using InnoDB will probably just make it slower. It seems what you want here is to find the 30 newest public links? Then, is there a way you could restructure it? One solution could be to put the public flag in the links table, then you could select the 30 newest ones in a CURSOR or a temporary table, and then join with the other two tables.

ivanatora 02-05-2008 12:11 PM
The LIMIT clause can change - it is made to return results by pages, so it won't be LIMIT 0,30 forever.
I understand it is normal to to ALL table scan for the table feeds, since the only condition there is to find a access = 'public' match and not use any indeces. I'm wondering how MySQL interprets ENUM field type and if it would benefit if I make an index there.

Maybe you are right, and I need to restructure the things a bit...


All times are GMT -5. The time now is 03:07 AM.