Re: Help with optimize SELECT

[Alexander Kosykh]

CREATE TABLE `ip_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_group_id` int(11) NOT NULL,
  `ip` int(11) NOT NULL DEFAULT '0',
  `mask` int(11) NOT NULL DEFAULT '-1',
  `uname` varchar(64) NOT NULL,
  `upass` varchar(64) NOT NULL,
  `mac` varchar(32) NOT NULL DEFAULT '',
  `allowed_cid` varchar(32) NOT NULL DEFAULT '',
  `ip_type` int(11) NOT NULL DEFAULT '0',
  `router_id` int(11) NOT NULL DEFAULT '0',
  `create_date` int(11) NOT NULL DEFAULT '0',
  `delete_date` int(11) NOT NULL DEFAULT '0',
  `is_deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_264d0ebccce540730e5da3d221dfa3d9` (`ip_group_id`,`ip_type`,`is_deleted`),
  KEY `uname` (`uname`)
) ENGINE=InnoDB AUTO_INCREMENT=13142 DEFAULT CHARSET=utf8 |

CREATE TABLE `iptraffic_service_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_group_id` int(11) NOT NULL,
  `downloaded_id` int(11) NOT NULL,
  `is_deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_55232d39c6b30c255118489783ed36e2` (`id`,`ip_group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=41272 DEFAULT CHARSET=utf8 |

CREATE TABLE `service_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `account_id` int(11) NOT NULL,
  `service_id` int(11) NOT NULL,
  `tariff_link_id` int(11) NOT NULL,
  `is_deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_4ac9ffe9ebe0322b6fb7d34f3e346a6b` (`account_id`,`tariff_link_id`,`id`,`is_deleted`,`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=41273 DEFAULT CHARSET=utf8 |

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `balance` double NOT NULL DEFAULT '0',
  `account_name` varchar(255) NOT NULL DEFAULT '',
  `credit` double NOT NULL DEFAULT '0',
  `flags` int(11) NOT NULL DEFAULT '0',
  `is_blocked` int(11) NOT NULL DEFAULT '0',
  `vat_rate` double NOT NULL DEFAULT '0',
  `sale_tax_rate` double NOT NULL DEFAULT '0',
  `int_status` int(11) NOT NULL DEFAULT '0',
  `block_recalc_abon` int(11) NOT NULL DEFAULT '0',
  `block_recalc_prepaid` int(11) NOT NULL DEFAULT '0',
  `unlimited` int(11) NOT NULL DEFAULT '0',
  `is_deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_33a254f7035d715e438372133be258b5` (`id`,`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=10556 DEFAULT CHARSET=utf8 |

CREATE TABLE `radius_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_id` int(11) NOT NULL,
  `owner_type` int(11) NOT NULL,
  `vendor` int(11) NOT NULL DEFAULT '0',
  `attr` int(11) NOT NULL,
  `value` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8 |

SHOW TABLE STATUS LIKE 'ip_groups';
+-----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free  | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| ip_groups | InnoDB |      10 | Compact    | 12577 |            126 |     1589248 |               0 |      1966080 | 4037017600 |          13142 | 2009-04-10 10:58:54 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+-----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.02 sec)

SHOW TABLE STATUS LIKE 'iptraffic_service_links';
+-------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name                    | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free  | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| iptraffic_service_links | InnoDB |      10 | Compact    | 19852 |             80 |     1589248 |               0 |       360448 | 4037017600 |          41272 | 2009-03-24 10:57:43 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+-------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.05 sec)

SHOW TABLE STATUS LIKE 'service_links';
+---------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name          | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free  | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+---------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| service_links | InnoDB |      10 | Compact    | 41530 |             63 |     2637824 |               0 |      2555904 | 4037017600 |          41273 | 2009-03-24 10:57:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+---------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.05 sec)

SHOW TABLE STATUS LIKE 'accounts';
+----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free  | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| accounts | InnoDB |      10 | Compact    | 11649 |            136 |     1589248 |               0 |       180224 | 4037017600 |          10557 | 2009-03-24 10:57:42 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

SHOW TABLE STATUS LIKE 'radius_data';
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free  | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| radius_data | InnoDB |      10 | Compact    |   29 |            564 |       16384 |               0 |            0 | 4037017600 |             37 | 2009-01-11 23:43:54 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.02 sec)


explain SELECT ip_groups.ip_group_id, ip_groups.uname, 'Password', ip_groups.upass, ':='
    -> FROM ip_groups
    -> LEFT JOIN iptraffic_service_links ON(ip_groups.ip_group_id=iptraffic_service_links.ip_group_id)
    -> LEFT JOIN service_links ON (iptraffic_service_links.id=service_links.id)
    -> LEFT JOIN accounts ON (accounts.id=service_links.account_id)
    -> WHERE ip_groups.uname='user_test'
    -> AND ip_groups.is_deleted='0'
    -> AND iptraffic_service_links.is_deleted='0'
    -> AND service_links.is_deleted='0'
    -> AND accounts.is_deleted='0';
