MySQL query killing my server

Question

Looking at this query there's got to be something bogging it down that I'm not noticing. I ran it for 7 minutes and it only updated 2 rows.

//set product count for makes
$tru->query->run(array(
    'name' => 'get-make-list',
    'sql' => 'SELECT id, name FROM vehicle_make',
    'connection' => 'core'
));
while($tempMake = $tru->query->getArray('get-make-list')) {
    $tru->query->run(array(
        'name' => 'update-product-count',
        'sql' => 'UPDATE vehicle_make SET product_count = (
            SELECT COUNT(product_id) FROM taxonomy_master WHERE v_id IN (
                SELECT id FROM vehicle_catalog WHERE make_id = '.$tempMake['id'].'
        )
) WHERE id = '.$tempMake['id'],
        'connection' => 'core'
    ));
}

I'm sure this query can be optimized to perform better, but I can't think of how to do it.

vehicle_make = 45 rows

taxonomy_master = 11,223 rows

vehicle_catalog = 5,108 rows

All tables have appropriate indexes

UPDATE: I should note that this is a 1-time script so overhead isn't a big deal as long as it runs.

CREATE TABLE IF NOT EXISTS `vehicle_make` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `product_count` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=46 ;

CREATE TABLE IF NOT EXISTS `taxonomy_master` (
  `product_id` int(10) NOT NULL,
  `v_id` int(10) NOT NULL,
  `vehicle_requirement` varchar(255) DEFAULT NULL,
  `is_sellable` enum('True','False') DEFAULT 'True',
  `programming_override` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`product_id`,`v_id`),
  KEY `idx2` (`product_id`),
  KEY `idx3` (`v_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `vehicle_catalog` (
  `v_id` int(10) NOT NULL,
  `id` int(11) NOT NULL,
  `v_make` varchar(255) NOT NULL,
  `make_id` int(11) NOT NULL,
  `v_model` varchar(255) NOT NULL,
  `model_id` int(11) NOT NULL,
  `v_year` varchar(255) NOT NULL,
  PRIMARY KEY (`v_id`,`v_make`,`v_model`,`v_year`),
  UNIQUE KEY `idx` (`v_make`,`v_model`,`v_year`),
  UNIQUE KEY `idx2` (`v_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Update: The successful query to get what I needed is here....

SELECT 
        m.id,COUNT(t.product_id) AS CountOf
        FROM taxonomy_master             t
            INNER JOIN vehicle_catalog   v ON t.v_id=v.id
            INNER JOIN vehicle_make      m ON v.make_id=m.id
        GROUP BY m.id;

Answer 1

instead of using nested query , you can separated this query to 2 or 3 queries,

and in php insert the result of the inner query to the out query ,

its faster !

Answer 2

@haim-evgi Separating the queries will not increase the speed significantly, it will just shift the load from the DB server to the Web server and create overhead of moving data between the two servers.

I am not sure with the appropriate indexes you run such query 7 minutes. Could you please show the table structure of the tables involved in these queries.

Answer 3

without the tables/columns this is my best guess from reverse engineering the given queries:

UPDATE m
    SET product_count =COUNT(t.product_id)
    FROM taxonomy_master             t
        INNER JOIN vehicle_catalog   v ON t.v_id=v.id
        INNER JOIN vehicle_make      m ON v.make_id=m.id
    GROUP BY m.name

The given code loops over each make, and then runs a query the counts for each. My answer just does them all in one query and should be a lot faster.

have an index for each of these:

vehicle_make.id      cover on name
vehicle_catalog.id   cover make_id
taxonomy_master.v_id

EDIT

give this a try:

CREATE TEMPORARY TABLE CountsOf (
     id int(11) NOT NULL
     , CountOf int(11) NOT NULL DEFAULT 0.00
);

INSERT INTO CountsOf 
        (id, CountOf )
    SELECT 
        m.id,COUNT(t.product_id) AS CountOf
        FROM taxonomy_master             t
            INNER JOIN vehicle_catalog   v ON t.v_id=v.id
            INNER JOIN vehicle_make      m ON v.make_id=m.id
        GROUP BY m.id;

UPDATE taxonomy_master,CountsOf
    SET taxonomy_master.product_count=CountsOf.CountOf 
WHERE taxonomy_master.id=CountsOf.id;

Answer 4

Seems like you need the following indices:

1. INDEX BTREE('make_id') on vehicle_catalog
2. INDEX BTREE('v_id') on taxonomy_master