CODEWITHSUNDEEP
phpmysqlperformance
DasSaffe
DasSaffe

Reputation: 2198

Performance-issues with mysql + php

I have the following PHP script, which executes a MySQL-Query.

$sSql = "SELECT DISTINCT t1.* "
      . "FROM import_data t1 "
      . "INNER JOIN import_profiles imp on t1.profile_id = imp.id "
      . "WHERE imp.creditornr = " . $vendor . " "
      . "AND t1.vendor = '" . $k . "' "
      . "AND t1.importRun = (SELECT MAX(importRun) AS importRun
         FROM import_data
         WHERE sku=t1.sku
           AND profile_id = t1.profile_id)";

In native SQL, a query looks like this:

SELECT DISTINCT t1.*
FROM import_data t1
INNER JOIN import_profiles imp on t1.profile_id = imp.id
WHERE imp.creditornr = 73329
AND t1.vendor = 'rackmountit'
AND t1.importRun = (SELECT MAX(importRun) AS importRun
            FROM import_data
            WHERE sku=t1.sku
              AND profile_id = t1.profile_id)

This is the explain of one of those queries: (I run > 10 of those) and the DB currently has ~100.000 entries (rising).

Explain

Also, those are the tables which are used in this statements:

import_data Table import_data

import_profiles Table import_profiles

I have no idea how, since I'm not that good in mysql, but are there any ideas how to increase the performance of those (sub)queries? Currently they are running > 1:00 and I need to optimize it.

Thanks and let me know, if you need further information.

EDIT 1 Added the CREATE TABLE statements

SET NAMES utf8;
SET time_zone = '+00:00';

CREATE TABLE `import_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `profile_id` int(11) NOT NULL,
  `sku` varchar(255) NOT NULL,
  `vendor` varchar(255) NOT NULL,
  `evp` double NOT NULL,
  `ek` double NOT NULL,
  `articletext1` text NOT NULL,
  `articletext2` text NOT NULL,
  `ean` text NOT NULL,
  `stock` int(11) NOT NULL,
  `zolltarif` int(11) NOT NULL,
  `tstamp` date NOT NULL,
  `importRun` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `import_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `creditornr` int(6) NOT NULL,
  `structure` text NOT NULL,
  `updatetime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Import-Profile für Vorlieferanten';

Upvotes: 0

Views: 76

Answers (3)

Rick James
Rick James

Reputation: 142518

import_data:  INDEX(sku, profile_id, importRun) -- for the MAX subquery
import_data:  INDEX(vendor)
import_profiles:  INDEX(creditornr, id)

(It is unclear which of the last two to have, but it won't hurt to include both.)

Since your query is somewhat a "groupwise max", see groupwise max.

Upvotes: 0

David
David

Reputation: 18281

This will depend on your schema and how your data actually looks like.

The vendor field seems like a good candidate for putting an index on it. But that depends on how unique it is. If every row of the vendor column is unique, then look for something else to filter on.

Using an analogy of shopping for groceries: As I mentioned in the column, going to temporary is like having a very long grocery list, copying all or almost all of the entire list to a new sheet of paper, and THEN going through the copy to find which items in the list are appropriate to the grocery isle you are in.

Edit: SO answer for how to add indexes, recommend reading comments as well. - https://stackoverflow.com/a/3002635/9908

Upvotes: 0

Taras
Taras

Reputation: 582

You should add indexes for fields: import_data.profile_id, import_data.sku and import_profiles.creditornr it should increase your SQL queries speed

Upvotes: 1

Related Questions