Menu
On a web site, I am using django to make some requests :
The django line :
CINodeInventory.objects.select_related().filter(ci_class__type='equipment',company__slug=self.kwargs['company'])
generates a MySQL query like that :
SELECT *
FROM `inventory_cinodeinventory`
INNER JOIN `ci_cinodeclass` ON ( `inventory_cinodeinventory`.`ci_class_id` = `ci_cinodeclass`.`class_name` )
INNER JOIN `accounts_companyprofile` ON ( `inventory_cinodeinventory`.`company_id` = `accounts_companyprofile`.`slug` )
INNER JOIN `accounts_companysite` ON ( `inventory_cinodeinventory`.`company_site_id` = `accounts_companysite`.`slug` )
INNER JOIN `accounts_companyprofile` T5 ON ( `accounts_companysite`.`company_id` = T5.`slug` )
WHERE (
`ci_cinodeclass`.`type` = 'equipment'
AND `inventory_cinodeinventory`.`company_id` = 'thecompany'
)
ORDER BY `inventory_cinodeinventory`.`name` ASC
The problem is that for only 40 000 entries in the main table, it takes 0.5 seconds to process.
I checked all indexes, create the ones that is required for sorting or joinning : I still have problem.
The funny thing is that if I replace the last INNER JOIN by a LEFT JOIN, the request is 10x faster ! Unfortunately, As I am using django for requesting, I do not have access to the SQL requests it generates (I do not want to do raw SQL myself).
for the last join as an "INNER JOIN" the EXPLAIN gives:
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+
| 1 | SIMPLE | accounts_companyprofile | const | PRIMARY | PRIMARY | 152 | const | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | inventory_cinodeinventory | range | inventory_cinodeinventory_41ddcf59,inventory_cinodeinventory_543518c6,inventory_cinodeinventory_14fe63e9 | inventory_cinodeinventory_543518c6 | 152 | NULL | 42129 | Using where |
| 1 | SIMPLE | T5 | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using join buffer |
| 1 | SIMPLE | accounts_companysite | eq_ref | PRIMARY,accounts_companysite_543518c6 | PRIMARY | 152 | cidb.inventory_cinodeinventory.company_site_id | 1 | Using where |
| 1 | SIMPLE | ci_cinodeclass | eq_ref | PRIMARY | PRIMARY | 92 | cidb.inventory_cinodeinventory.ci_class_id | 1 | Using where |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+
For the last join as "LEFT JOIN", I got :
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+---------+---------+------------------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+---------+---------+------------------------------------------------+------+-------------+
| 1 | SIMPLE | accounts_companyprofile | const | PRIMARY | PRIMARY | 152 | const | 1 | |
| 1 | SIMPLE | inventory_cinodeinventory | index | inventory_cinodeinventory_41ddcf59,inventory_cinodeinventory_543518c6,inventory_cinodeinventory_14fe63e9 | name | 194 | NULL | 173 | Using where |
| 1 | SIMPLE | accounts_companysite | eq_ref | PRIMARY | PRIMARY | 152 | cidb.inventory_cinodeinventory.company_site_id | 1 | |
| 1 | SIMPLE | T5 | eq_ref | PRIMARY | PRIMARY | 152 | cidb.accounts_companysite.company_id | 1 | |
| 1 | SIMPLE | ci_cinodeclass | eq_ref | PRIMARY | PRIMARY | 92 | cidb.inventory_cinodeinventory.ci_class_id | 1 | Using where |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+---------+---------+------------------------------------------------+------+-------------+
it seems for the "INNER JOIN" case, MySQL do not find any indexes for the T5 join : why ?
The profiling gives :
starting 0.000011
checking query cache for query 0.000086
Opening tables 0.000014
System lock 0.000005
Table lock 0.000052
init 0.000064
optimizing 0.000021
statistics 0.000180
preparing 0.000024
Creating tmp table 0.000308
executing 0.000003
Copying to tmp table 0.353414 !!!
Sorting result 0.037244
Sending data 0.035168
end 0.000005
removing tmp table 0.550974 !!!
end 0.000009
query end 0.000003
freeing items 0.000113
storing result in query cache 0.000009
logging slow query 0.000002
cleaning up 0.000004
So it seems, there is a step where mysql uses a temporary table. This step does not occurs with a LEFT JOIN, only with the INNER JOIN. I tried to avoid that by including a "force index for join" in the query but it did not help...
join tables are :
CREATE TABLE IF NOT EXISTS `accounts_companysite` (
`slug` varchar(50) NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`deleted` tinyint(1) NOT NULL,
`company_id` varchar(50) NOT NULL,
`name` varchar(128) NOT NULL,
`address` longtext NOT NULL,
`city` varchar(64) NOT NULL,
`zip_code` varchar(6) NOT NULL,
`state` varchar(32) NOT NULL,
`country` varchar(2) DEFAULT NULL,
`phone` varchar(20) NOT NULL,
`fax` varchar(20) NOT NULL,
`more` longtext NOT NULL,
PRIMARY KEY (`slug`),
KEY `accounts_companysite_543518c6` (`company_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `accounts_companyprofile` (
`slug` varchar(50) NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`deleted` tinyint(1) NOT NULL,
`name` varchar(128) NOT NULL,
`address` longtext NOT NULL,
`city` varchar(64) NOT NULL,
`zip_code` varchar(6) NOT NULL,
`state` varchar(32) NOT NULL,
`country` varchar(2) DEFAULT NULL,
`phone` varchar(20) NOT NULL,
`fax` varchar(20) NOT NULL,
`contract_id` varchar(32) NOT NULL,
`more` longtext NOT NULL,
PRIMARY KEY (`slug`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `inventory_cinodeinventory` (
`uuid` varchar(36) NOT NULL,
`name` varchar(64) NOT NULL,
`synopsis` varchar(64) NOT NULL,
`path` varchar(255) NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`deleted` tinyint(1) NOT NULL,
`root_id` varchar(36) DEFAULT NULL,
`parent_id` varchar(36) DEFAULT NULL,
`order` int(11) NOT NULL,
`ci_class_id` varchar(30) NOT NULL,
`data` longtext NOT NULL,
`serial` varchar(64) NOT NULL,
`company_id` varchar(50) NOT NULL,
`company_site_id` varchar(50) NOT NULL,
`vendor` varchar(48) NOT NULL,
`type` varchar(64) NOT NULL,
`model` varchar(64) NOT NULL,
`room` varchar(30) NOT NULL,
`rack` varchar(30) NOT NULL,
`rack_slot` varchar(30) NOT NULL,
PRIMARY KEY (`uuid`),
KEY `inventory_cinodeinventory_1fb5ff88` (`root_id`),
KEY `inventory_cinodeinventory_63f17a16` (`parent_id`),
KEY `inventory_cinodeinventory_41ddcf59` (`ci_class_id`),
KEY `inventory_cinodeinventory_543518c6` (`company_id`),
KEY `inventory_cinodeinventory_14fe63e9` (`company_site_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
I also tried to tune MySQL by adding in my.cnf :
join_buffer_size = 16M
tmp_table_size = 160M
max_seeks_for_key = 100
... but it does not help.
With django, it is easy to use Postgresql instead of Mysql, so I tried it : with the same query and same data in db, postgres is much faster that Mysql : x10 more faster while using INNER JOIN (analyse shows it uses indexes unlike Mysql)
DO you have an idea why my MySQL INNER JOIN is so slow ?
EDIT 1:
after some testing, I reduce the problem to this request :
SELECT *
FROM `inventory_cinodeinventory`
INNER JOIN `accounts_companyprofile` ON `inventory_cinodeinventory`.`company_id` = `accounts_companyprofile`.`slug`
ORDER BY `inventory_cinodeinventory`.`name` ASC
This request is very slow and I do not see why. Without the 'ORDER BY' clause, it is fast, but not with it, although, the name index is set :
CREATE TABLE IF NOT EXISTS `inventory_cinodeinventory` (
`uuid` varchar(36) NOT NULL,
`name` varchar(64) NOT NULL,
`synopsis` varchar(64) NOT NULL,
`path` varchar(255) NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`deleted` tinyint(1) NOT NULL,
`root_id` varchar(36) DEFAULT NULL,
`parent_id` varchar(36) DEFAULT NULL,
`order` int(11) NOT NULL,
`ci_class_id` varchar(30) NOT NULL,
`data` longtext NOT NULL,
`serial` varchar(64) NOT NULL,
`company_id` varchar(50) NOT NULL,
`company_site_id` varchar(50) NOT NULL,
`vendor` varchar(48) NOT NULL,
`type` varchar(64) NOT NULL,
`model` varchar(64) NOT NULL,
`room` varchar(30) NOT NULL,
`rack` varchar(30) NOT NULL,
`rack_slot` varchar(30) NOT NULL,
PRIMARY KEY (`uuid`),
KEY `inventory_cinodeinventory_1fb5ff88` (`root_id`),
KEY `inventory_cinodeinventory_63f17a16` (`parent_id`),
KEY `inventory_cinodeinventory_41ddcf59` (`ci_class_id`),
KEY `inventory_cinodeinventory_14fe63e9` (`company_site_id`),
KEY `inventory_cinodeinventory_543518c6` (`company_id`,`name`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
EDIT 2:
Previous request can be solved with a 'FORCE INDEX FOR ORDER BY (name)'. Unfortunately this tip does not work with the initial request in my topic...
EDIT 3:
I rebuilt the database with replacing 'uuid' primary keys from varchar to an integer : it does not help at all... bad news.
EDIT 4:
I tried Mysql 5.5.20 : not better. Postgresql 8.4 is 10x faster for this particular request.
I modified a little the resquest (removed the T5 join) :
SELECT *
FROM `inventory_cinodeinventory`
INNER JOIN `ci_cinodeclass` ON ( `inventory_cinodeinventory`.`ci_class_id` = `ci_cinodeclass`.`class_name` )
INNER JOIN `accounts_companyprofile` ON ( `inventory_cinodeinventory`.`company_id` = `accounts_companyprofile`.`slug` )
INNER JOIN `accounts_companysite` ON ( `inventory_cinodeinventory`.`company_site_id` = `accounts_companysite`.`slug` )
WHERE (
`ci_cinodeclass`.`type` = 'equipment'
AND `inventory_cinodeinventory`.`company_id` = 'thecompany'
)
ORDER BY `inventory_cinodeinventory`.`name` ASC
This is working fine, but I have some other requests, just a little different where this trick is not working.
In fact, after searching, it seems that as soon you join 2 tables that have "a lot in common" that is, let's say, half the rows of the right table can be join to those on the left table (it is my case) : Mysql prefer using table scanning instead of index : faster I found somewhere (!!)
Your real issue is with the second line in your first explain:
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+
| 1 | SIMPLE | inventory_cinodeinventory | range | inventory_cinodeinventory_41ddcf59,inventory_cinodeinventory_543518c6,inventory_cinodeinventory_14fe63e9 | inventory_cinodeinventory_543518c6 | 152 | NULL | 42129 | Using where |
You're analyzing 42129 rows using this WHERE clause:
AND `inventory_cinodeinventory`.`company_id` = 'thecompany'
If you don't already have one, you should have an index on inventory_cinodeinventory for (company_id, name)
i.e.
ALTER TABLE `inventory_cinodeinventory`
ADD INDEX `inventory_cinodeinventory__company_id__name` (`company_id`, `name`);
That way your WHERE and ORDER BY clauses don't wind up conflicting, causing a bad index selection, which appears to be happening right now.
If you do already have an index with those columns, in that order, I would suggest running OPTIMIZE TABLE inventory_cinodeinventory; to see if it it gets MySQL to use the correct index.
In general, you've got a larger issue (which I presume is due to Django's design, but I lack experience using that framework) in that you have these huge keys. All of the keys in your EXPLAIN are 152 and 92 bytes in length. This makes for much larger indexes, which means more disk access, which means slower queries. Primary and foreign keys would ideally be ints or very short varchar columns (e.g. varchar(10)). varchar(50) for these keys is going to put a significant constant multiple on your DB response time.
134
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
