Optimise Query

[isedeasy]

I have the following query which works just fine until I have a few hundred rows in one of the joined tables, then it takes over 30 sec to load the page.

 

How can I optimise this query? I have certain columns indexed.       

SELECT
a.id,
COUNT(DISTINCT b.id) AS table1no,
COUNT(DISTINCT c.id) AS table2no,
COUNT(DISTINCT d.id) AS table3no,
COUNT(DISTINCT e.id) AS table4no
FROM table a
LEFT JOIN table1 AS b ON b.user = a.id
LEFT JOIN table2 AS c ON c.user = a.id AND c.deleted = 0
LEFT JOIN table3 AS d ON d.user = a.id AND d.status != 2 AND d.stage = 5
LEFT JOIN table4 AS e ON e.user = a.id AND e.status != 2 AND e.stage = 5
WHERE a.id = $id

 

 

[requinix]

It seems like you're trying to do 5 different queries at the same time. What if you split them apart?

[mikosiko]

@isedeasy

 

To get better help

 

- What is the  EXPLAIN result for that sentence?

- Post Tables structure and Index definitions... complement with number of rows in each table

[fenway]

You know, there's a sticky that describes exactly what information we'll need to help you.

[kickstart]

Hi

 

Couple of thing not really to do with optimisation.

 

Looks like you need a group by clause.

 

Assuming b.id, etc, are primary keys and so unique there is no need to use distinct.

 

You don't need the AS when aliasing the table names

 

Efficiency wise it is going to come down to whether b.user, etc, are set up foreign keys.

 

All the best

 

Keith

[isedeasy]

Thanks for all the replies so far, they have all been very helpful.

 

I have been looking into foreign keys and I have completely redone all my keys/indexes to use foreign keys where applicable.

 

The problem I have at the moment is my comment system, I have 3 different types of items that users can comment on  so I have a column for the items id and a column for the type of item (because the id's are auto increment). therefore all the comments are in one table.

 

Should I use a separate table for each type of item users can comment on? that way I can use foreign keys to link the row with the items ID or should I keep it as it is and just have an index consisting of item combined with id?

 

tableA - id

tableB - id

tableC - id

 

CommentsTable - item (either tableA, tableB, or tableC), id

 

Hope that makes sense

[mikosiko]

In my first reply to you I wrote:

- Post Tables structure and Index definitions... complement with number of rows in each table

... no information provided...

 

now....

I have 3 different types of items that users can comment

based on that and your mention of tables (A, B, C) make me suspicious about if your data modeling is correct or not.... for a better answer post your tables structure and explain a little what you are doing....

 

[isedeasy]

That's my point I want the correct structure. Shall I have a separate comment table for each of the three types or one comment table with a column for the type?

[mikosiko]

post the structures of your tables A,B y C

[isedeasy]

CREATE TABLE `comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` int(6) NOT NULL,
`item` int(6) NOT NULL,
`type` int(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `user` (`user`),
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`user`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `item1` (
`id` int(6) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `item2` (
`id` int(6) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `item3` (
`id` int(6) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

I'm not at home now so will this watered down version do?

 

I have it set up so that 1 in the comments.type column refers to item1, 2 in the comments.type column refurs to item2 etc

 

So would be best for each item to have its own comments table or is there a way to use foreign keys for comments.item to reference the id of the items?

[isedeasy]

Does anyone have an answer to my last question?

 

"Shall I have a separate comment table for each of the three types or one comment table with a column for the type?"

[fenway]

It's always simpler to have a shared table.

[isedeasy]

It's always simpler to have a shared table.

 

Ok thank you.

 

How should I go about keys for this table?

 

CREATE TABLE `comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` int(6) NOT NULL,
`item` int(6) NOT NULL,
`type` int(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `user` (`user`),
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`user`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

Is there away to use a foriegn key for the`item` column as it can be joined with different tables?

 

 

[fenway]

Is there away to use a foriegn key for the`item` column as it can be joined with different tables?

Nope.  If you really want that, you'll need a "parent" table, and then subsequent child tables.