Help optimizing query

2004-11-23 Thread Jesse Sheidlower 

I have what I thought was a simple, well-indexed query, but it
turns out that it's acting as a pretty big drag. The one thing
that's clearly a problem (though I'm not sure of the extent of
the problem), I'm not sure how to fix.

There are three tables: citations, subjects, and a many-to-many
table linking these. They look like this (edited to remove
extraneous fields):

CREATE TABLE `citation` (
  `id` int(11) NOT NULL auto_increment,
  `word` varchar(50) NOT NULL default '',
  `last_modified` timestamp(14) NOT NULL,
  `deleted` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `deleted` (`deleted`),
  KEY `word` (`word`)
)

CREATE TABLE `subject` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `last_modified` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`)
) 
CREATE TABLE `citation_subject` (
  `id` int(11) NOT NULL auto_increment,
  `citation_id` int(11) NOT NULL default '0',
  `subject_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `citation_id` (`citation_id`,`subject_id`)
) 

A usual query is to get (some number of) the 
citations for a given subject, ordering by the
word which is stripped of spaces and hyphens.
I don't know of any other way to accomplish
this ordering. The EXPLAIN looks like this:

mysql EXPLAIN SELECT citation.*, REPLACE(REPLACE(citation.word,' ',''), 
'-','') AS stripped_word
- FROM citation, subject, citation_subject
- WHERE subject.name = 'History'
- AND citation_subject.subject_id = subject.id
- AND citation_subject.citation_id = citation.id
- AND (citation.deleted IS NULL OR citation.deleted = 0)
- ORDER BY stripped_word\G
*** 1. row ***
table: subject
 type: ref
possible_keys: PRIMARY,name
  key: name
  key_len: 50
  ref: const
 rows: 1
Extra: Using where; Using temporary; Using filesort
*** 2. row ***
table: citation_subject
 type: index
possible_keys: citation_id
  key: citation_id
  key_len: 8
  ref: NULL
 rows: 1247
Extra: Using where; Using index
*** 3. row ***
table: citation
 type: eq_ref
possible_keys: PRIMARY,deleted
  key: PRIMARY
  key_len: 4
  ref: citation_subject.citation_id
 rows: 1
Extra: Using where
3 rows in set (0.00 sec)



The number of records involved is relatively small (a few thousands; in
practice this query would also have a LIMIT statement and would be
preceded by a COUNT(*)), but it's dragging down the application it's
running in. (I have a considerably more complex query that involves
millions of records and twice as many joins, that is faster.) I'm
running this in Perl.

Any suggestions? I'd like to get rid of the whole temporary and
filesort things, but I'm not sure if that's what matters given
that there's only 1 row being returned there.

Thanks.

Jesse Sheidlower

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help optimizing query

2004-11-23 Thread roger 
* Jesse Sheidlower
[...]
 CREATE TABLE `citation_subject` (
   `id` int(11) NOT NULL auto_increment,
   `citation_id` int(11) NOT NULL default '0',
   `subject_id` int(11) NOT NULL default '0',
   PRIMARY KEY  (`id`),
   KEY `citation_id` (`citation_id`,`subject_id`)
 )

Try adding an index with subject_id as the first column.

ALTER TABLE `citation_subject` ADD INDEX (`subject_id`,`citation_id`);

-- 
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help optimizing query

2004-11-23 Thread Jesse Sheidlower 
On Tue, Nov 23, 2004 at 09:55:15PM +0100, [EMAIL PROTECTED] wrote:
 * Jesse Sheidlower
 [...]
  CREATE TABLE `citation_subject` (
`id` int(11) NOT NULL auto_increment,
`citation_id` int(11) NOT NULL default '0',
`subject_id` int(11) NOT NULL default '0',
PRIMARY KEY  (`id`),
KEY `citation_id` (`citation_id`,`subject_id`)
  )
 
 Try adding an index with subject_id as the first column.
 
 ALTER TABLE `citation_subject` ADD INDEX (`subject_id`,`citation_id`);

Thanks. This did help slightly--I didn't realize that the
order of this would make such a difference, if both were
always being used.

I'm now coming to the determination that there are other
parts of the application functioning as the biggest drags.
If this is so, I apologize for the wasted bandwidth; I'm
still poking at this query though.

Jesse Sheidlower

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Need help optimizing query

2004-07-15 Thread Patrick Drouin 
 Hello everyone,

I'm having a hard time with the following query. It
retrieves about 3K rows from a few tables. One of them
contains over 40M rows. When run on a 3Ghz server with
1G of RAM it returns the rows in more than 1 mini. I
don't think that's normal.

Here's the output of EXPLAIN:

mysql explain SELECT ti.posi, ti.docid, d.filename,
ti.id, c.name FROM corpus_documents cd, corpus c,
documents d, tokens_ins ti, tokens t WHERE
c.corpusid=4 AND cd.corpusid=c.corpusid AND
cd.docid=d.docid AND t.docid=d.docid AND ti.id=t.id
AND t.word='police' AND t.docid=ti.docid\g;

