RE: speed issue - inserts slowing down selects

PROTECTED] Sent: Wednesday, October 13, 2004 2:02 PM To: [EMAIL PROTECTED] Subject: speed issue - inserts slowing down selects We have what is called a data grabber for quotes that is inserting rows into both InnoDb and MyIsam tables. When the data is being imported ( inserted ), it is dramatically

Re: speed issue - inserts slowing down selects

One thing you could do, which may not be the best, is insert one (or some set limit) of rows at a time, then after each sleep for .25 seconds or something, so that your inserts get spread out more over time, and there is idle time between them for the selecting clients to complete. Obviously this m

speed issue - inserts slowing down selects

We have what is called a data grabber for quotes that is inserting rows into both InnoDb and MyIsam tables. When the data is being imported ( inserted ), it is dramatically slowing clients down that are running queries against the table. Our first attempt at a solution was to insert one row at

RE: Why won't mysql use the index? WAS: strange table speed issue

004 11:16 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Why won't mysql use the index? WAS: strange table speed issue Here is what the manual says about the table scan threshold: (http://dev.mysql.com/doc/mysql/en/Where_optimisations.html) Each table index is q

RE: Why won't mysql use the index? WAS: strange table speed issue

y, June 25, 2004 10:56 AM To: [EMAIL PROTECTED] Subject: Why won't mysql use the index? WAS: strange table speed issue This is crazy. If someone can help me out, I'll pay them! A table: +--+-+-

RE: Why won't mysql use the index? WAS: strange table speed issue

al Message- From: MerchantSense [mailto:[EMAIL PROTECTED] Sent: Friday, June 25, 2004 10:56 AM To: [EMAIL PROTECTED] Subject: Why won't mysql use the index? WAS: strange table speed issue This is crazy. If someone can help me out, I'll pay them! A table: +--+

Why won't mysql use the index? WAS: strange table speed issue

l Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Thursday, June 24, 2004 11:41 PM To: Marc Slemko Cc: MerchantSense; [EMAIL PROTECTED] Subject: Re: strange table speed issue I'm not certain, but I don't think a multi-column index will help here. The manual is uncle

RE: strange table speed issue

lto:[EMAIL PROTECTED] Sent: Thursday, June 24, 2004 11:41 PM To: Marc Slemko Cc: MerchantSense; [EMAIL PROTECTED] Subject: Re: strange table speed issue I'm not certain, but I don't think a multi-column index will help here. The manual is unclear on how a multi-column index is used

Re: strange table speed issue

I'm not certain, but I don't think a multi-column index will help here. The manual is unclear on how a multi-column index is used when you are comparing the first key part to a range rather than to a constant, but I get the impression it doesn't use the second key part in that case. For you, t

RE: strange table speed issue

Yep, that's exactly what I did -Original Message- From: Sergio Salvi [mailto:[EMAIL PROTECTED] Sent: Thursday, June 24, 2004 10:55 PM To: [EMAIL PROTECTED] Subject: RE: strange table speed issue On Thu, 24 Jun 2004, MerchantSense wrote: > Yes, but I now have multi-column

RE: strange table speed issue

: Thursday, June 24, 2004 10:37 PM > To: Sergio Salvi > Cc: MerchantSense; [EMAIL PROTECTED] > Subject: Re: strange table speed issue > > > Sergio Salvi wrote: > > Hi! > > > > Can you send the output of the following command? > > > > show index from

Re: strange table speed issue

On Fri, 25 Jun 2004, Michael Stassen wrote: > > Sergio Salvi wrote: > > Hi! > > > > Can you send the output of the following command? > > > > show index from ip2org; > > > > It seems you don't have an index on both fields (even though it says you > > have multi-field index)... > > MUL doesn'

RE: strange table speed issue

ergio Salvi Cc: MerchantSense; [EMAIL PROTECTED] Subject: Re: strange table speed issue Sergio Salvi wrote: > Hi! > > Can you send the output of the following command? > > show index from ip2org; > > It seems you don't have an index on both fields (even though it says you &

Re: strange table speed issue

Sergio Salvi wrote: Hi! Can you send the output of the following command? show index from ip2org; It seems you don't have an index on both fields (even though it says you have multi-field index)... MUL doesn't mean part of a multi-field index. From the manual

RE: strange table speed issue

I added the multicolumn, and still have the same problem : mysql> explain SELECT org from ip2org where ip_start<=1094799892 and ip_end>=1094799892; ++--+---+--+-+--+-+- ---+ | table | type | possible_keys | key | key_len | ref | rows|

RE: strange table speed issue

On Thu, 24 Jun 2004, MerchantSense wrote: > Seems ok to me... > > It seems to be checking all the rows in the explain for some reason too... > > mysql> show index from ip2org; > +++--+--+-+---+- > +--++

RE: strange table speed issue

lto:[EMAIL PROTECTED] Sent: Thursday, June 24, 2004 8:00 PM To: MerchantSense Cc: [EMAIL PROTECTED] Subject: Re: strange table speed issue Hi! Can you send the output of the following command? show index from ip2org; It seems you don't have an index on both fields (even though it says you ha

Re: strange table speed issue

Hi! Can you send the output of the following command? show index from ip2org; It seems you don't have an index on both fields (even though it says you have multi-field index)... []s, Sergio Salvi. On Thu, 24 Jun 2004, MerchantSense wrote: > Hi - I need some help :) > > I set up a simple my

strange table speed issue

Hi - I need some help :) I set up a simple mysql table: mysql> describe ip2org; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | ip_start | bigint(20) | | MU

Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS

