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 OUTFILE...
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 sche
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 connec
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
http://dev.mysql.com/tech-resources/articles/storage-en
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 SEL
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 ORDE
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
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 e
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
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 enforcin
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 Pages/
--- 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 h
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 query
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 w
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 wan
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,
. So I used the same syntax 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, 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 d
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 then
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 lis
--- 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
tty sure that what the manual
> > > says is that MySQL only
> > > USES one index per request, not one index per
> table.
> > > I would try adding an
> > > index that starts with ColC (and maybe only
> ColC).
> > > Your index starts with
> &
ith ColC (and maybe only ColC).
> Your index starts with
> ColA but you do not use 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
>
>
> - Origi
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"
>
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 a
gt; Regards
>
> John B
>
> -Original Message-
> From: Homam S.A. [mailto:[EMAIL PROTECTED]
> Sent: 11 February 2005 21:29
> To: mysql@lists.mysql.com
> Subject: Re: Remove spaces
>
> update your_table
> set your_field = trim(your_field)
>
>
>
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 ha
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 audi
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 t
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 logi
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 bottlenec
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 an
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 on
Diaz.
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
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 other
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 (My
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
start
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 mirr
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
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
worri
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".
--- "Homa
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 ex
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
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
__
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 othe
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
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 joining
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 won
48 matches
Mail list logo