He
+---++--+---+-+---+--+--+|
table | type | possible_keys | key | key_len | ref |
rows | Extra
|+---++--+---+-+---+--+--+|
c | ref | PRIMARY,corpus_corpusid | PRIMARY | 3 |
const | 1 | Using where; Using index || t | ref |
PRIMARY,tokens_id,tokens_docid,tokens_word,tokens_word_docid
| PRIMARY | 30 | const | 24 | Using where || ti | ref
| PRIMARY,tokens_ins_id,tokens_ins_docid |
tokens_ins_id | 4 | t.id | 96 | Using where || d | ref
| PRIMARY,documents_docid | PRIMARY | 3 | t.docid | 3
| Using index || cd | eq_ref | PRIMARY | PRIMARY | 6 |
const,d.docid | 1 | Using where; Using index
|+---++--+---+-+---+--+--+
5 rows in set (0.00 sec)

It seems to be using indexes as expected and it does
not seem to look at that many rows. Here's tthe query
chewed up and nicely displayed:

SELECT ti.posi, ti.docid, d.filename, ti.id, c.name
FROM corpus_documents cd, corpus c, documents d,
tokens_ins ti, tokens t
WHERE c.corpusid=4
AND cd.corpusid=c.corpusid
AND cd.docid=d.docid
AND t.docid=d.docid
AND ti.id=t.id
AND t.word='police'
AND t.docid=ti.docid;
...
3791 rows in set (1 min 29.78 sec)

Here are descriptions of the tables at play :
mysql desc tokens_ins;
+--+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+---+--+-+-+---+
| id | int(11) | | MUL | 0 | |
| posi | int(11) | | PRI | 0 | |
| docid | mediumint(20) | | PRI | 0 | |
| originalspelling | varchar(30) | | | | |
+--+---+--+-+-+---+
4 rows in set (0.02 sec)


mysql desc tokens;
+-++--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-++--+-+-+---+
| id | int(11) | | MUL | 0 | |
| docid | mediumint(20) | | PRI | 0 | |
| word | varchar(30) binary | | PRI | | |
| pos | varchar(10) | | PRI | 0 | |
| absfreq | mediumint(20) | | MUL | 0 | |
+-++--+-+-+---+
5 rows in set (0.00 sec)

mysql desc corpus;
+--+---+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+--+---+--+-+-++
| corpusid | mediumint(20) | | PRI | NULL |
auto_increment |
| name | varchar(30) | | PRI
|+-+-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-+-+--+-+-+---+
| docid | mediumint(20) | | PRI | 0 | |
| filename | varchar(30) | | PRI | | |
| language | char(3) | | | | |
| description | varchar(255) binary | YES | | NULL | |
+-+-+--+-+-+---+
4 rows in set (0.00 sec)


mysql desc corpus_documents;
+--+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+---+--+-+-+---+
| corpusid | mediumint(20) | | PRI | 0 | |
| docid | mediumint(20) | | PRI | 0 | |
+--+---+--+-+-+---+
2 rows in set (0.00 sec)

Can anybody give me a hand speeding up this ting? I'm
running out of ideas.

Thanks,
P | |
| language | char(3) | | MUL | | |
+--+---+--+-+-++
3 rows in set (0.00 sec)
mysql desc documents;








__
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need help optimizing query

2004-07-15 Thread Arnaud 
On 15 Jul 2004 at 6:27, Patrick Drouin [EMAIL PROTECTED] 
wrote:

 I'm having a hard time with the following query. It
 retrieves about 3K rows from a few tables. One of them
 contains over 40M rows. When run on a 3Ghz server with
 1G of RAM it returns the rows in more than 1 mini. I
 don't think that's normal.

Your indexes look good, but I see that you have some varchar fields. 
Maybe
you could run an optimize table on these tables? Also, you are 
talking
about a 40M rows table. If it is a read only MyISAM table, I might 
try
compression.

Arnaud

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need help optimizing query

2004-07-15 Thread Patrick Drouin 
Bonjour Arnaud,

--- Arnaud [EMAIL PROTECTED] wrote:
 On 15 Jul 2004 at 6:27, Patrick Drouin
 Your indexes look good, but I see that you have some
 varchar fields. 
 Maybe
 you could run an optimize table on these tables?

I'm running it at the moment, I will follow-up on the
list when it's done. It could take a while I guess. 

 Also, you are 
 talking
 about a 40M rows table. If it is a read only MyISAM
 table, I might 
 try
 compression.

 It's mainly read-only, I sometimes batch load some
data but users don't update at all. I'll look into
compression and see what it is about.

Thanks,
Patrick

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Need help optimizing query

2004-07-15 Thread Victor Pendleton 
What version of MySQL are you using? Have you checked the cardinality on
these tables?

-Original Message-
From: Patrick Drouin
To: [EMAIL PROTECTED]
Sent: 7/15/04 8:27 AM
Subject: Need help optimizing query

 Hello everyone,

I'm having a hard time with the following query. It
retrieves about 3K rows from a few tables. One of them
contains over 40M rows. When run on a 3Ghz server with
1G of RAM it returns the rows in more than 1 mini. I
don't think that's normal.

Here's the output of EXPLAIN:

mysql explain SELECT ti.posi, ti.docid, d.filename,
ti.id, c.name FROM corpus_documents cd, corpus c,
documents d, tokens_ins ti, tokens t WHERE
c.corpusid=4 AND cd.corpusid=c.corpusid AND
cd.docid=d.docid AND t.docid=d.docid AND ti.id=t.id
AND t.word='police' AND t.docid=ti.docid\g;

