CODEWITHSUNDEEP
mysqldatabasedatabase-design
Michael
Michael

Reputation: 44230

Refinement to this MySQL query?

I've got a query which is taking a long time and I was wondering if there was a better way to do it? Perhaps with joins?

It's currently taking ~2.5 seconds which is way too long.

To explain the structure a little: I have products, "themes" and "categories". A product can be assigned any number of themes or categories. The themeitems and categoryitems tables are linking tables to link a category/theme ID to a product ID.

I want to get a list of all products with at least one theme and category. The query I've got at the moment is below:

SELECT *
FROM themes t, themeitems ti, products p, catitems ci, categories c
WHERE t.ID = ti.THEMEID 
AND ti.PRODID = p.ID 
AND p.ID = ci.PRODID 
AND ci.CATID = c.ID

I'm only actually selecting the rows I need when performing the query but I've removed that to abstract a little.

Any help in the right direction would be great!

Edit: EXPLAIN below

EXPLAIN

Upvotes: 0

Views: 201

Answers (5)

Raymond Nijland
Raymond Nijland

Reputation: 11602

Ive made an answer off this because i could not place it as an comment

Basic thumb off action if you want to remove FULL table scans with JOINS. You should index first.

Note that this not always works with ORDER BY/GROUP BY in combination with JOINS, because often an Using temporary; using filesort is needed.

Extra because this is out off the scope off the question and how to fix slow query with ORDER BY/GROUP BY in combination with JOIN

Because the MySQL optimizer thinks it needs to access the smallest table first to get the best execution what will cause MySQL cant always use indexes to sort the result and needs to use an temporary table and the filesort the fix the wrong sort ordering

(read more about this here MySQL slow query using filesort this is how i fix this problem because using temporary really can kill performance when MySQL needs an disk based temporary table)

Upvotes: 0

Neville Kuyt
Neville Kuyt

Reputation: 29649

Your query is slow because you don't have any indexes on your tables.

Try:

create unique index pk on themes (ID)
create index fk on themeitems(themeid, prodid)
create unique index pk on products (id)
create index fk catitems(prodid, catid)
create unique index pk on categories (id)

As @symcbean writes in the comments, the catitems and themeitems indices should probably be unique indices too - if there isn't another column to add to that index (e.g. "validityDate"), please add that to the create statement.

Upvotes: 1

symcbean
symcbean

Reputation: 48387

Simply selecting less data is the glaringly obvious solution here.

Why do you need to know every column and every row every time you run the query? Addressing any one of these 3 factors will improve performance.

I want to get a list of all products with at least one theme and category

That rather implies you don't care which theme and category, in which case.....

SELECT p.*
FROM themeitems ti, products p, catitems ci
WHERE p.ID = ti.PRODID  
AND p.ID = ci.PRODID

It may be possible to make the query run significantly faster - but you've not provided details of the table structure, the indexes, the volume of data, the engine type, the query cache configuration, the frequency of data updates, the frequency with which the query is run.....

update

Now that you've provided the explain plan then it's obvious you've got very small amounts of data AND NO RELEVENT INDEXES!!!!!

As a minimum you should add indexes on the product foreign key in the themeitems and catitems tables. Indeed, the primary keys for these tables should be the product id and category id / theme id, and since it's likely that you will have more products than categories or themes then the fields should be in that order in the indexes. (i.e. PRODID,CATID rather than CATID, PRODID)

update2

Given the requirement "to get a list of all products with at least one theme and category", it might be faster still (but the big wins are reducing the number of joins and adding the right indexes) to....

SELECT p.*
FROM product p
INNER JOIN (
    SELECT DISTINCT ti.PRODID
    FROM themeitems ti, catitems ci
    WHERE ti.PRODID=ci.PRODID
) i ON p.id=i.PRODID

Upvotes: 0

cgon
cgon

Reputation: 1981

Your query is very simple. I do not think that your cost decreases with implementing joins. You can try putting indexes to appropriate columns

Upvotes: 0

Steph Locke
Steph Locke

Reputation: 6146

Utilise correct JOINs and ensure there are indexes on the fields used in the JOIN is the standard response for this issue.

SELECT *
FROM themes t
INNER JOIN themeitems ti ON t.ID = ti.THEMEID
INNER JOIN products p ON ti.PRODID = p.ID
INNER JOIN catitems ci ON p.ID = ci.PRODID
INNER JOIN categories c ON ci.CATID = c.ID

The specification of the JOINs assists the query engine in working out what it needs to do, and the indexes on the columns used in the join, will enable more rapid joining.

Upvotes: 1

Related Questions