userinfos table key

[seyi]

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?

[AH]

version of vm??

[seyi]

2.6.10

[Milbo]

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`)

??

[seyi]

Hi Milbo,

Yes, that works well on testing.