rendered paste bodymysql> explain SELECT u.*, (CONCAT(u.is_approved,u.is_activated,u.is_suspended)) AS status, COUNT(f.file_id) AS files_count, IF(SUM(f.file_size),SUM(f.file_size),0) AS total_file_size FROM uploader_users AS u LEFT JOIN uploader_userfiles AS f USING(userid) GROUP BY u.userid ORDER BY u.userid asc LIMIT 0, 25;
+----+-------------+-------+-------+---------------+---------+---------+--------------------------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+--------------------------+--------+-------+
| 1 | SIMPLE | u | index | NULL | PRIMARY | 4 | NULL | 185455 | |
| 1 | SIMPLE | f | ref | userid | userid | 4 | boxstr_uploader.u.userid | 14 | |
+----+-------------+-------+-------+---------------+---------+---------+--------------------------+--------+-------+
2 rows in set (0.03 sec)
mysql> show index from uploader_users;
+----------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| uploader_users | 0 | PRIMARY | 1 | userid | A | 185455 | NULL | NULL | | BTREE | |
| uploader_users | 1 | username | 1 | username | A | 185455 | NULL | NULL | | BTREE | |
| uploader_users | 1 | fl_has_public | 1 | fl_has_public | A | 2 | NULL | NULL | YES | BTREE | |
+----------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
mysql> show index from uploader_userfiles;
+--------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| uploader_userfiles | 0 | PRIMARY | 1 | file_id | A | 583558 | NULL | NULL | | BTREE | |
| uploader_userfiles | 1 | userid | 1 | userid | A | 41682 | NULL | NULL | | BTREE | |
| uploader_userfiles | 1 | folder_id | 1 | folder_id | A | 53050 | NULL | NULL | | BTREE | |
| uploader_userfiles | 1 | file_extension | 1 | file_extension | A | 633 | NULL | NULL | | BTREE | |
| uploader_userfiles | 1 | desktop_upload | 1 | desktop_upload | A | 3 | NULL | NULL | | BTREE | |
| uploader_userfiles | 1 | file_isimage | 1 | file_isimage | A | 3 | NULL | NULL | YES | BTREE | |
| uploader_userfiles | 1 | file_isarchive | 1 | file_isarchive | A | 2 | NULL | NULL | | BTREE | |
+--------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)
mysql> show create table uploader_users;
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| uploader_users | CREATE TABLE `uploader_users` (
`userid` int(10) unsigned NOT NULL auto_increment,
`username` varchar(64) collate utf8_unicode_ci NOT NULL default '',
`password` varchar(32) collate utf8_unicode_ci NOT NULL default '',
`email` varchar(255) collate utf8_unicode_ci NOT NULL default '',
`level` tinyint(3) unsigned default '0',
`max_messages` tinyint(1) default '20',
`is_activated` tinyint(1) default '0',
`is_approved` tinyint(1) default '0',
`is_suspended` tinyint(1) default '0',
`last_login_time` int(11) default '0',
`last_login_ip` varchar(15) collate utf8_unicode_ci default NULL,
`reg_email` varchar(255) collate utf8_unicode_ci default '',
`reg_date` int(10) unsigned default '0',
`reg_ip` varchar(15) collate utf8_unicode_ci default '0.0.0.0',
`pref_accepts_pm` tinyint(1) default '1',
`pref_show_email` tinyint(1) default '0',
`bw_used` double unsigned NOT NULL default '0',
`bw_max` int(10) unsigned NOT NULL default '0',
`bw_reset_last` int(10) unsigned default '0',
`bw_reset_period` tinyint(3) unsigned default '30',
`bw_reset_auto` tinyint(1) default '1',
`bw_xfer_rate` smallint(5) unsigned default '0',
`fl_max_storage` smallint(5) unsigned default '5',
`fl_max_filesize` mediumint(8) unsigned default '2048',
`fl_allowed_types` varchar(255) collate utf8_unicode_ci default NULL,
`fl_images_only` tinyint(1) default '1',
`fl_rename_permission` tinyint(1) default '0',
`fl_allow_folders` tinyint(1) default '1',
`fl_max_folders` smallint(6) default '10',
`fl_watermark` tinyint(1) default '1',
`fl_has_public` tinyint(1) default '0',
`xtr_admin_comments` varchar(255) collate utf8_unicode_ci default NULL,
`xtr_suspended_reason` longtext collate utf8_unicode_ci,
`xtr_new_email_address` varchar(255) collate utf8_unicode_ci default NULL,
`xtr_activation_code` varchar(32) collate utf8_unicode_ci default NULL,
`xtr_password_reset_code` varchar(32) collate utf8_unicode_ci default NULL,
`xtr_change_email_code` varchar(32) collate utf8_unicode_ci default NULL,
`xtr_max_zip_size` varchar(10) collate utf8_unicode_ci NOT NULL default '100',
`xtr_max_zips_daily` varchar(5) collate utf8_unicode_ci NOT NULL default '5',
`xtr_zip_make_freq` varchar(6) collate utf8_unicode_ci NOT NULL default '120',
`xtr_max_zips` varchar(5) collate utf8_unicode_ci NOT NULL default '50',
`xtr_max_playlists` varchar(5) collate utf8_unicode_ci NOT NULL default '100',
PRIMARY KEY (`userid`),
KEY `username` (`username`),
KEY `fl_has_public` (`fl_has_public`)
) ENGINE=MyISAM AUTO_INCREMENT=190456 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table uploader_userfiles;
+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| uploader_userfiles | CREATE TABLE `uploader_userfiles` (
`file_id` bigint(20) unsigned NOT NULL auto_increment,
`file_name` varchar(255) collate utf8_unicode_ci NOT NULL default '',
`file_description` text collate utf8_unicode_ci NOT NULL,
`file_location` varchar(255) collate utf8_unicode_ci NOT NULL default '',
`file_size` int(10) unsigned NOT NULL default '0',
`file_isimage` tinyint(1) default '0',
`file_ismp3` tinyint(1) NOT NULL default '0',
`file_isarchive` tinyint(1) NOT NULL default '0',
`file_extension` varchar(10) collate utf8_unicode_ci NOT NULL,
`file_date` int(10) unsigned NOT NULL default '0',
`file_key` char(5) collate utf8_unicode_ci NOT NULL,
`file_views` int(10) unsigned default '0',
`file_last_view` int(10) unsigned default '0',
`userid` int(10) unsigned NOT NULL default '0',
`folder_id` int(10) unsigned NOT NULL default '0',
`desktop_upload` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`file_id`),
KEY `userid` (`userid`),
KEY `folder_id` (`folder_id`),
KEY `file_extension` (`file_extension`),
KEY `desktop_upload` (`desktop_upload`),
KEY `file_isimage` (`file_isimage`),
KEY `file_isarchive` (`file_isarchive`)
) ENGINE=MyISAM AUTO_INCREMENT=1327468 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>