My query is too slow

Question

Please excuse my bad english. I am really frustrated and do not know what to do next. My problem is a sql query. The problem with the query is that the query takes too long and I think that this is my bad. Maybe you guys could help me out with my problem. I already tried many methods. For example: splitting the query in single steps or setting new indexe. My database is MariaDB v10.0.35.

My goal is to disband my adresses from the meeting_dates table in a completely new table. I also want to have an entry in the new table with every city, street and housenumber. But I also have entries with the where the street_number is 0, which stands for all streets in the cirty. Perhaps the query will explain more. If you guys have any questions or need more information , I will try to answer them or provide you with the necessary information. Thanks in advance!

INSERT INTO meeting_dates_new (meeting, meeting_date, cityNumber, streetNumber, category, entryHousenumberTo)
        SELECT
           mt.meeting,
           mt.meeting_date,
           mt.cityNumber,
           CASE md.streetNumber
           WHEN 0 THEN
               l.streetNumber
           ELSE
               md.streetNumber
           END AS streetNumber,
           md.category,
           CASE WHEN housenr.id IS NULL THEN
               0
           ELSE
               housenr.id
           END AS houseNumber,
           CASE WHEN cd1.houseNumberFrom != 0 THEN
               cd1.houseNumberFrom
           ELSE
               0
           END AS entryHousenumberTo
       FROM
           meeting_dates mt
           LEFT JOIN locations l ON (mt.cityNumber = l.cityNumber
                   AND mt.streetNumber = 0 AND 0 = (
                       SELECT
                           COUNT(*) AS counter
                       FROM
                           meeting_dates mtON
                   WHERE
                       mtON.cityNumber = l.cityNumber
                       AND mtON.streetNumber = l.streetNumber
                       AND mtON.category = mt.category
                       AND mt.houseNumberFrom BETWEEN mtON.houseNumberFrom
                       AND mtON.houseNumberTill                      
                       ORDER BY
                       mtON.meeting
                   LIMIT 1))
           LEFT JOIN meeting_dates md1 ON (mt.streetNumber = 0
                   AND md1.streetNumber = l.streetNumber
                   AND md1.cityNumber = md.cityNumber
                   AND md1.category = md.category
                   AND md1.meeting > md.meeting
                   AND md1.houseNumberFrom > 0)
           LEFT JOIN houseNumber housenr ON
               CASE WHEN md.houseNumberTill != 0 THEN
                   (md.houseNumberTill != 0 AND housenr.id BETWEEN md.houseNumberFrom AND md.houseNumberTill          
                   AND CASE WHEN md.evenOdd IN ('even', 'odd') THEN
                       housenr.id % 2
                   ELSE
                       0
                   END = CASE md.evenOdd WHEN 'even' THEN
                   0 WHEN 'odd' THEN 1 ELSE 0 END)
               WHEN md1.houseNumberFrom != 0 THEN
                   (md.houseNumberTill = 0 AND housenr.id BETWEEN md.houseNumberFrom AND CASE WHEN md1.houseNumberFrom = 1 THEN md1.houseNumberFrom WHEN md1.houseNumberFrom > 1 THEN md1.houseNumberFrom-1 END       
                   AND CASE WHEN md.evenOdd IN ('even', 'odd') THEN
                       housenr.id % 2
                   ELSE
                       0
                   END = CASE md.evenOdd WHEN 'even' THEN
                   0 WHEN 'odd' THEN 1 ELSE 0 END)
                   END
                   GROUP BY md.meeting, md.meeting_date, md.cityNumber, streetNumber, md1.houseNumberFrom, md.category, houseNumberFrom;

EDIT

These are the table definitions of all required tables.