Hello, > > Could one not store the total while using the > index > > and use "select FOUND_ROWS()" without > > SQL_CALC_FOUND_ROWS to retrieve the total? > > Yes, it could. > It is the optimization that wasn't implemented yet. > (but it's in the TODO) Once again, thanks for the response. Could

Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS

Hi! On Dec 05, tk wrote: > Hello Sergei, Gunnar, and others, > > thank you for your quick responses. > One little mystery remains: > > Why does one need to read all the row data (with > SQL_CALC_FOUND_ROWS) to get the total number of > results when using a limit? > > When the index is used to

Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS

Hello Sergei, Gunnar, and others, thank you for your quick responses. One little mystery remains: Why does one need to read all the row data (with SQL_CALC_FOUND_ROWS) to get the total number of results when using a limit? When the index is used to find relevant rows and sort the results, the c

RE: fulltext search speed issue with SQL_CALC_FOUND_ROWS

TECTED] [mailto:[EMAIL PROTECTED] > Sent: Donnerstag, 4. Dezember 2003 16:13 > To: [EMAIL PROTECTED] > Subject: fulltext search speed issue with SQL_CALC_FOUND_ROWS > > > I have some_table with 100,000 rows and with an > average of 500 words in some_column of each row. When

Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS

Hi! On Dec 04, tk wrote: > Hello, > > Thanks for the response. > There is one thing that is not clear however. > > Regardless of whether or not I perform the fulltext > search with or without the SQL_CALC_FOUND_ROWS > keyword, the results that I get are exactly the same. > > Also, the notion

Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS

ns any faster. :-) > > > Hope that helps. > > > Matt > > > - Original Message - > From: <[EMAIL PROTECTED]> > Sent: Thursday, December 04, 2003 9:13 AM > Subject: fulltext search speed issue with > SQL_CALC_FOUND_ROWS > > > >

Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS

You might want to try your fulltext search IN BOOLEAN MODE to see if that runs any faster. :-) Hope that helps. Matt - Original Message - From: <[EMAIL PROTECTED]> Sent: Thursday, December 04, 2003 9:13 AM Subject: fulltext search speed issue with SQL_CALC_FOUND_ROWS > I h

fulltext search speed issue with SQL_CALC_FOUND_ROWS

I have some_table with 100,000 rows and with an average of 500 words in some_column of each row. When i do a fulltext search on this table using a query such as the following, all of my results are under 0.1 seconds: SELECT something FROM some_table WHERE MATCH (some_column) AGAINST ('some_search_

Re: Query speed issue

What are the configuration you are using? What's the size of your buffers? What's your system? Maybe increasing sort buffer and key buffer will be good. ;) Alexis Quoting Brad Teale <[EMAIL PROTECTED]>: > Hello, > > The problem: > I have the following query with is taking upwards of 2 minute

Query speed issue

Hello, The problem: I have the following query with is taking upwards of 2 minutes to complete and we need it faster, prefer less than 30 seconds (don't laugh): select modelhr, avg(f.temp-b.temp), avg(abs(f.temp-b.temp)), stddev(f.temp-b.temp), stddev(abs(f.temp-b.temp)), count(f.temp-b.temp) fro

RE: Speed issue

> So it may seem that on your machine, with both MySQL and Access loaded > together Access runs ever so much faster. However, you are comparing single > use Access to single use MySQL. (also, once mysql starts caching things, it > gets faster. The more you use it, the faster it runs!) Also, once y

RE: Speed issue

the important stuff. In short: MySQL != "Drop in replacement for desktop database" =C= * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: Simon Ashby [mailto:[EMAIL PROTECTED]] Sent: Monday, July 01, 2002 4:58 AM To: MYSQL S

RE: Speed issue

From: Simon Ashby [mailto:[EMAIL PROTECTED]] Sent: 01 July 2002 10:58 To: MYSQL Subject: Speed issue > > Any pointers on how to get rid of the disk churning and get a normal > response in this set up would be much appreciated. Turn off OBDC logging. Tim Ward Brett War

Speed issue

Hi I am trying to move an Access 2000 database into MYSQL 3.27.47nt running on a Win2K machine. MYODBC with the 3.51 drivers is also installed. MYSQL is running and readily responds to SQL queries using the DOS window at the mysql> prompt. However, when linking the MYSQL database with ADO and VB

RE: Indexing on a DATE field/bizarre speed issue with a LEFT JOIN?

Sorry for the long reply: At 02:57 PM 6/10/2002 -0500, you wrote: >[snip] >SELECT > head.po >FROM > head > LEFT JOIN line ON (head.sn=line.snHead) >WHERE > head.po > 1 > AND line.dateETA<='2002-06-10' >LIMIT 50 > >As is, this query is very fast (0.01 second

RE: Indexing on a DATE field/bizarre speed issue with a LEFT JOIN?

[snip] SELECT head.po FROM head LEFT JOIN line ON (head.sn=line.snHead) WHERE head.po > 1 AND line.dateETA<='2002-06-10' LIMIT 50 As is, this query is very fast (0.01 seconds when there are 25,000 records in 'head', and 50,000 records in 'line'). However,

Indexing on a DATE field/bizarre speed issue with a LEFT JOIN?

indexed. And, for both "<=" and ">=" queries, EXPLAIN returns the same information. The only thing I can think of is that NULL values are allowed in line.dateETA, and about half the values are NULL (22,000 lines). However, I tested changing the line.dateETA fie