Hi Guys,
I have a query where I am left joining #__virtuemart_userinfos table, like this:
LEFT JOIN #__virtuemart_userinfos uv ON uv.virtuemart_user_id=uu.user_id AND uv.address_type="BT"
It works fine when there is not that many records, but as it gets larger it gets much slower.
To give an example, on a very powerful server (24 cores) with a lot of memory (24 GB RAM) and fast drives (SSD), where there is over 30,000 users, the query slows down and takes over 4 minutes to run.
After some debugging by Percona, they found that adding this key fixes the problem:
ALTER TABLE `#__virtuemart_userinfos` ADD KEY(`virtuemart_user_id`,`address_type`);
Now it takes 0.24 seconds to run the same query.
Possible to add the key to core?
version of vm??
2.6.10
How works that?
PRIMARY KEY (`virtuemart_userinfo_id`),
KEY `i_virtuemart_user_id` (`virtuemart_userinfo_id`,`virtuemart_user_id`),
KEY `virtuemart_user_id` (`virtuemart_user_id`,`address_type`),
KEY `address_type` (`address_type`),
KEY `address_type_name` (`address_type_name`)
??
Hi Milbo,
Yes, that works well on testing.