CREATE TABLE `meeting_dates` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL DEFAULT '0',
  `tstamp` int(10) NOT NULL DEFAULT '0',
  `zip_code` int(5) unsigned NOT NULL DEFAULT '0',
  `cityNumber` int(11) NOT NULL DEFAULT '0',
  `city` varchar(100) DEFAULT NULL,
  `streetNumber` int(11) NOT NULL DEFAULT '0',
  `street` varchar(100) DEFAULT NULL,
  `category` varchar(10) DEFAULT NULL,
  `houseNumberFrom` int(11) NOT NULL DEFAULT '0',
  `houseNumberTill` int(11) NOT NULL DEFAULT '0',
  `evenOdd` enum('even','odd','all') DEFAULT NULL,
  `meeting` int(11) NOT NULL DEFAULT '0',
  `meeting_date` date NOT NULL,
  `notes_intern` tinytext,
  `description_extern` tinytext,
  `cycle` varchar(100) DEFAULT NULL,
  `additional` tinytext,
  `note` tinytext,
  `description_intern` tinytext,
  `sort` int(5) DEFAULT NULL,
  `valid_from` date NOT NULL,
  `valid_to` date NOT NULL,
  PRIMARY KEY (`uid`),
  KEY `parent` (`pid`) USING BTREE,
  KEY `search` (`meeting`,`houseNumberFrom`,`houseNumberTill`,`streetNumber`,`cityNumber`,`category`,`evenOdd`) USING BTREE,
  KEY `sub_search` (`meeting`,`houseNumberFrom`,`houseNumberTill`,`cityNumber`,`streetNumber`,`category`,`pid`) USING BTREE,
  KEY `test` (`pid`,`meeting`,`houseNumberFrom`,`houseNumberTill`,`cityNumber`,`streetNumber`,`category`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=13046789 DEFAULT CHARSET=utf8mb4

CREATE TABLE `locations` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `cityNumber` int(11) NOT NULL,
  `city` varchar(255) NOT NULL,
  `streetNumber` int(11) NOT NULL,
  `street` varchar(255) NOT NULL,
  `zip_code` int(5) unsigned zerofill NOT NULL,
  PRIMARY KEY (`uid`),
  KEY `cityNumber` (`cityNumber`),
  KEY `streetNumber` (`streetNumber`),
  KEY `city_street` (`cityNumber`,`streetNumber`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=257095 DEFAULT CHARSET=utf8mb4

CREATE TABLE `houseNumber` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `housenumber` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `housenumber` (`housenumber`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

CREATE TABLE `meeting_dates_new` (
  `meeting` int(11) NOT NULL DEFAULT '0',
  `meeting_date` date NOT NULL,
  `cityNumber` int(11) NOT NULL,
  `streetNumber` int(11) NOT NULL,
  `category` varchar(10) NOT NULL,
  `houseNumber` int(11) NOT NULL DEFAULT '0',
  `entryHousenumberTo` int(11) DEFAULT '0',
  KEY `meeting` (`meeting`) USING BTREE,
  KEY `cityNumber` (`cityNumber`) USING BTREE,
  KEY `streetNumber` (`streetNumber`) USING BTREE,
  KEY `category` (`category`) USING BTREE,
  KEY `houseNumber` (`houseNumber`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

This query is always executed when there is new data in the meetings_dates table. There is no fixed schedule. The query has to be as fast as possible because other services have to access the disbands data. These services access the data every single hour. For the meeting_dates table with 250k entries, the query takes 2500 seconds. However, I also have a meeting_dates table with 350k entries. And this went on for over 13,000 seconds until I canceled it. The select is very slow with and without the insert. I tried to set the index on the individual columns, but also an index on all columns. And I tried without an index. But there was no improvement

Is this a query that you need to run only once? If so, does it matter that it's slow? How long does it take to run? Is the SELECT also slow, or does the query become slow only when you add the INSERT in front of it? Have you created indexes on meeting_dates_new? — dbdemon, Aug 17 '20 at 8:10
It might be useful to see the output of the EXPLAIN for the SELECT query: EXPLAIN SELECT mt.meeting, mt.meeting_date, .... (You can use \G as the terminator instead of ; to get the output as one parameter per line.) I also notice that the meeting_dates table is MyISAM whereas the others are InnoDB. Any particular reason? Are the database system variables sufficient for the size of the tables? E.g. the innodb_buffer_pool_size and key_buffer_size variables? You want to keep as much of the data in memory as possible and avoid having to go to the disk to retrieve data. — dbdemon, Aug 17 '20 at 12:02
Please consider reading about Asking query performance questions — mustaccio, Aug 17 '20 at 12:59

Rick James · Accepted Answer · 2020-08-17 15:56:21Z

(Too long for a Comment)

Let's start with simplifications:

LEFT seems unnecessary. Use LEFT only if you the "right" table is optional and you want NULLs when the missing.

Some CASEs can be shortened:

     CASE WHEN housenr.id IS NULL THEN
         0
     ELSE
         housenr.id
     END AS houseNumber,

--> COALESCE(housenr.id, 0) AS houseNumber,

           CASE WHEN md.evenOdd IN ('even', 'odd') THEN
                   housenr.id % 2
               ELSE
                   0
               END

--> `IF(md.evenOdd = '

         CASE md.evenOdd WHEN 'even' THEN
               0 WHEN 'odd' THEN 1 ELSE 0 END)

--> IF(md.evenOdd = 'odd', 1, 0)

or even simply (since booleans are 1 for true and 0 for false)

--> (md.evenOdd = 'odd')

It seems 'wrong' to test something from another table in a LEFT JOIN: LEFT JOIN meeting_dates md1 ON (mt.streetNumber = 0 AND.... If you are "filtering" on mt.streetNumber = 0, move that clause to WHERE. The ON clause should be limited to specifying how the tables are 'related'.
The complex handling of houseNumber and From and Till is too complex to optimize. Is there any way to change the incoming data in order to simplify the tests to avoid the complexity?
If the data is unchanging, perhaps some of it could be summarized nightly?
I don't see a WHERE clause, do you process the entire table every time?

Stack Exchange Network

current community

your communities

more stack exchange communities

My query is too slow

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged performance mariadb or ask your own question.

Hot Network Questions

My query is too slow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged performance mariadb or ask your own question.

Related

Hot Network Questions