Very slow SELECT on 13.000 Rows
Good evening together!
A quite long time ago i started to modernize the database structure of my web application. Therefore i started using Primary- and Foreign-Keys. I read that i had to use InnoDB for having all PK-/FK-Features (such as RI-Checks etc) and so i switched to this engine.
After migrating and filling my new tables with entries of the old one i realized that one query (SELECT-Statement, more informations about this follows) took quite long time on the webserver (>60 seconds) and worked VERY MUCH FASTER on my development-computer (< 1 second). This is a huge difference and i dont think that it comes of hardware reasons only...
So, lets go into detail:
I have two tables: Table A (Artikel) and Table B (DownloadLinks)
Create-Statement of Table "artikel":
[sql]
CREATE TABLE IF NOT EXISTS `artikel` (
`PK_Artikel` BINARY(16) NOT NULL ,
`ID` INT NULL ,
`FK_Rubric` BINARY(16) NOT NULL ,
`FK_Category` BINARY(16) NOT NULL ,
`FK_Language` BINARY(16) NOT NULL ,
`FK_User` BINARY(16) NOT NULL ,
`Title` VARCHAR(250) NULL ,
`Description` TEXT NULL ,
`Views` INT NULL ,
`CreationTimestamp` DATETIME NULL ,
`UpdateTimestamp` DATETIME NULL ,
`Publication` VARCHAR(250) NULL ,
`ReviewLink` VARCHAR(250) NULL ,
`PrivateReviewlLink` VARCHAR(250) NULL ,
`Regisseur` VARCHAR(250) NULL ,
`Actors` VARCHAR(250) NULL ,
`Runtime` VARCHAR(250) NULL ,
`Producer` VARCHAR(250) NULL ,
`Artist` VARCHAR(250) NULL ,
`Album` VARCHAR(250) NULL ,
`Equipment` VARCHAR(250) NULL ,
`Approved` TINYINT(1) NULL ,
`Request` TINYINT(1) NULL DEFAULT False ,
PRIMARY KEY (`PK_Artikel`, `ID`) )
ENGINE = InnoDB;
[/sql]
Create-Statement of Table B:
[sql]
CREATE TABLE IF NOT EXISTS `downloadlinks` (
`PK_DownloadLink` BINARY(16) NOT NULL ,
`FK_System` BINARY(16) NOT NULL ,
`FK_Artikel` BINARY(16) NOT NULL ,
`FK_Owner` BINARY(16) NOT NULL ,
`FK_Group` BINARY(16) NOT NULL ,
`PrivateTitle` VARCHAR(250) NULL ,
`DownloadLink` VARCHAR(500) NOT NULL ,
`FileName` VARCHAR(250) NULL ,
`FileSize` BIGINT NULL ,
`Downloads` INT NULL DEFAULT 0 ,
`Approved` TINYINT(1) NULL ,
`CreationTimestamp` DATETIME NULL ,
`UpdateTimestamp` DATETIME NULL ,
PRIMARY KEY (`PK_DownloadLink`) )
ENGINE = InnoDB;
[/sql]
As you see every downloadlink is part of an article.
I browse both tables via the following SELECT-Statements:
Table A SELECT:
[sql]
SELECT x.PK_Artikel, x.Title, x.FK_Language, u.UploadHint, c.Title AS CategoryTitle, COUNT(co.PK_Comment) AS Comments, SUM(dl.Downloads) AS Downloads
FROM Artikel x
INNER JOIN users u ON x.FK_User = u.PK_User
JOIN categories c ON x.FK_Category = c.PK_Category
JOIN downloadLinks dl ON dl.FK_Artikel = x.PK_Artikel (dies ist die langsame Tablle)
LEFT OUTER JOIN comments co ON co.FK_Upload = x.PK_Upload
WHERE x.Approved = ?Approved AND x.FK_Rubric = ?FK_Rubric
GROUP BY dl.FK_Artikel, co.FK_Artikel
ORDER BY x.CreationTimestamp " + (newestFirst ? "DESC" : "ASC")
LIMIT ?Offset, ?Count
[/sql]
Table B SELECT:
[sql]
SELECT dl.PrivateTitle, dl.FK_Group, COUNT(dl.FK_Group) AS Count,
SUM(dl.Downloads) AS Downloads, SUM(dl.FileSize) AS Size,
dl.FK_System, s.System, s.Collection
FROM downloadlinks dl
INNER JOIN systems s ON s.PK_System = dl.FK_System
WHERE FK_Artikel = ?FK_Artikel
GROUP BY FK_Group
ORDER BY Downloads DESC, s.SortIndex ASC
[/sql]
So, lets talk about my problem: The query to search for items in Table A is working very fast (< 1 second). The query for searching in Table B is quite of the same structure and even structured more simply (as i think), but it tooks 1 to 2 minutes on my webserver. Table A has about 3500 Entries and Table B about 13000. Of course this 4 times more than in Table A, but i think the query has to work faster though...
Furtermore i realized that mysqldumper says Table A has an IndexLength of 160KB and Table B an IndexLength of 1.5MB. Why is Table B IndexLength so much greater with it has 1 PK less than Table A? I cannot understand where this difference comes from? Has this something to do with my problem?
I posted my question on another forum, but they could not help me.
In Addition i am not sure whether this is the right place for my thread. Maybe it has to bee moved to the InnoDB-Board...
Hopefully you understand what my problem is as i am not a native english speaker
Lets hope you can help me out with my problem :-)
Ciao and a nice evening to everyone :-)
Edited 1 time(s). Last edit at 01/01/2011 12:19PM by Roland Moch.