He
+---++--
+---+-+---+--+--
+|
table | type | possible_keys | key | key_len | ref |
rows | Extra
|+---++-
-+---+-+---+--+-
-+|
c | ref | PRIMARY,corpus_corpusid | PRIMARY | 3 |
const | 1 | Using where; Using index || t | ref |
PRIMARY,tokens_id,tokens_docid,tokens_word,tokens_word_docid
| PRIMARY | 30 | const | 24 | Using where || ti | ref
| PRIMARY,tokens_ins_id,tokens_ins_docid |
tokens_ins_id | 4 | t.id | 96 | Using where || d | ref
| PRIMARY,documents_docid | PRIMARY | 3 | t.docid | 3
| Using index || cd | eq_ref | PRIMARY | PRIMARY | 6 |
const,d.docid | 1 | Using where; Using index
|+---++-
-+---+-+---+--+-
-+
5 rows in set (0.00 sec)

It seems to be using indexes as expected and it does
not seem to look at that many rows. Here's tthe query
chewed up and nicely displayed:

SELECT ti.posi, ti.docid, d.filename, ti.id, c.name
FROM corpus_documents cd, corpus c, documents d,
tokens_ins ti, tokens t
WHERE c.corpusid=4
AND cd.corpusid=c.corpusid
AND cd.docid=d.docid
AND t.docid=d.docid
AND ti.id=t.id
AND t.word='police'
AND t.docid=ti.docid;
...
3791 rows in set (1 min 29.78 sec)

Here are descriptions of the tables at play :
mysql desc tokens_ins;
+--+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+---+--+-+-+---+
| id | int(11) | | MUL | 0 | |
| posi | int(11) | | PRI | 0 | |
| docid | mediumint(20) | | PRI | 0 | |
| originalspelling | varchar(30) | | | | |
+--+---+--+-+-+---+
4 rows in set (0.02 sec)


mysql desc tokens;
+-++--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-++--+-+-+---+
| id | int(11) | | MUL | 0 | |
| docid | mediumint(20) | | PRI | 0 | |
| word | varchar(30) binary | | PRI | | |
| pos | varchar(10) | | PRI | 0 | |
| absfreq | mediumint(20) | | MUL | 0 | |
+-++--+-+-+---+
5 rows in set (0.00 sec)

mysql desc corpus;
+--+---+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+--+---+--+-+-++
| corpusid | mediumint(20) | | PRI | NULL |
auto_increment |
| name | varchar(30) | | PRI
|+-+-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-+-+--+-+-+---+
| docid | mediumint(20) | | PRI | 0 | |
| filename | varchar(30) | | PRI | | |
| language | char(3) | | | | |
| description | varchar(255) binary | YES | | NULL | |
+-+-+--+-+-+---+
4 rows in set (0.00 sec)


mysql desc corpus_documents;
+--+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+---+--+-+-+---+
| corpusid | mediumint(20) | | PRI | 0 | |
| docid | mediumint(20) | | PRI | 0 | |
+--+---+--+-+-+---+
2 rows in set (0.00 sec)

Can anybody give me a hand speeding up this ting? I'm
running out of ideas.

Thanks,
P | |
| language | char(3) | | MUL | | |
+--+---+--+-+-++
3 rows in set (0.00 sec)
mysql desc documents;








__
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

RE: Need help optimizing query

2004-07-15 Thread Patrick Drouin 
Hello Victor,

 What version of MySQL are you using? Have you
 checked the cardinality on
 these tables?

Problem solved! Optimizing the table brought the query
time down to 17 secs Wow! 

Thanks for the input Victor and merci to Arnaud for
the  quick fix.

Patrick



__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need help optimizing query, awfully slow on only 20000 records

2003-08-21 Thread SAQIB 
Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html

Saqib Ali
-
http://www.xml-dev.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need help optimizing query, awfully slow on only 20000 records

2003-08-21 Thread Apollo (Carmel Entertainment) 
Absolutely! I have multiple indexes. I think it might be a problem with ODBC

 Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html
 
 Saqib Ali
 -
 http://www.xml-dev.com
 
 


-
Visit CARMEL MUSIC  ENTERTAINMENT website http://carmelme.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Need help optimizing query, awfully slow on only 20000 records

2003-08-20 Thread Mechain Marc 
Two Questions:

Is the same query running directly on the linux server thru mysql is also very slow ?

Have you done a explain plan on the query ?

Marc.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Need help optimizing query, awfully slow on only 20000 records

