Geek Answers Handbook

Help me turn SUBQUERY into JOIN

Two tables.

letter id (int10) | property (int10)

Messages emailid (int10) is indexed | message (middle text)

Subquery (which is terrible in mysql).

SELECT COUNT (*) FROM messages WHERE message LIKE '% word%' AND emailID IN (SELECT id FROM emails WHERE ownership = 32)

The use here is that I run an email search (which is obviously simplified in the example above) that generates a list of, say, 3,000 email identifiers. Then I want to search the messages, because I need to perform a text match - from only 3000 letters from the message.

The request for messages is expensive (the message is not indexed), but this is normal because it will only check a few lines.

Ideas:

i) Connection. My attempts so far have not worked and have led to a full scan of the table of the message table (i.e. the email index was not used) ii) a temporary table. I think it might work. iii) cache identifiers in the client and the launch of 2 requests. It works. Not elegant. iv) subquery. MySQL subqueries run a second query every time, so this does not work. possibly fixed in mysql 6.

Ok, that’s what I still have. These are the actual field names (I simplified this question a bit).

Inquiry:

SELECT COUNT(*) FROM ticket LEFT JOIN ticket_subject 
ON (ticket_subject.ticketid = ticket.id) 
WHERE category IN (1) 
AND ticket_subject.subject LIKE "%about%"

Results:

1   SIMPLE  ticket  ref     PRIMARY,category    category    4   const   28874    
1   SIMPLE  ticket_subject  eq_ref  PRIMARY     PRIMARY     4   deskpro.ticket.id   1   Using where

It takes 0.41 seconds and returns a counter (*) of 113.

Duration:

SELECT COUNT (*) FROM ticket WHERE category IN (1)

It takes 0.01 seconds and finds 33,000 results.

Launch

SELECT COUNT (*) FROM ticket_subject WHERE subject LIKE "%about%"

Takes 0.14 seconds and finds 1300 results.

Both the ticket table and the ticket_subject table have 300,000 rows.

There is a pointer to ticket_subject.ticketid and ticket.category.

, LIKE , FULLTEXT. . :

1) A - , . 0,001 2) B - , - . 0,1 .

. , , 0,3 ; , B , , , , JOINED on .

+2
4

Boolean :

SELECT COUNT(*) 
FROM messages 
join emails ON emails.id = messages.emailid
WHERE ownership = 32 AND message LIKE '%word%'

ownership, LIKE. .

, @Martin Smith @MJB, MySQL FULLTEXT, .

, :

explain SELECT COUNT(*) FROM ticket WHERE category IN (1)\G

           id: 1
  select_type: SIMPLE
        table: ticket
         type: ref
possible_keys: category
          key: category
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index

" " - , , , , . .

explain SELECT COUNT(*) FROM ticket_subject WHERE subject LIKE '%about%'\G

           id: 1
  select_type: SIMPLE
        table: ticket_subject
         type: ALL
possible_keys: NULL        <---- no possible keys
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where

, , LIKE. WHERE, , .

explain SELECT COUNT(*) FROM ticket LEFT JOIN ticket_subject 
ON (ticket_subject.ticketid = ticket.id) 
WHERE category IN (1) 
AND ticket_subject.subject LIKE '%about%'\G

           id: 1
  select_type: SIMPLE
        table: ticket
         type: ref
possible_keys: PRIMARY,category
          key: category
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index

           id: 1
  select_type: SIMPLE
        table: ticket_subject
         type: ref
possible_keys: ticketid
          key: ticketid
      key_len: 4
          ref: test.ticket.id
         rows: 1
        Extra: Using where

, , , LIKE.

ALTER TABLE ticket_subject ENGINE=MyISAM;

CREATE FULLTEXT INDEX ticket_subject_fulltext ON ticket_subject(subject);

explain SELECT COUNT(*) FROM ticket JOIN ticket_subject  
ON (ticket_subject.ticketid = ticket.id)  
WHERE category IN (1)  AND MATCH(ticket_subject.subject) AGAINST('about')

           id: 1
  select_type: SIMPLE
        table: ticket
         type: ref
