Jump to content

Trouble with EXPLAIN


shamoon

Recommended Posts

So I have a query... the MySQL Slow log caught it and outputted:

# Time: 071031 11:31:51
# User@Host: bookswim[bookswim] @ D3350.servadmin.com [12.47.45.169]
# Query_time: 33  Lock_time: 0  Rows_sent: 100  Rows_examined: 163196
use bsdb;
SELECT b.title, b.title_html, b.authors, b.description, b.book_id, b.cover_id, b.publication_date, b.avg_rating AS rating, IF( b.total_rentals > 0, b.copies_owned / b.total_rentals, 0) AS usage_ratio
			FROM books b, books_to_categories AS btc
			WHERE b.total_rentals > 0 AND btc.node_id='17' AND btc.book_id=b.book_id GROUP BY b.book_id ORDER BY usage_ratio DESC, copies_effective DESC, publication_date DESC  LIMIT 0, 100;

 

It looks like it has to examine 163K rows.  The odd thing is when I do an explain on it.  The results are far less.

 

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	btc 	ref 	book_id,node_id 	node_id 	32 	const 	26234 	Using where; Using temporary; Using filesort
1 	SIMPLE 	b 	ref 	PRIMARY,book_id 	PRIMARY 	22 	bsdb.btc.book_id 	1 	Using where

 

Only 26K rows.  Why the difference?

Link to comment
Share on other sites

Sorry, I mean ANALYZE... OPTIMIZE is the expensive one, and rarely needs to be run (especially if you're not deleting records).

 

Also:

Estimated number of rows may be very inaccurate It is well known this number is aproximate but I guess not everyone knows how much wrong it could be. If number of rows is estimated performing btree dive with

certainly structured tree you still can get number of rows off 3-10 times. It gets even worse if cardinality is used. In this case if data distribution is skewed and you statement looks at portion of data with different distribution results can be off by 10-100 times and even more.

Link to comment
Share on other sites

By the looks of it, after further examination you want the top 100 books where the total_rentals is > 0 and book_to_category.node_id is 17, implying you want the top 100 books for a particular category.

Also, i'm assuming you have the following relationship book -> book_to_categories -> book_categories, i.e. one -> many -> one or a nomalised many -> many relationship.

 

My questions are this :

1) How many books do you have?

2) How many categories do you have?

3) Can 1 book be part of many categories (and vice versa)?

 

 

Link to comment
Share on other sites

My questions are this :

1) How many books do you have?

Over 200K

 

2) How many categories do you have?

Each category can be a child of other categories, so after all is said and done there are 15K categories

 

3) Can 1 book be part of many categories (and vice versa)?

Yes.  One book can be part of SEVERAL categories.

 

The reason for the GROUP BY, by the way, is to ensure that I get unique book_id's.  Is there another way?

Link to comment
Share on other sites

So if you do the following what do you get :

SELECT COUNT(book_id)
FROM books_to_categories
WHERE node_id = 17
UNION 
SELECT COUNT(book_id)
FROM books_to_categories
WHERE node_id = 17
GROUP BY book_id

 

Note how i have put 17 in as an INT and NOT a '17' VARCHAR ;)

Link to comment
Share on other sites

Depends what slack sort of settings you have in MySQL. He's obviously not got STRICT_TRANS_TABLES set on his SQL mode settings. Port over his PHP to another database system and you might find he starts having errors  :o

 

Hmm, i stand corrected, i've just tested that and it's not the case. I could have sworn it throws an error...

You re-learn something new every day.

Link to comment
Share on other sites

So if you do the following what do you get :

SELECT COUNT(book_id)
FROM books_to_categories
WHERE node_id = 17
UNION 
SELECT COUNT(book_id)
FROM books_to_categories
WHERE node_id = 17
GROUP BY book_id

 

Note how i have put 17 in as an INT and NOT a '17' VARCHAR ;)

 

The explain is:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	books_to_categories 	ALL 	node_id 	NULL 	NULL 	NULL 	4310306 	Using where
2 	UNION 	books_to_categories 	ALL 	node_id 	NULL 	NULL 	NULL 	4310306 	Using where; Using temporary; Using filesort
NULL 	UNION RESULT 	<union1,2> 	ALL 	NULL 	NULL 	NULL 	NULL 	NULL 	 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.