+----+-------------+-------------------------+--------+------------------------------------------------+----------------------------------------+---------+-----------------------+-------+---------------------------------------------+
| id | select_type | table                   | type   | possible_keys                                  | key                                    | key_len | ref                   | rows  | Extra                                       |
+----+-------------+-------------------------+--------+------------------------------------------------+----------------------------------------+---------+-----------------------+-------+---------------------------------------------+
|  1 | SIMPLE      | ip_groups               | ref    | index_264d0ebccce540730e5da3d221dfa3d9,uname   | uname                                  | 194     | const                 |     1 | Using where                                 |
|  1 | SIMPLE      | accounts                | index  | PRIMARY,index_33a254f7035d715e438372133be258b5 | index_33a254f7035d715e438372133be258b5 | 8       | NULL                  | 11649 | Using where; Using index; Using join buffer |
|  1 | SIMPLE      | service_links           | ref    | PRIMARY,index_4ac9ffe9ebe0322b6fb7d34f3e346a6b | index_4ac9ffe9ebe0322b6fb7d34f3e346a6b | 4       | UTM5.accounts.id      |     2 | Using where; Using index                    |
|  1 | SIMPLE      | iptraffic_service_links | eq_ref | PRIMARY,index_55232d39c6b30c255118489783ed36e2 | PRIMARY                                | 4       | UTM5.service_links.id |     1 | Using where                                 |
+----+-------------+-------------------------+--------+------------------------------------------------+----------------------------------------+---------+-----------------------+-------+---------------------------------------------+
4 rows in set (0.00 sec)


explain SELECT inet_ntoa(ip_groups.ip & 0xFFFFFFFF) AS 'ip', unhex(radius_data.value) AS 'attr', accounts.is_deleted, accounts.is_blocked
    -> FROM ip_groups
    -> LEFT JOIN iptraffic_service_links ON (ip_groups.ip_group_id=iptraffic_service_links.ip_group_id)
    -> LEFT JOIN service_links ON (iptraffic_service_links.id=service_links.id)
    -> LEFT JOIN radius_data ON (service_links.service_id=radius_data.owner_id)
    -> LEFT JOIN accounts ON (service_links.account_id=accounts.id)
    -> WHERE ip_groups.uname='user_test'
    -> AND ip_groups.is_deleted='0'
    -> AND iptraffic_service_links.is_deleted='0'
    -> AND service_links.is_deleted='0'
    -> AND accounts.is_deleted='0';
+----+-------------+-------------------------+--------+------------------------------------------------+---------+---------+---------------------------------+-------+--------------------------------+
| id | select_type | table                   | type   | possible_keys                                  | key     | key_len | ref                             | rows  | Extra                          |
+----+-------------+-------------------------+--------+------------------------------------------------+---------+---------+---------------------------------+-------+--------------------------------+
|  1 | SIMPLE      | ip_groups               | ref    | index_264d0ebccce540730e5da3d221dfa3d9,uname   | uname   | 194     | const                           |     1 | Using where                    |
|  1 | SIMPLE      | iptraffic_service_links | ALL    | PRIMARY,index_55232d39c6b30c255118489783ed36e2 | NULL    | NULL    | NULL                            | 19853 | Using where; Using join buffer |
|  1 | SIMPLE      | service_links           | eq_ref | PRIMARY,index_4ac9ffe9ebe0322b6fb7d34f3e346a6b | PRIMARY | 4       | UTM5.iptraffic_service_links.id |     1 | Using where                    |
|  1 | SIMPLE      | radius_data             | ALL    | NULL                                           | NULL    | NULL    | NULL                            |    29 |                                |
|  1 | SIMPLE      | accounts                | eq_ref | PRIMARY,index_33a254f7035d715e438372133be258b5 | PRIMARY | 4       | UTM5.service_links.account_id   |     1 | Using where                    |
+----+-------------+-------------------------+--------+------------------------------------------------+---------+---------+---------------------------------+-------+--------------------------------+
5 rows in set (0.00 sec)

I'd made stored procedures cut long query to some simple. Here they are

CREATE DEFINER=`radius`@`localhost` PROCEDURE `auth_check`(IN un VARCHAR(64))
BEGIN

DECLARE ig_id INT(11);

DECLARE isl_id INT(11);

DECLARE a_id INT(11);

DECLARE s_id INT(11);

DECLARE cnt INT(11);

DECLARE up VARCHAR(64);

SELECT ip_group_id,upass INTO ig_id,up FROM ip_groups WHERE is_deleted='0' AND uname=UN;

SELECT id INTO isl_id FROM iptraffic_service_links WHERE is_deleted='0' AND ip_group_id=ig_id;

SELECT account_id,service_id INTO a_id,s_id FROM service_links WHERE is_deleted='0' AND id=isl_id;

SELECT COUNT(*) INTO cnt FROM accounts WHERE is_deleted='0' AND id=a_id;

IF cnt>0 THEN

SELECT ig_id AS 'id', un AS 'UserName', 'Cleartext-Password' AS 'Attribute', up AS 'Value', ':=' AS 'op';

END IF;

END;

CREATE DEFINER=`radius`@`localhost` PROCEDURE `rad_reply`(IN un VARCHAR(64))
BEGIN

DECLARE ig_id INT(11);

DECLARE uip INT(11);

DECLARE isl_id INT(11);

DECLARE a_id INT(11);

DECLARE s_id INT(11);

DECLARE r_attr TEXT;

DECLARE blocked INT(11);

SELECT ip_group_id,ip INTO ig_id,uip FROM ip_groups WHERE is_deleted='0' AND uname=UN;

SELECT id INTO isl_id FROM iptraffic_service_links WHERE is_deleted='0' AND ip_group_id=ig_id;

SELECT account_id,service_id INTO a_id,s_id FROM service_links WHERE is_deleted='0' AND id=isl_id;

SELECT is_blocked INTO blocked FROM accounts WHERE is_deleted='0' AND id=a_id;

SELECT value INTO r_attr FROM radius_data WHERE owner_id=s_id;

SELECT inet_ntoa(uip& 0xFFFFFFFF) AS 'ip', unhex(r_attr) AS 'attr', blocked AS 'is_blocked';

END;

they works more faster than one big select query. is it right way to use them?