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
SELECTalso slow, or does the query become slow only when you add theINSERTin front of it? Have you created indexes onmeeting_dates_new? – dbdemon Aug 17 '20 at 8:10EXPLAINfor theSELECTquery:EXPLAIN SELECT mt.meeting, mt.meeting_date, .... (You can use\Gas the terminator instead of;to get the output as one parameter per line.) I also notice that themeeting_datestable isMyISAMwhereas the others areInnoDB. Any particular reason? Are the database system variables sufficient for the size of the tables? E.g. theinnodb_buffer_pool_sizeandkey_buffer_sizevariables? 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