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
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
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
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,
:[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 when you
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 when you are comparing
:[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:
+--+-+--+-+-+---+
| Field| Type
:
06/25/2004 01:52 Fax to:
PM Subject: RE: Why won't mysql use the
index? WAS: strange table speed
issue
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 queried, and the best
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) | |
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
:[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
have multi
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;
+++--+--+-+---+-
+--++-+
|
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|
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
: 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
have multi-field index)...
MUL doesn't mean part
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't mean part of a
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
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 indexes
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 you give an
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 of
] [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
i do a fulltext search on this table
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
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 find
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
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 have some_table
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
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)
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 minutes to
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
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 Ward Limited
.
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
Subject: Speed issue
Hi
I am
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 you
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 field to NOT NULL, and I didn't get any
better performance.
Is there a known speed issue in searching on date
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 seconds when there
35 matches
Mail list logo