2003-08-20 Thread Apollo (Carmel Entertainment) 
When following query is pulled up, it takes about whole 1-2 minutes to come up.
inquiries table has 17000 records, contacts has about 7000,
individual_contacts has about 16000.
It has gotten worse once I upgraded to 4.0 and latest MyODBC.
Clients are separate machines (mix of Win98 and WinXP). Those 20K records is
what feeds the Access97 form, pull down list filters out some and pulls up about
3K and people just start typing a name and then (since there multiple inquiries
for some clients) pull down the list to pick inquiry they want.

 What are you defining as a huge performance hit?
 
 Is the result set 20K records, or the base tables? How big are the base
 tables?
 
 Are the client and server on the same machine? Pulling 20K records across
 the network could take some time. Formatting 20K records into a pull -down
 list in access will also take a long time. Anyway who reads a 20K list?
 
 Which parts of the process are slow? How does the query perform from the
 mysql command line?
 
  -Original Message-
  From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED]
  Sent: 19 August 2003 17:29
  To: [EMAIL PROTECTED]
  Subject: Need help optimizing query, awfully slow on only
  2 records
 
 
  1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0
 
  I have about 20K records that result from the following
  query. Front end for the
  database is ACCESS97 and pulling up 20K records makes a huge
  performance hit.
  For the form in question I am using PASSTHROUGH type query
  (the one that just
  passes everything straight to server without ODBC).
  NOTE: souce_for_inquiries_form is the join table and is
  searchable in the from
  (it feeds a pull-down list).
 
  SELECT inquiries.inquiry_id, inquiries.contact_id,
  inquiries.indiv_contact_id,
  inquiries.phone, inquiries.fax, inquiries.agent_id,
  inquiries.inquiry_date,
  inquiries.event_type, inquiries.letter_type, inquiries.event_date,
  inquiries.event_date_general, inquiries.event_location,
  inquiries.guests,
  inquiries.hours, inquiries.budget, inquiries.event_description,
  inquiries.talent_description, inquiries.past_use,
  inquiries.referred_by,
  inquiries.date_sent, inquiries.end_user, inquiries.event_id,
  inquiries.notes,
  source_for_inquiries_form.organization,
  source_for_inquiries_form.mailing_address_1,
  source_for_inquiries_form.mailing_address_2,
  source_for_inquiries_form.city,
  source_for_inquiries_form.state, source_for_inquiries_form.zip,
  source_for_inquiries_form.contact_type,
  individual_contacts.contact_name_first,
  individual_contacts.contact_name_last,
  individual_contacts.contact_prefix,
  individual_contacts.contact_title, individual_contacts.email
  FROM inquiries
  LEFT JOIN individual_contacts ON inquiries.indiv_contact_id =
  individual_contacts.indiv_contact_id
  INNER JOIN contacts AS source_for_inquiries_form ON
  inquiries.contact_id =
  source_for_inquiries_form.contact_id
  ORDER BY  inquiries.inquiry_id DESC;
 
  -



-
Visit CARMEL MUSIC  ENTERTAINMENT website http://carmelme.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Need help optimizing query, awfully slow on only 20000 records

2003-08-20 Thread Apollo (Carmel Entertainment) 

Result of EXPLAIN is:
table|type|possible_keys|key|key_len|ref|rows|Extra
inquiries|ALL|contact_id| | | |8253|Using filesort 
individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3|inquiries.indiv_contact_id|1

source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3|inquiries.contact_id|1
 


 What does EXPLAIN SELECT query show?  Have you read the chapter in the
 manual on optimizing queries?  Do you have all the proper indices set
 up?
 
 --Michael
 
  -Original Message-
  From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] 
  Sent: Wednesday, August 20, 2003 2:52 PM
  To: [EMAIL PROTECTED]
  Cc: [EMAIL PROTECTED]
  Subject: RE: Need help optimizing query, awfully slow on only 
  2 records
  
  
  When following query is pulled up, it takes about whole 1-2 
  minutes to come up. inquiries table has 17000 records, 
  contacts has about 7000, individual_contacts has about 
  16000. It has gotten worse once I upgraded to 4.0 and latest 
  MyODBC. Clients are separate machines (mix of Win98 and 
  WinXP). Those 20K records is what feeds the Access97 form, 
  pull down list filters out some and pulls up about 3K and 
  people just start typing a name and then (since there 
  multiple inquiries for some clients) pull down the list to 
  pick inquiry they want.
  
   What are you defining as a huge performance hit?
   
   Is the result set 20K records, or the base tables? How big are the 
   base tables?
   
   Are the client and server on the same machine? Pulling 20K records 
   across the network could take some time. Formatting 20K 
  records into a 
   pull -down list in access will also take a long time. 
  Anyway who reads 
   a 20K list?
   
   Which parts of the process are slow? How does the query 
  perform from 
   the mysql command line?
   
-Original Message-
From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED]
Sent: 19 August 2003 17:29
To: [EMAIL PROTECTED]
Subject: Need help optimizing query, awfully slow on only 2 
records
   
   
1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0
   
I have about 20K records that result from the following 
  query. Front 
end for the database is ACCESS97 and pulling up 20K 
  records makes a 
huge performance hit.
For the form in question I am using PASSTHROUGH type query
(the one that just
passes everything straight to server without ODBC).
NOTE: souce_for_inquiries_form is the join table and is
searchable in the from
(it feeds a pull-down list).
   
