Performance-issues with mysql + php

Question

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

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

import_data

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';

What is your table schema or did I miss it? Going to temporary is exceedingly bad in most cases as MySQL is writing a temp file to disk and then scanning through that. — David, May 10 '16 at 12:24
If your data is valuable, which it likely is, copy the table in question to a dev machine and try adding indexes to columns in your where conditions. That vendor field is likely ideal if its cardinality is less then 50% ( super ideal if its below 20%). — David, May 10 '16 at 12:26
You can [read this](http://mysql.rjweb.org/doc.php/index_cookbook_mysql) and see if it guides you? Basically add indexes only on the columns referenced in the where clauses — Martin, May 10 '16 at 12:26
@David you mean the structure of `import_data`? Using some kind of phpMyAdmin, since I don't have access to a shell. Also, how can I check the cardinalitiy? — DasSaffe, May 10 '16 at 12:28
Do you have any indexes in your tables? Could you publish import_data and import_profiles CREATE TABLE output here? — Taras, May 10 '16 at 12:30
DasSaffe - Look at this to get an idea of which fields in your data can help you cut down on total rows selected - http://stackoverflow.com/a/1002363/9908 Smaller the count, the better. "You cannot make a computer run faster, but you can make it do less." — David, May 10 '16 at 12:32
@DasSaffe Likely not an issue given what seems like a small amount of data but when the day comes when you deal with larger amounts of data. This section of MySQL manual is your friend - http://dev.mysql.com/doc/refman/5.0/en/optimizing-innodb.html — David, May 10 '16 at 12:35
@DasSaffe Found a decent blog post talking about indexes and InnoDB ( table engine you're using ) - https://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/ I have to run, but my parting advice. DO NOT PUT INDEXES ON HIGH CARDINALITY COLUMNS... and use them as minimal as possible. MySQL will try to keep indexes loaded in memory so less is crucial. Too many and you hurt performance overall ( rapid loading/unloading). — David, May 10 '16 at 12:40
Does anyone think that prepared statements **_might_** help?? — Cayce K, May 10 '16 at 12:51
@CayceK - Prepared statements won't help enough to notice. The performance problem is the lack of indexes. — Rick James, May 10 '16 at 22:51

score 1 · Accepted Answer · answered May 10 '16 at 12:41

1

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

answered May 10 '16 at 12:41

Taras

572
3
15

so, when I can choose what type of index, I choose "index" instead of "unique", unless I can guarantee that this field will be unique? – DasSaffe May 10 '16 at 12:43
@DasSaffe - http://stackoverflow.com/questions/388982/mysql-5-0-indexes-unique-vs-non-unique Now that you know what keywords to look for, stackoverflow is your best friend for answers. – David May 10 '16 at 12:44
@DasSaffe you should add primary keys. This makes them more unique. that is best done (most often) on the id. – Cayce K May 10 '16 at 12:44
@CayceK Been a while but I don't believe InnoDB allows for tables without a primary key, ideally one that is not a natural key. – David May 10 '16 at 12:46
@David now I'm out of my element. Didn't notice the InnoDB engine. That is a solid thing to know. Not sure if it forces a primary key / has one if you don't implicitly define one. I'm going to bow out of the conversation :D – Cayce K May 10 '16 at 12:47
@David, InnoDB can't work with tables without primary key defined. If some table does not have it is created automatically by InnoDB engine. Type of indexes should be selected according to your data in DB. Correct indexes will increase performance any way. – Taras May 10 '16 at 12:52
That indeed boosted the performance IMMENSE! The precedure went down from over 30 min to 5 sec (!) – DasSaffe May 10 '16 at 13:11
@Taras I stopped using MySQL around 5.1-5.2 but wasn't sure if that had somehow changed in last few updates. – David May 10 '16 at 16:58

score 0 · Answer 2 · edited May 23 '17 at 12:31

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

score 0 · Answer 3 · answered May 10 '16 at 22:49

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.

Performance-issues with mysql + php

3 Answers3