possible_keys: PRIMARY,category
          key: category
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index

           id: 1
  select_type: SIMPLE
        table: ticket_subject
         type: fulltext
possible_keys: ticketid,ticket_subject_fulltext
          key: ticket_subject_fulltext          <---- now it uses an index
      key_len: 0
          ref: 
         rows: 1
        Extra: Using where

LIKE. . MySQL.

: , ( :-). :

select count(*) from users
where reputation > 50000

+----------+
| count(*) |
+----------+
|       37 |
+----------+
1 row in set (0.00 sec)

, .

           id: 1
  select_type: SIMPLE
        table: users
         type: range
possible_keys: users_reputation_userid_displayname
          key: users_reputation_userid_displayname
      key_len: 4
          ref: NULL
         rows: 37
        Extra: Using where; Using index

select count(*) from badges
where badges.creationdate like '%06-24%'

+----------+
| count(*) |
+----------+
|     1319 |
+----------+
1 row in set, 1 warning (0.63 sec)

, , 700 . , . :

select count(*) from users join badges using (userid)
where users.reputation > 50000 and badges.creationdate like '%06-24%'

+----------+
| count(*) |
+----------+
|       19 |
+----------+
1 row in set, 1 warning (0.03 sec)

. :

           id: 1
  select_type: SIMPLE
        table: users
         type: range
possible_keys: PRIMARY,users_reputation_userid_displayname
          key: users_reputation_userid_displayname
      key_len: 4
          ref: NULL
         rows: 37
        Extra: Using where; Using index

           id: 1
  select_type: SIMPLE
        table: badges
         type: ref
possible_keys: badges_userid
          key: badges_userid
      key_len: 8
          ref: testpattern.users.UserId
         rows: 1
        Extra: Using where

, , . , MySQL , , .

: , , > 5000, > 500 > 50. .

select count(*) from users join badges using (userid)
where users.reputation > 5000 and badges.creationdate like '%06-24%'

+----------+
| count(*) |
+----------+
|      194 |
+----------+
1 row in set, 1 warning (0.27 sec)

select count(*) from users join badges using (userid)
where users.reputation > 500 and badges.creationdate like '%06-24%'

+----------+
| count(*) |
+----------+
|      624 |
+----------+
1 row in set, 1 warning (0.93 sec)

select count(*) from users join badges using (userid)
where users.reputation > 50 and badges.creationdate like '%06-24%'
--------------

+----------+
| count(*) |
+----------+
|     1067 |
+----------+
1 row in set, 1 warning (1.72 sec)

, "", , , LIKE "".

, . , . , .

, , , , , , , . creationdate_day DAYOFYEAR(creationdate).

:

ALTER TABLE Badges ADD COLUMN creationdate_day SMALLINT;
UPDATE Badges SET creationdate_day = DAYOFYEAR(creationdate);
CREATE INDEX badge_creationdate_day ON Badges(creationdate_day);

select count(*) from users join badges using (userid)
where users.reputation > 50 and badges.creationdate_day = dayofyear('2010-06-24')

+----------+
| count(*) |
+----------+
|     1067 |
+----------+
1 row in set, 1 warning (0.01 sec)  <---- not too shabby!

:

          id: 1
  select_type: SIMPLE
        table: badges
         type: ref
possible_keys: badges_userid,badge_creationdate_day
          key: badge_creationdate_day    <---- here is our new index
      key_len: 3
          ref: const
         rows: 1318
        Extra: Using where

           id: 1
  select_type: SIMPLE
        table: users
         type: eq_ref
possible_keys: PRIMARY,users_reputation_userid_displayname
          key: PRIMARY
      key_len: 8
          ref: testpattern.badges.UserId
         rows: 1
        Extra: Using where
+8
SELECT COUNT(*) 
FROM messages 
join emails ON emails.id = messages.emailid
WHERE message LIKE '%word%' 
AND ownership = 32

, '%word%' . , , MyISAM.

+3

, , :

select count(*)
from messages m
  inner join emails e
    on e.id = m.emailid
where m.message like '%word%'
  and e.ownership = 32

, . FTS - WORD, . , , , , .

+2

, ? , , , , , .

0

More articles:


All Articles