SELECT inquiries.inquiry_id, inquiries.contact_id, 
inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, 
inquiries.agent_id, inquiries.inquiry_date,
inquiries.event_type, inquiries.letter_type, inquiries.event_date,
inquiries.event_date_general, inquiries.event_location,
inquiries.guests,
inquiries.hours, inquiries.budget, inquiries.event_description,
inquiries.talent_description, inquiries.past_use,
inquiries.referred_by,
inquiries.date_sent, inquiries.end_user, inquiries.event_id,
inquiries.notes,
source_for_inquiries_form.organization,
source_for_inquiries_form.mailing_address_1,
source_for_inquiries_form.mailing_address_2,
source_for_inquiries_form.city,
source_for_inquiries_form.state, source_for_inquiries_form.zip,
source_for_inquiries_form.contact_type,
individual_contacts.contact_name_first,
individual_contacts.contact_name_last,
individual_contacts.contact_prefix,
individual_contacts.contact_title, individual_contacts.email
FROM inquiries
LEFT JOIN individual_contacts ON inquiries.indiv_contact_id =
individual_contacts.indiv_contact_id
INNER JOIN contacts AS source_for_inquiries_form ON
inquiries.contact_id =
source_for_inquiries_form.contact_id
ORDER BY  inquiries.inquiry_id DESC;
   
-
  
  
  
  -
  Visit CARMEL MUSIC  ENTERTAINMENT website http://carmelme.com
  
  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
  http://lists.mysql.com/mysql? [EMAIL PROTECTED]
  
  
 
 


Apolinaras Apollo Sinkevicius
Carmel Music  Entertainment, LLC 
701 Main Street 
Evanston, IL 60202 
Phone: (847) 864-5969 X110
  Fax: (847) 864-6149 
Toll-free: 800-276-5969 X110
   e-mail: [EMAIL PROTECTED] 
 web-site:  http://carmelme.com 

Having an event in Chicago, or would you like to bring Chicago entertainment 
to your event? Give Carmel Music  Entertainment a call for the finest 
entertainment available in Chicago.

-
Visit CARMEL MUSIC  ENTERTAINMENT website http://carmelme.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL

Re: Need help optimizing query, awfully slow on only 20000 records

2003-08-20 Thread Apollo (Carmel Entertainment) 
Query takes 3.4 seconds to run on the server, but it takes 1-2minutes to run via
MyODBC 3.51.06 using passthrough (Access97 is the front end, but it has query
type that allows bypass of Access interpretation.

 Two Questions:
 
 Is the same query running directly on the linux server thru mysql is also
 very slow ?
 
 Have you done a explain plan on the query ?
 
 Marc.
 
 


-
Visit CARMEL MUSIC  ENTERTAINMENT website http://carmelme.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Need help optimizing query, awfully slow on only 20000 records

2003-08-20 Thread Apollo (Carmel Entertainment) 
When I send the query throuh comman line, it works perfect 3-4 seconds, but when
I do Access97 pass-through query, that is when it runs into 1-2 minutes.
It is almost as slow as using Access97 native query that goes through MyODBC, so...

 That doesn't look too bad.  Is the query that slow when you use the
 command-line client alone (connected directly to the server), or is it
 just when accessing the database through the ODBC tunnel?
 
 --Michael
 
  -Original Message-
  From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] 
  Sent: Wednesday, August 20, 2003 3:14 PM
  To: Michael S. Fischer
  Cc: [EMAIL PROTECTED]
  Subject: RE: Need help optimizing query, awfully slow on only 
  2 records
  
  
  
  Result of EXPLAIN is:
  table|type|possible_keys|key|key_len|ref|rows|Extra
  inquiries|ALL|contact_id| | | |8253|Using filesort 
  individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3|
  inquiries.indiv_contact_id|1
  
  source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3|
  inquiries.contact_id|1
   
  
  
   What does EXPLAIN SELECT query show?  Have you read the 
  chapter in the
   manual on optimizing queries?  Do you have all the proper 
  indices set
   up?
   
   --Michael
   
-Original Message-
From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 20, 2003 2:52 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Need help optimizing query, awfully slow on only 
2 records


When following query is pulled up, it takes about whole 1-2 
minutes to come up. inquiries table has 17000 records, 
contacts has about 7000, individual_contacts has about 
16000. It has gotten worse once I upgraded to 4.0 and latest 
MyODBC. Clients are separate machines (mix of Win98 and 
WinXP). Those 20K records is what feeds the Access97 form, 
pull down list filters out some and pulls up about 3K and 
people just start typing a name and then (since there 
multiple inquiries for some clients) pull down the list to 
pick inquiry they want.

 What are you defining as a huge performance hit?
 
 Is the result set 20K records, or the base tables? How 
  big are the 
 base tables?
 
 Are the client and server on the same machine? Pulling 
  20K records 
 across the network could take some time. Formatting 20K 
records into a 
 pull -down list in access will also take a long time. 
Anyway who reads 
 a 20K list?
 
 Which parts of the process are slow? How does the query 
