NOTE: I sent this message yesterday, but for some
reason it didn't show up in the list. If you've
already received it, I apologize for the
inconvenience.
So here it goes again:
I searched the online manual and all I could find was
a reference to a statement of the form:
SELECT INTO
Is there a way to use dirty reads (that acquire no
read locks on the table) with MyISAM tables?
I want to avoid having the read requests queuing up
while the table is updated, and I can tolerate a small
margin of inconsistency for the sake of throughput.
So far I found only information about
Download the .NET connector for MySQL:
http://dev.mysql.com/downloads/connector/net/1.0.html
Reference it in your project and you're ready to go.
It comes with plenty of code examples on how to use
it.
--- Winanjaya [EMAIL PROTECTED] wrote:
Dear All,
Any body has experience to connect
I've been contemplating writing my own custom storage
engine for MySQL.
I haven't found much documentation on the subject
besides these articles:
http://netmirror.org/mirror/mysql.com/tech-resources/articles/creating-new-storage-engine.html
If I use the LIMIT clause without using
SQL_CALC_FOUND_ROWS in a non-UNION statement, MySQL
returns the number of rows found up to the LIMIT.
Using SQL_CALC_FOUND_ROWS forces MySQL to keep going
on checking all hits, and that affects performance.
However, if I use the LIMIT clause at the end of a
MySQL seems to let me use the LIMIT clause in both
parts of a UNION ALL query, but as soon as I add an
ORDER BY CLAUSE, it gives me a syntax error.
For example, this query executes fine:
SELECT * FROM A WHERE X = 1 LIMIT 1000
UNION ALL
SELECT * FROM B WHERE Y = 1 LIMIT 1000
But this returns an
Nevermind, I found in the documentation that I need to
paranthesize the SELECT parts of the UNION ALL, like
this:
(SELECT X, Y FROM A WHERE W = 1 LIMIT 1000)
UNION ALL
(SELECT X, Y FROM B WHERE W = 1 LIMIT 1000)
ORDER BY X
--- Homam S.A. [EMAIL PROTECTED] wrote:
MySQL seems to let me use
I have a query of the form:
(SELECT A, B from X ORDER BY A LIMIT 1000)
UNION ALL
(SELECT A, B from Y ORDER BY A LIMIT 1000)
ORDER BY A
I thought may be each query needs to start with
SELECT, so I wrapped the above query in a
derived-table expression like:
SELECT * FROM (
(SELECT A, B from X
It seems that MySQL freaks out whenever it seems
something that looks like a derive table and refuses
it to cache. Even a non-UNION query like:
SELECT * FROM
(SELECT * FROM X WHERE A = 5) AS DerivedTable
Won't be cached.
I read a comment in the documentation that if you put
SQL_CACHE in the
There's nothing wrong with polling as long as it's
moderate and it doesn't lock resources.
On a heavily updated table in a large volumen site, it
scales much better than nasty triggers or events
firing all over the place.
Triggers are intended to maintain internal database
consistency by
--- mos [EMAIL PROTECTED] wrote:
Correct, but the speed difference will more than
make up for it. If you
have a busy website, it is very important not to
overtax the database
server with frills, like the number of rows found.
If you want to have the
record counts, then you will have to
Most likely it's the OS cache caching all those disk
segments in memory. Also in InnoDB, MySQL uses the
Buffer Pool Size to cache data pages in addition to
the OS cache.
If you're running ona Windows machine, you can easily
tell what's going on by opening up Performance Monitor
and watching
as
T-SQL with END and it worked fine.
Homam
--- Tom Crimmins [EMAIL PROTECTED] wrote:
On Wednesday, March 09, 2005 07:49, Philippe
Poelvoorde wrote:
Daniel Kasak wrote:
Homam S.A. wrote:
In the documentation, it doesn't mention which
version
of MySQL supports the CASE statement
I just spent quite a while hunting out a bug that
turned out to be a mis-spelled session variable
defaulting to NULL.
Ultimately what I want is, if I used a session
veriable without defining it before hand with a SET,
MySQL would return an error.
Such problem doesn't exist in T-SQL, for example,
I'm sorry, I meant user variables, not session
variables.
--- Homam S.A. [EMAIL PROTECTED] wrote:
I just spent quite a while hunting out a bug that
turned out to be a mis-spelled session variable
defaulting to NULL.
Ultimately what I want is, if I used a session
veriable without defining
If your tables are mostly read-only, you could
pre-generate page numbers on a periodic basis and
select only specific ranges WHERE row_number BETWEEN
page_start AND page_finish.
Or you could just send the top 1000 IDs of the table
to the client, and have the client figure out which
IDs belong to
Unfortunately this doesn't work well if you want to
tell your users how many pages were found in the
query. Sure, you could use SQL_CALC_FOUND_ROWS with
FOUND_ROWS(), but this will defeate the purpose of the
LIMIT clause -- speed -- because MySQL will have to
figure out all the results of the
In the documentation, it doesn't mention which version
of MySQL supports the CASE statement, but it refers to
stored procedures, so is it only supported for 5.x?
I can't get any example of a CASE statement work in
MySQL.
The reason I'm asking is because in other SQL
dialects, such as T-SQL, you
Whenever I try to update an ISAM table (through a join
with nother small lookup table, also ISAM), I get the
following error:
Error Code : 1114
The table '#sql_910_0' is full
I read the following in the documentation:
http://dev.mysql.com/doc/mysql/en/full-table.html
But none of the reasons
MySQL keeps throwing error 1114 on temp files (those
that MySQL creates behind the scenes when executing a
query and start with #), which is pretty dumb because
there's no way for the user to set the maximum row
size on temp table files. It seems that MySQL is
wrongly setting the temp size and
MySQL decide to use no
index in some situations. Mike
- Original Message -
From: Homam S.A. [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: February 22, 2005 8:12 PM
Subject: Avoiding filesort #2
Actually with the query below it does avoid
filesort
--- mos [EMAIL PROTECTED] wrote:
At 05:12 AM 2/23/2005, Homam S.A. wrote:
Thanks Mike for your feedback.
Unfortunately the HANDLER statement has the same
limitation, i.e. it doesn't allow you to specify
range
criteria for index key parts. You have to specify
constants in the index_name
I read How My SQL Optimizes Order By
(http://dev.mysql.com/doc/mysql/en/order-by-optimization.html),
and I'm aware of its severe limitation due to the
one-index-per-table rule.
However, even when I follow all the roles, I'm still
getting filesort instead of using the index order.
So I created an
Actually with the query below it does avoid filesort,
but once I use anything other than the equal operator
(e.g. ColC 5), it reverts back to filesort.
Any thoughts?
--- Homam S.A. [EMAIL PROTECTED] wrote:
I read How My SQL Optimizes Order By
(http://dev.mysql.com/doc/mysql/en/order
it in your WHERE portion of
the statement. This
could confuse the index selection process and have
MySQL decide to use no
index in some situations. Mike
- Original Message -
From: Homam S.A. [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: February 22, 2005 8:12 PM
Subject
I think you meant select session from users where
user_id = 'X'. Anyway, it doesn't matter how big your
table is as long as you build an index on (user_id,
session). This way MySQL doesn't have to touch the
table for this query.
--- Scott Haneda [EMAIL PROTECTED] wrote:
I build a few login
Why MySQL insists on ignoring the query cache whenever
I use the same query repeatedly to populate a temp
table?
So I have:
create temporary table MyTable
select SQL_CACHE * from SomeTable WHERE (A bunch of
criteria) limit 1000;
SomeTable is a read-only table.
If I issue the query without the
select test, max(audit_date)
from your_table
where device = 1
group by test
order by 1
--- Daevid Vincent [EMAIL PROTECTED] wrote:
I need to get the aggregate data from various tables
for a report.
The idea is that we audit devices daily on a
schedule, and also allow users
to audit the
update your_table
set your_field = trim(your_field)
--- John Berman [EMAIL PROTECTED] wrote:
Hi
I have a table with a number of fields
The table is already populated, however some entries
have got spaces both
before and after the data.
Future imports into the table will have the
think trim() was
supported long time ago.
--- John Berman [EMAIL PROTECTED] wrote:
Thanks for this
I did this:
update mc_census set surname = trim(surname)
however it fails with a syntax error ?
I'm on 4.1
Regards
John B
-Original Message-
From: Homam S.A. [mailto
Since MySQL stores RowIDs with the indexed words
instead of the table's primary key IDs, and since it
uses only one index per table in any query, performing
a full-text search on a large table (several million
rows) and joining it with another large table proves
to be extremely slow!
The
I'm evaluating MySQL FT search, and so far it's been
very disappointing. The queries on a test table of
about 2 million rows with Text columns (average 75
words per text column) are extremely slow, compared to
a regular FT search engine, like Lucene.
What's disturbing is that it doesn't consume
Is there a rowcount global variable or function in
MySQL equivalent to @@rowcount in MS SQL Server that
returns the number of affected rows from the last SQL
statement on the current connection?
Thanks!
__
Do you Yahoo!?
All your favorites on
I have a non-primary-key index on a large MyISAM
table, and dropping the index takes a long time, in
addition to maxing out the CPU utilization in its
final 1/3 interval.
Why is that?
In MS SQL Server for example, dropping the index is
almost instantaeous, unless it's clustered and you
have
.
Administracion de sistema de respaldo : Mauricio
Guajardo.
Saludos,
Alvaro Avello
Administrador de Red.
Servinco S.A.
- Original message follows -
--- Keith Ivey [EMAIL PROTECTED] wrote:
Homam S.A. wrote:
I have a non-primary-key index on a large MyISAM
table, and dropping
Dropping an index on a MyISAM table should be
instantaneous. It should only take as long as deleting
the idx file from the file system. But it's taking
almost as long as creating the index itself!
Here's my queries and time they took:
/*[10:58:17 AM][367172 ms]*/ alter table MyTable add
index
I know about my.ini, but I'm not sure how to specify
these mysqld start-up options to it:
--shared-memory
--shared-memory-base-name=MYSQL
I tried adding entries like:
shared-memory=true
shared-memory-base-name=MYSQL
But it didn't work.
So my question is, How do I specify the above mysqld
What's the default fill factor on MyISAM B-Tree
indexes? I know it's 15/16 by default for InnoDB, but
nothing is mentioned about MyISAM.
For example, if I'm going to create read-only tables
and index them, I'd like the fill factor to be 100%
since there won't be updates and therefore I'm not
You could also leave the password blank if you don't
want to force MySQL to revert to the old password
encryption on start-up. This should be fine if your
server is in the DMZ and you control all the processes
that have access to the server.
Unfortunately, 4.2.x breaks backward compatibility
with
Tom, this is awesome! It must have been fresh out of
dev because I downloaded 3.51.09 a couple of days ago.
I just downloaded it and it works fine with 4.1
authentication.
Thanks!
--- Tom Crimmins [EMAIL PROTECTED] wrote:
Try MyODBC 3.51.10. It supports 4.1 auth. Here is a
link to a mirror
I have a simple table in SQL Server that doesn't have
any BLOB field (no text or image columns). Only char,
varchar, bit, and int columns. I created a DTS package
that uses a simple select to copy the contents of this
table into an identical table created in MySQL through
MyODBC 3.51.
Whenver I
OK, I figured out what was going on. When I created a
varchar(256) in the MySQL destination table, MySQL
silently converted to it text (instead of protesting
that varchar maxes out at 255), and I just found the
problem by executing show create table MyTable.
--- Homam S.A. [EMAIL PROTECTED
Is it possible to copy the database files (both MyISAM
and InnoDB) that contain tables, indices, and
full-text indices from one MySQL server to another
running/active MySQL server and start using them right
away?
I appreciate your help!
Homam
Make sure the subquery returns a scalar value. Either
the roleDBNum column has to be unique or you need to
apply an aggregate function to reduce to a single
value. If the the subquery returns many values, use
the IN operator instead of =.
Disclaimier: I'm still new to MySQL, but I'm assuming
it
Thanks Mike for the information. Yes, Emmett mentioned
the same thing in a private message, and it seems that
MyISAM is exactly what I'm looking for: a
heavily-indexed large table that will be also indexed
for full-text search and built off-line -- no updates
whatsoever.
However, I will be
Thanks Mike. I think testing ultimately determines how
efficient heterogeneous engine joins are. I just
wanted to know if someone had issues with them in a
heavy-load environment.
--- mos [EMAIL PROTECTED] wrote:
At 04:00 PM 12/21/2004, Homam S.A. wrote:
Thanks Mike for the information. Yes
I was wondering what's the shortcut for executing only
selected text in the query window (SQL Query Area) in
MySQL Query Browser.
Using Ctr-E or Ctrl-Enter executes everything in the
window, not just the selected text, as MS Query
Analyzer does.
It's a great hassle to have to comment all the
I'm new to MySQL and I was wondering which storage
engine is the best choice for heavily-indexed,
read-mostly data.
From skimming over the documentation, it seems that
MyISAM is a better choice since it doesn't have the
transactional overhead. Yet I'm worried that it's
becoming depricated and
48 matches
Mail list logo
