Mariadb – My query is too slow

mariadbperformance

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

Best Answer

(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?

YOUR QUERY

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;

At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506.

REALITY CHECK

No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can fight to one enemy they all have in common : The InnoDB Architecture.

InnoDB Architecture

CLUSTERED INDEX

Please keep in mind that the each threadid entry has a primary key attached. This means that when you read from the index, it must do a primary key lookup within the ClusteredIndex (internally named gen_clust_index). In the ClusteredIndex, each InnoDB page contains both data and PRIMARY KEY index info. See my post Best of MyISAM and InnoDB for more info.

REDUNDANT INDEXES

You have a lot of clutter in the table because some indexes have the same leading columns. MySQL and InnoDB has to navigate through the index clutter to get to needed BTREE nodes. You should reduced that clutter by running the following:

ALTER TABLE newbb_innopost
    DROP INDEX threadid,
    DROP INDEX threadid_2,
    DROP INDEX threadid_visible_dateline,
    ADD INDEX threadid_visible_dateline_index (`threadid`,`visible`,`dateline`,`userid`)
;

Why strip down these indexes ?

The first three indexes start with threadid
threadid_2 and threadid_visible_dateline start with the same three columns
threadid_visible_dateline does not need postid since it's the PRIMARY KEY and it's embedded

BUFFER CACHING

The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages.

Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data. InnoDB caches every data page and index page (and its grandmother) it touches. If your InnoDB Buffer Pool is too small, you could be caching pages, invalidating pages, and removing pages all in one query.

TABLE LAYOUT

You could shave of some space from the row by considering importthreadid and importpostid. You have them as BIGINTs. They take up 16 bytes in the ClusteredIndex per row.

You should run this

SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();

This will recommend what data types these columns should be for the given dataset.

CONCLUSION

MyISAM has a lot less to contend with than InnoDB, especially in the area of caching.

While you revealed the amount of RAM (32GB) and the version of MySQL (Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002), there are still other pieces to this puzzle you have not revealed

The InnoDB settings
The Number of Cores
Other settings from my.cnf

If you can add these things to the question, I can further elaborate.

UPDATE 2014-08-28 11:27 EDT

You should increase threading

innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M

I would consider disabling the query cache (See my recent post Why query_cache_type is disabled by default start from MySQL 5.6?)

query_cache_size = 0

I would preserve the Buffer Pool

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Increase purge threads (if you do DML on multiple tables)

innodb_purge_threads = 4

Best Answer

Related Solutions

Mysql – Slow INSERT/UPDATE on InnoDB

Mysql – Why are simple SELECTs on InnoDB 100x slower than on MyISAM