perform from 
 the mysql command line?
 
  -Original Message-
  From: Apollo (Carmel Entertainment) 
  [mailto:[EMAIL PROTECTED]
  Sent: 19 August 2003 17:29
  To: [EMAIL PROTECTED]
  Subject: Need help optimizing query, awfully slow on 
  only 2 
  records
 
 
  1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0
 
  I have about 20K records that result from the following 
query. Front 
  end for the database is ACCESS97 and pulling up 20K 
records makes a 
  huge performance hit.
  For the form in question I am using PASSTHROUGH type query
  (the one that just
  passes everything straight to server without ODBC).
  NOTE: souce_for_inquiries_form is the join table and is
  searchable in the from
  (it feeds a pull-down list).
 
  SELECT inquiries.inquiry_id, inquiries.contact_id, 
  inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, 
  inquiries.agent_id, inquiries.inquiry_date,
  inquiries.event_type, inquiries.letter_type, 
  inquiries.event_date,
  inquiries.event_date_general, inquiries.event_location,
  inquiries.guests,
  inquiries.hours, inquiries.budget, 
  inquiries.event_description,
  inquiries.talent_description, inquiries.past_use,
  inquiries.referred_by,
  inquiries.date_sent, inquiries.end_user, inquiries.event_id,
  inquiries.notes,
  source_for_inquiries_form.organization,
  source_for_inquiries_form.mailing_address_1,
  source_for_inquiries_form.mailing_address_2,
  source_for_inquiries_form.city,
  source_for_inquiries_form.state, 
  source_for_inquiries_form.zip,
  source_for_inquiries_form.contact_type,
  individual_contacts.contact_name_first,
  individual_contacts.contact_name_last,
  individual_contacts.contact_prefix,
  individual_contacts.contact_title, individual_contacts.email
  FROM inquiries
  LEFT JOIN individual_contacts ON inquiries.indiv_contact_id =
  individual_contacts.indiv_contact_id
  INNER JOIN contacts AS source_for_inquiries_form ON
  inquiries.contact_id =
  source_for_inquiries_form.contact_id
  ORDER BY  inquiries.inquiry_id DESC;
 
  -


-
Visit

RE: Need help optimizing query, awfully slow on only 20000 records

2003-08-20 Thread Michael S. Fischer 
That doesn't look too bad.  Is the query that slow when you use the
command-line client alone (connected directly to the server), or is it
just when accessing the database through the ODBC tunnel?

--Michael

 -Original Message-
 From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, August 20, 2003 3:14 PM
 To: Michael S. Fischer
 Cc: [EMAIL PROTECTED]
 Subject: RE: Need help optimizing query, awfully slow on only 
 2 records
 
 
 
 Result of EXPLAIN is:
 table|type|possible_keys|key|key_len|ref|rows|Extra  
 inquiries|ALL|contact_id| | | |8253|Using filesort   
 individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3|
 inquiries.indiv_contact_id|1
 
 source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3|
 inquiries.contact_id|1

 
 
  What does EXPLAIN SELECT query show?  Have you read the 
 chapter in the
  manual on optimizing queries?  Do you have all the proper 
 indices set
  up?
  
  --Michael
  
   -Original Message-
   From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] 
   Sent: Wednesday, August 20, 2003 2:52 PM
   To: [EMAIL PROTECTED]
   Cc: [EMAIL PROTECTED]
   Subject: RE: Need help optimizing query, awfully slow on only 
   2 records
   
   
   When following query is pulled up, it takes about whole 1-2 
   minutes to come up. inquiries table has 17000 records, 
   contacts has about 7000, individual_contacts has about 
   16000. It has gotten worse once I upgraded to 4.0 and latest 
   MyODBC. Clients are separate machines (mix of Win98 and 
   WinXP). Those 20K records is what feeds the Access97 form, 
   pull down list filters out some and pulls up about 3K and 
   people just start typing a name and then (since there 
   multiple inquiries for some clients) pull down the list to 
   pick inquiry they want.
   
What are you defining as a huge performance hit?

Is the result set 20K records, or the base tables? How 
 big are the 
base tables?

Are the client and server on the same machine? Pulling 
 20K records 
across the network could take some time. Formatting 20K 
   records into a 
pull -down list in access will also take a long time. 
   Anyway who reads 
a 20K list?

Which parts of the process are slow? How does the query 
   perform from 
