help with explain, please?

fandelem

I don't understand how this is doing a filesort, etc. Could someone please enlighten me??

Apologies for the wrapping, but I figured it would be more readable then..

#
# Table structure for table 'quotesland_quotes'
#

CREATE TABLE quotesland_quotes (
  id mediumint(16) NOT NULL auto_increment,
  author_id int(16) default NULL,
  category_id int(16) default NULL,
  quote mediumtext,
  submitted_by varchar(75) default NULL,
  lastviewed datetime NOT NULL default '0000-00-00 00:00:00',
  viewed bigint(20) default NULL,
  pending char(1) default NULL,
  PRIMARY KEY  (id),
  KEY index_author_id (author_id),
  KEY index_category_id (category_id),
  KEY index_pending (pending),
  KEY index_quote (quote(250)),
  KEY index_viewed (viewed)
) TYPE=MyISAM;

#
# Table structure for table 'quotesland_authors'
#

CREATE TABLE quotesland_authors ( 
  id int(16) NOT NULL auto_increment, 
  author varchar(75) default NULL,
  first varchar(30) default NULL,
  last varchar(30) default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY index_artist (author),
  KEY index_lastfirst (last,first)
) TYPE=MyISAM;

#
# Table structure for table 'quotesland_categories'
#

CREATE TABLE quotesland_categories (
  id int(16) NOT NULL default '0',    

  category varchar(75) default NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

And here is the explain:

[size=1]
explain SELECT a.id,a.author_id,a.category_id,a.quote,b.author,c.category 
          FROM quotesland_quotes as a, quotesland_authors as b, quotesland_categories as c
           where a.author_id = b.id and a.category_id = c.id
           order by viewed desc limit 5

(apologies for the wrap)
+-------+--------+-----------------------------------+-----------------+---------+---------------+------+---------------------------------+
| table | type   | possible_keys                     | key             | key_len | ref           | rows | Extra                           |
+-------+--------+-----------------------------------+-----------------+---------+---------------+------+---------------------------------+
| b     | ALL    | PRIMARY                           | NULL            |    NULL | NULL          | 5337 | Using temporary; Using filesort |
| a     | ref    | index_author_id,index_category_id | index_author_id |       5 | b.id          |    2 |                                 |
| c     | eq_ref | PRIMARY                           | PRIMARY         |       4 | a.category_id |    1 |                                 |
+-------+--------+-----------------------------------+-----------------+---------+---------------+------+---------------------------------+
[/size]

nemonoman

As I read it, MySQL can do indexed searches on table a and c, which will (quickly) return 1 row and two rows respectively.

MySQL's plan is to build a temporary table with no NULLs (in effect

SELECT * FROM quotesland_authors WHERE author IS NOT NULL

It will FILESORT that table -- I don't see why is it should, but that's it's PLAN.

It will then find those rows that match values returned in the table a and c searches, doing a row by row comparison of the temporary table.

I'm not great at reading plans of this kind, but this is what I think it says, and I'd be glad to be enlightened if I have it wrong.

I'm assuming the temporary table is due to these table definitions of quotesland_authors:

author varchar(75) default NULL,

and

UNIQUE KEY index_artist (author)

MySQL seems concerned about a UNIQUE KEY on a NULL column.

This is an unexpected plan, IMO, since column author isn't part of any join.

Does the query run slow?

Does anybody have any advice on how to TUNE MySQL queries?

For my part, I'd try defining column author as NOT NULL and seeing if effects are any different. On some databases there's a significant performance difference between columns with values '' and NULL.

fandelem

The new table definition of quotesland_authors is:

CREATE TABLE quotesland_authors (
  id int(16) NOT NULL auto_increment,
  author varchar(75) not null default '',
  first varchar(30) not null default '',
  last varchar(30) not null default '',
  PRIMARY KEY  (id),
  UNIQUE KEY index_artist (author),
  KEY index_lastfirst (last,first)
) TYPE=MyISAM;

and I still receive the same explain statement back..

What I don't understand is how it finds the 'possible keys' of PRIMARY (the autoincrementing id field) which is what my where clause is based on (a.author_id = b.id .. where b.id is this PRIMARY key I'm talking about) but then the key it finds is NULL. I have even tried having the table definition define the 'id' field as defaulting to something other than NULL and it still yields the same results. I am baffled. Can anyone add any insight?

cheers,

fandelem

Just FYI for any of those who are doing something similar to what I was doing (grabbing from three different tables) I did in fact find a faster SQL query (difference of about .50 seconds per query to about .04 seconds, which if you have 1000 queries a second, is quite noticable...)

new:

SELECT A.id 
     , A.author_id
     , A.category_id
     , A.quote
     , B.author
     , C.category
FROM quotesland_quotes      AS A 
LEFT JOIN quotesland_authors     AS B 
ON   A.author_id = B.id 
LEFT JOIN quotesland_categories   AS C 
ON   A.category_id = C.id 
ORDER BY viewed DESC LIMIT 5; 

5 rows in set (0.10 sec)

and the old version:

SELECT A.id
     , A.author_id
     , A.category_id
     , A.quote
     , B.author
     , C.category
FROM quotesland_quotes      AS A 
   , quotesland_authors     AS B 
   , quotesland_categories  AS C 
WHERE A.author_id  = B.id 
  AND A.category_id = C.id
ORDER BY viewed DESC LIMIT 5 

5 rows in set (0.54 sec)

nemonoman

Fandelem: thanks for the update.

I'm surprised by your results, for when I learned SQL w.Oracle, back in the stone age, we were taught that best practice was to avoid LEFT JOINs.

Would you kindly send along the EXPLAIN of your newer query? I'd like to see the difference.

Also, what suggested a LEFT JOIN strategy? A good move, apparently.

In any case, good for you to bang your way through this.

fandelem

Explain follows below. I would first like to point out that as I am a very strong advocate of poking through until I get something, I really have a crude understanding of MySQL (in the more intricate areas, such as these) -- and I honestly don't know why I chose LEFT JOIN over anything else, other than I saw an example on mysql.com about it. If you have any information on mysql JOINs (better than what google can produce about a rinky-dink table and a slight-glazing over the topic such as mysql does) I would be very happy. Perhaps even a great book in the general SQL realm that goes into great depths in this area...?

NEW:

[size=1]
+-------+--------+---------------+--------------+---------+---------------+-------+-------+
| table | type   | possible_keys | key          | key_len | ref           | rows  | Extra |
+-------+--------+---------------+--------------+---------+---------------+-------+-------+
| A     | index  | NULL          | index_viewed |       8 | NULL          | 12009 |       |
| B     | eq_ref | PRIMARY       | PRIMARY      |       4 | A.author_id   |     1 |       |
| C     | eq_ref | PRIMARY       | PRIMARY      |       3 | A.category_id |     1 |       |
+-------+--------+---------------+--------------+---------+---------------+-------+-------+
3 rows in set (0.00 sec)
[/size]

edit: While this explain was VERY misleading at first, because it says '12009' results, keep in mind that it is only grabbing the first 5 (due to my limit clause) which makes this faster (at least that is what I think).

cheers,