Index needed for virtuemart_product_medias table media_id

[Studio 42]

Hi the devs,

I wrote a tool to check media and product association to find missing medias.
In some server this is very slow, because no index exist for virtuemart_product_medias.virtuemart_media_id
Before index any simple join query need more then 30 secondes for a table having 6000 medias for 20 results.
On adding the index the result are found in 0.008 secondes only !
I know that this is not needed in front, but this slowdown the media list search in back-end and the tool i provide free here http://shop.st42.fr/en/categories-tools/virtuemart-media-folder-clear.htm to find media to delete and used by more then 300 users and my tool Vm be Pro to batch process images used by more then 150 customers.

Regards,
Patrick

[Milbo]

Please check the last install.sql (maybe only in the svn, not in the 3.0.18.5), because I added some keys there.

Btw, when you say "just set a key", the problem is to set the right key, so please just write also which key.

[Studio 42]

Hi max, the key is for virtuemart_product_medias.virtuemart_media_id as explained on my first message.
In the last beta 3.0.18.5 ordering key was added. But not virtuemart_media_id

This is the  current VM product_medias table CREATE :

Code Select Expand

CREATE TABLE IF NOT EXISTS `vm3la_virtuemart_product_medias` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `virtuemart_product_id` int(1) unsigned NOT NULL DEFAULT '0',
  `virtuemart_media_id` int(1) unsigned NOT NULL DEFAULT '0',
  `ordering` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `virtuemart_product_id` (`virtuemart_product_id`,`virtuemart_media_id`),
  KEY `ordering` (`virtuemart_product_id`,`ordering`)
)

KEY `virtuemart_product_id` use virtuemart_product_id before virtuemart_media_id, so virtuemart_media_id column index cannot be user to this query for eg.

Code Select Expand

SELECT m.virtuemart_media_id
FROM  `hr_virtuemart_medias` m
LEFT JOIN  `hr_virtuemart_product_medias` AS m2 ON m2.virtuemart_media_id = m.virtuemart_media_id
LEFT JOIN  `hr_virtuemart_products_en_gb` AS t ON t.virtuemart_product_id = m2.virtuemart_product_id
GROUP BY m.virtuemart_media_id
LIMIT 0 , 30


But with this

Code Select Expand

CREATE TABLE IF NOT EXISTS `hr_virtuemart_product_medias` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `virtuemart_product_id` int(1) unsigned NOT NULL DEFAULT '0',
  `virtuemart_media_id` int(1) unsigned NOT NULL DEFAULT '0',
  `ordering` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `virtuemart_product_id` (`virtuemart_product_id`,`virtuemart_media_id`),
  KEY `ordering` (`virtuemart_product_id`,`ordering`),
  KEY `virtuemart_media_id` (`virtuemart_media_id`)
)

because   KEY `virtuemart_media_id` (`virtuemart_media_id`)
Now virtuemart_media_id can be used because it's first(and used alone) and the join work with the key now, else it need to do a temp table to search for the virtuemart_media_id and need more then 30 secondes for this case without any search, if i add a search for any fields this need 10 secondes more, but only some microseconds more with the new index key

[Milbo]

my new key is

Code Select Expand


  PRIMARY KEY (`id`),
  UNIQUE KEY `virtuemart_product_id` (`virtuemart_product_id`,`virtuemart_media_id`),
  KEY `ordering` (`virtuemart_product_id`, `ordering`)


is this really necessary?
KEY `virtuemart_media_id` (`virtuemart_media_id`)

I am always for new keys if they give us performance, but useless keys use just webspace. I am just happy when you give me some speed results

[Studio 42]

This is for eg a complex query i use

Code Select Expand

SELECT m.virtuemart_media_id AS id, file_url AS url, file_title AS title, file_description AS description, file_meta AS meta, COUNT( m2.virtuemart_media_id ) AS total, GROUP_CONCAT(
product_name
) AS
NAMES FROM  `hr_virtuemart_medias` m
LEFT JOIN  `hr_virtuemart_product_medias` AS m2 ON m2.virtuemart_media_id = m.virtuemart_media_id
LEFT JOIN  `hr_virtuemart_products_en_gb` AS t ON t.virtuemart_product_id = m2.virtuemart_product_id
WHERE  `file_type` =  'product'
GROUP BY m.virtuemart_media_id
LIMIT 0 , 100


this was before new key with virtuemart_media_id, approxymate 6000 medias and 2600 products, when i have run my test to find the problem:
execution time in phpmyadmin  32.4871 sec

this is same wtih new key DB with 9420 medias and 3871 products :
execution time in phpmyadmin  0.0031 sec with the new key

On doing a search on  file_url, i dumped the values in php and only to get the results from DB i needed 32 from 60 secondes, so i was meaning that this was the thumb creation, that was so slow, but even if this was generated i needed alwasy more then 30 secondes, so i checked the slow down and this was the query.
I said me, hum group_concat can be slow, so i tested in mysql without the GROUP_CONCAT and the time was same.
So i used EXPLAIN in mysql and have see that the query use a temp table and no index.
Hum strange, my joins and group are all primary keys, so why it don't use indexes?
So checked each table and discovered that the virtuemart_media_id have no index. Added it and now it's faster then liight
You can try this query yourself,change the table prefix, ( remove the GROUP_CONCAT and COUNT )if you mean it comes from here) and you see that this only need some microseconds more with the new key and many second if you have many datas without the key.

[Milbo]

my new key is

Code Select Expand


  PRIMARY KEY (`id`),
  UNIQUE KEY `virtuemart_product_id` (`virtuemart_product_id`,`virtuemart_media_id`),
  KEY `ordering` (`virtuemart_product_id`, `ordering`)


is this really necessary?
KEY `virtuemart_media_id` (`virtuemart_media_id`)

I am always for new keys if they give us performance, but useless keys use just webspace. I am just happy when you give me some speed results

You use a JOin on the virtuemart_media_id and a
GROUP BY m.virtuemart_media_id

So you need it. I add it