the mysql command line?

 -Original Message-
 From: Apollo (Carmel Entertainment) 
 [mailto:[EMAIL PROTECTED]
 Sent: 19 August 2003 17:29
 To: [EMAIL PROTECTED]
 Subject: Need help optimizing query, awfully slow on 
 only 2 
 records


 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0

 I have about 20K records that result from the following 
   query. Front 
 end for the database is ACCESS97 and pulling up 20K 
   records makes a 
 huge performance hit.
 For the form in question I am using PASSTHROUGH type query
 (the one that just
 passes everything straight to server without ODBC).
 NOTE: souce_for_inquiries_form is the join table and is
 searchable in the from
 (it feeds a pull-down list).

 SELECT inquiries.inquiry_id, inquiries.contact_id, 
 inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, 
 inquiries.agent_id, inquiries.inquiry_date,
 inquiries.event_type, inquiries.letter_type, 
 inquiries.event_date,
 inquiries.event_date_general, inquiries.event_location,
 inquiries.guests,
 inquiries.hours, inquiries.budget, 
 inquiries.event_description,
 inquiries.talent_description, inquiries.past_use,
 inquiries.referred_by,
 inquiries.date_sent, inquiries.end_user, inquiries.event_id,
 inquiries.notes,
 source_for_inquiries_form.organization,
 source_for_inquiries_form.mailing_address_1,
 source_for_inquiries_form.mailing_address_2,
 source_for_inquiries_form.city,
 source_for_inquiries_form.state, 
 source_for_inquiries_form.zip,
 source_for_inquiries_form.contact_type,
 individual_contacts.contact_name_first,
 individual_contacts.contact_name_last,
 individual_contacts.contact_prefix,
 individual_contacts.contact_title, individual_contacts.email
 FROM inquiries
 LEFT JOIN individual_contacts ON inquiries.indiv_contact_id =
 individual_contacts.indiv_contact_id
 INNER JOIN contacts AS source_for_inquiries_form ON
 inquiries.contact_id =
 source_for_inquiries_form.contact_id
 ORDER BY  inquiries.inquiry_id DESC;

 -
   
   
   
   -
   Visit CARMEL MUSIC  ENTERTAINMENT website http://carmelme.com
   
   -- 
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:
   http://lists.mysql.com/mysql? [EMAIL PROTECTED]
   
   
  
  
 
 
 Apolinaras Apollo Sinkevicius
 Carmel Music  Entertainment, LLC 
 701 Main Street 
 Evanston, IL 60202

RE: Need help optimizing query, awfully slow on only 20000 records

2003-08-20 Thread Michael S. Fischer 
What does EXPLAIN SELECT query show?  Have you read the chapter in the
manual on optimizing queries?  Do you have all the proper indices set
up?

--Michael

 -Original Message-
 From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, August 20, 2003 2:52 PM
 To: [EMAIL PROTECTED]
 Cc: [EMAIL PROTECTED]
 Subject: RE: Need help optimizing query, awfully slow on only 
 2 records
 
 
 When following query is pulled up, it takes about whole 1-2 
 minutes to come up. inquiries table has 17000 records, 
 contacts has about 7000, individual_contacts has about 
 16000. It has gotten worse once I upgraded to 4.0 and latest 
 MyODBC. Clients are separate machines (mix of Win98 and 
 WinXP). Those 20K records is what feeds the Access97 form, 
 pull down list filters out some and pulls up about 3K and 
 people just start typing a name and then (since there 
 multiple inquiries for some clients) pull down the list to 
 pick inquiry they want.
 
  What are you defining as a huge performance hit?
  
  Is the result set 20K records, or the base tables? How big are the 
  base tables?
  
  Are the client and server on the same machine? Pulling 20K records 
  across the network could take some time. Formatting 20K 
 records into a 
  pull -down list in access will also take a long time. 
 Anyway who reads 
  a 20K list?
  
  Which parts of the process are slow? How does the query 
 perform from 
  the mysql command line?
  
   -Original Message-
   From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED]
   Sent: 19 August 2003 17:29
   To: [EMAIL PROTECTED]
   Subject: Need help optimizing query, awfully slow on only 2 
   records
  
  
   1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0
  
   I have about 20K records that result from the following 
 query. Front 
   end for the database is ACCESS97 and pulling up 20K 
 records makes a 
   huge performance hit.
   For the form in question I am using PASSTHROUGH type query
   (the one that just
   passes everything straight to server without ODBC).
   NOTE: souce_for_inquiries_form is the join table and is
   searchable in the from
   (it feeds a pull-down list).
  
   SELECT inquiries.inquiry_id, inquiries.contact_id, 
   inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, 
   inquiries.agent_id, inquiries.inquiry_date,
   inquiries.event_type, inquiries.letter_type, inquiries.event_date,
   inquiries.event_date_general, inquiries.event_location,
   inquiries.guests,
   inquiries.hours, inquiries.budget, inquiries.event_description,
   inquiries.talent_description, inquiries.past_use,
   inquiries.referred_by,
   inquiries.date_sent, inquiries.end_user, inquiries.event_id,
   inquiries.notes,
   source_for_inquiries_form.organization,
   source_for_inquiries_form.mailing_address_1,
   source_for_inquiries_form.mailing_address_2,
   source_for_inquiries_form.city,
   source_for_inquiries_form.state, source_for_inquiries_form.zip,
   source_for_inquiries_form.contact_type,
   individual_contacts.contact_name_first,
   individual_contacts.contact_name_last,
   individual_contacts.contact_prefix,
   individual_contacts.contact_title, individual_contacts.email
   FROM inquiries
   LEFT JOIN individual_contacts ON inquiries.indiv_contact_id =
   individual_contacts.indiv_contact_id
   INNER JOIN contacts AS source_for_inquiries_form ON
   inquiries.contact_id =
   source_for_inquiries_form.contact_id
   ORDER BY  inquiries.inquiry_id DESC;
  
   -
 
 
 
 -
 Visit CARMEL MUSIC  ENTERTAINMENT website http://carmelme.com
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql? [EMAIL PROTECTED]
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Need help optimizing query, awfully slow on only 20000 records

2003-08-19 Thread Apollo (Carmel Entertainment) 
1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0

I have about 20K records that result from the following query. Front end for the
database is ACCESS97 and pulling up 20K records makes a huge performance hit.
For the form in question I am using PASSTHROUGH type query (the one that just
passes everything straight to server without ODBC).
NOTE: souce_for_inquiries_form is the join table and is searchable in the from
(it feeds a pull-down list).

SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id,
inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date,
inquiries.event_type, inquiries.letter_type, inquiries.event_date,
inquiries.event_date_general, inquiries.event_location, inquiries.guests,
inquiries.hours, inquiries.budget, inquiries.event_description,
inquiries.talent_description, inquiries.past_use, inquiries.referred_by,
inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes,
source_for_inquiries_form.organization,
source_for_inquiries_form.mailing_address_1,
source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city,
source_for_inquiries_form.state, source_for_inquiries_form.zip,
source_for_inquiries_form.contact_type, individual_contacts.contact_name_first,
individual_contacts.contact_name_last, individual_contacts.contact_prefix,
individual_contacts.contact_title, individual_contacts.email
FROM inquiries 
LEFT JOIN individual_contacts ON inquiries.indiv_contact_id =
individual_contacts.indiv_contact_id 
INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id =
source_for_inquiries_form.contact_id
ORDER BY  inquiries.inquiry_id DESC;

-
Visit CARMEL MUSIC  ENTERTAINMENT website http://carmelme.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Need help optimizing query

2002-10-05 Thread johnt 


Greetings, I am struggling trying to get good performance 
using indexes.

I'll spare the details and narrow down the situation. I have about 
100,000 rows of data with the following fields:

title - vachar(64)
description - text 
state - varchar(2)
priority - int
modified - date

People type in a search string, and I need the results ordered in
the following way:

1) by priority DESC
2) items matching the title
3) items matching the description

I've experimented with full-text search, but cannot get it to do
this effeciently all in one query because of the way I need 
to order things. I tried adding  two full-text indexes like:

SELECT title, MATCH(title) AGAINST ('query') AS intitle FROM mytable WHERE 
MATCH(title, description) AGAINST ('query') [AND state = 'state'] ORDER by priority 
desc, intitle desc, modified desc;

but this only uses the (title,description) index, an not the (title)
index or the state index. 

It would be much faster if I could do only a full text search on
records with the state that is selected. (Any ideas on how to do this?)

Currently I do the following which which is really weak when there is
no state selected:

SELECT title, (title LIKE '%query%') AS intitle FROM mytable WHERE ((description LIKE 
'%query%') OR (title LIKE '%query%')) [AND state='state'] ORDER BY priority desc, 
intitle desc, modified desc;

Does anyone have any better ideas? I would consider doing multiple queries
using the different indexes but then I don't know how many rows matched 
without taking out the duplicates which would be expensive, I think.

Thanks in advance,

John

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Help Optimizing Query...

2002-06-12 Thread Matthew Walker 

I need help optimizing the query below. I've included the query, the
table declerations, and the output of explain. Any help would be greatly
appreciated.

What I'd really like is to find out how to get it to use a key on the
lineitems table. I've tried adding indexes on the OrderID and MerchantID
columns, but it didn't use them.

SELECT Sites.TemplateName, SUM(LineItems.Quantity * LineItems.ItemPrice)
as Total FROM Sites, LineItems, Orders WHERE Sites.SiteID =
Orders.SiteID AND Orders.OrderStatusID=100 AND
Orders.OrderStatusID=120 AND LineItems.OrderID=Orders.OrderID AND
LineItems.MerchantID = Orders.MerchantID AND Year(Orders.OrderDate) =
Year(Now()) GROUP BY Sites.TemplateName


CREATE TABLE Sites (
  SiteID int(10) unsigned NOT NULL auto_increment,
  MerchantID int(10) unsigned NOT NULL default '0',
  TemplateName varchar(255) NOT NULL default '',
CLIP
  PRIMARY KEY  (SiteID),
  UNIQUE KEY TemplateName (TemplateName)
) TYPE=MyISAM;

CREATE TABLE Orders (
  OrderID int(10) unsigned NOT NULL default '0',
  MerchantID int(10) unsigned NOT NULL default '0',
  SiteID int(10) unsigned NOT NULL default '0',
  BatchID int(10) unsigned NOT NULL default '0',
  CustomerID int(10) unsigned NOT NULL default '0',
  OrderDate datetime NOT NULL default '-00-00 00:00:00',
  OrderStatusID int(10) NOT NULL default '0',
CLIP
  PRIMARY KEY  (OrderID,MerchantID),
  KEY CustomerID (CustomerID),
  KEY MerchantID (MerchantID),
  KEY SiteID (SiteID)
) TYPE=MyISAM;

CREATE TABLE LineItems (
  LineItemID int(10) unsigned NOT NULL auto_increment,
  OrderID int(10) unsigned NOT NULL default '0',
  MerchantID int(10) unsigned NOT NULL default '0',
  StoreItemID int(10) unsigned NOT NULL default '0',
  LineItemTypeID int(10) unsigned NOT NULL default '0',
  ItemPrice decimal(10,2) NOT NULL default '0.00',
  Quantity int(10) unsigned NOT NULL default '0',
  Description text NOT NULL,
  LastModified timestamp(14) NOT NULL,
  PRIMARY KEY  (LineItemID)
) TYPE=MyISAM;


| table | type   | possible_keys | key | key_len |
ref| rows  | Extra   |

| LineItems | ALL| NULL  | NULL|NULL |
NULL   | 33675 | Using temporary |

| Orders| eq_ref | PRIMARY,MerchantID,SiteID | PRIMARY |   8 |
LineItems.OrderID,LineItems.MerchantID | 1 | where used  |

| Sites | eq_ref | PRIMARY   | PRIMARY |   4 |
Orders.SiteID  | 1 | |



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php