On Thu, Dec 1, 2011 at 5:54 PM, Miguel Angel Nieto wrote:
> You should check pt-archiver.
+1. It works very well for this type of job.
- Perrin
On Mon, Oct 15, 2012 at 11:24 AM, Carlos Eduardo Caldi
wrote:
> Somebody knows how can I log or measure the index use ?
http://www.percona.com/doc/percona-toolkit/2.1/pt-index-usage.html
- Perrin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:h
On Thu, Oct 11, 2012 at 7:29 AM, Markus Falb wrote:
> Should I raise innodb_lock_wait_timeout? What are the disadvantages?
The disadvantage is that if the locks still don't clear by the time
the timeout is reached, you're just making the other process wait
longer before failing.
> Should I retry
On Wed, Aug 8, 2012 at 1:12 PM, Elim Qiu wrote:
> But when I use browser for the system testing (perl_test.cgi and forum.pl),
> both report me that DBD:mysql is not installed (or not available)
It's possible that DBD::mysql is installed in a place where it's not
in the library path for your CGI s
Hi,
This probably means you changed your MySQL libraries but didn't
recompile or reinstall DBD::mysql, Usually this kind of issue is
solved by reinstalling DBD::mysql in the same way that you previously
installed it, so it can build against your current libraries.
- Perrin
On Mon, Aug 6, 2012 a
On Thu, Feb 23, 2012 at 5:50 PM, Daevid Vincent wrote:
> Anyone have any thoughts on how I might optimize this query?
As always, it's all about the indexes. The index it chose on your
main table looks pretty weak. You probably should move those
non-joining columns out of your join condition in
On Fri, Mar 4, 2011 at 1:32 AM, Angela liu wrote:
> Can Isolation levels be set per transaction? I know isolation levels can be
> set per session or globally, but not sure at transaction level.
Given that you can only have one transaction at a time in a session,
there is no real difference betw
On Thu, Oct 14, 2010 at 3:28 AM, Johan De Meersman wrote:
> That depends on the type of lock. If no lock type is specified, InnDB will
> prefer row locks, while MyISAM will do table locks.
>
> That may help, unless all your queries are trying to access the same rows
> anyway :-)
Even that can wor
On Wed, Jun 2, 2010 at 10:28 AM, Lin Chun wrote:
> *don't know it very clear, but i think is the problem of seems it
> take full scaning*
Yes, it has no indexes so it isn't good for very large subqueries.
You should create them as temporary tables instead and give them
indexes.
- Perrin
--
My
On Fri, May 28, 2010 at 11:38 AM, Andre Matos wrote:
> I have a table that uses auto_increment to generate the Id automatically
> working fine.
> However, I need to create a new table where the Id must be a number generated
> randomly, so I cannot use the auto_increment.
You'd be better off usin
On Wed, Apr 21, 2010 at 11:14 AM, shamu...@gmail.com wrote:
> This is my current my.cnf setttings. Could anyone take a quick peek and tell
> me if I set anything awfully wrong?
If your tables are MyISAM (not InnoDB), then 128MB is much too small
for your key_buffer. You should look at the sample
On Wed, Apr 14, 2010 at 10:22 AM, Dan Nelson wrote:
> Switch to InnoDB :)
Seconded. No need to complicate your life with MyISAM workarounds
when InnoDB solves this problem already.
- Perrin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http:/
On Thu, Feb 25, 2010 at 2:48 AM, Dan Nelson wrote:
> IN is fine ( for example ... WHERE field1 IN (1,2,3,4,5,6) is extremely
> efficient);
Yes, I meant to say IN/NOT IN subqueries, not value lists.
> it's subqueries in general that are killers.
Subqueries in the FROM clause (aka derived tables)
On Wed, Feb 24, 2010 at 10:11 AM, Yang Zhang wrote:
> Any ideas on
> how to optimize this by convincing mysql to see the independence use a
> const join?
http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/
You need to rewrite as a join or use a FROM subquery. You
On Sat, Feb 6, 2010 at 12:11 PM, D. Dante Lorenso wrote:
> I have a system that imports about 40 million records every 2 days into a
> single table in MySQL. I was having problems with LOAD DATA CONCURRENT
> LOCAL INFILE where the table I was importing into would lock until the
> import was compl
On Thu, Jan 21, 2010 at 7:15 AM, Krishna Chandra Prajapati
wrote:
> 1. Inserted data into a table A by user.
> 2. Selecting data from table A inserting data to table B after applying some
> rules(update).
> 3. Deleting data from table A.
> 4. Selecting data from table B using some conditions (SELE
On Fri, Jan 15, 2010 at 2:54 AM, Johan Machielse
wrote:
> The problem is that multiple users can read and update the same field
> simultaneously (worse case) which could lead to unpredictable problems.
There are other ways to do handle most cases. For example:
UPDATE table SET value = value + 1
On Tue, Dec 15, 2009 at 11:58 AM, Keith Murphy wrote:
> Writers do block readers. Just at the row level vs the table level of
> MyISAM. It's just much less likely for writers to block readers.
No, they don't. Not unless you use an extreme isolation level.
InnoDB uses multi-version concurrency to
On Mon, Dec 14, 2009 at 8:32 PM, D. Dante Lorenso wrote:
> I have an "items" table that is heavily updated with 40 million records
> every 1 or 2 days and I need all those items indexed so they can be
> searched. The problem that I'm having is that the table is constantly
> locked because an inse
On Mon, Sep 7, 2009 at 8:18 PM, Allen Fowler wrote:
> Note: In this scheme the worker winds up with all "new" records generated
> since the last worker claimed any. Not sure how else to guarantee atomicity.
> I would prefer "only n records per request". Ideas?
SELECT...FOR UPDATE followed by one
On Thu, Aug 20, 2009 at 10:43 AM, Johnny Withers wrote:
> MySQL can only use one index at a time.
That was fixed years ago, in MySQL 5.0.
http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html
- Perrin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
T
On Tue, Aug 18, 2009 at 5:08 AM, Simon Kimber wrote:
> I have indexes on siteid, datestamp and msgtype.
>
> Queries such as the following are constantly appearing in the slow
> queries log:
>
> SELECT * FROM enquiries WHERE siteid = 59255 AND msgtype = 0 ORDER BY
> datestamp DESC LIMIT 5;
Read the
Nope, Nathan is right: INSERT...SELECT is a locking statement due to
statement-based replication. There's lots of info on this if you
google it. The common solution is to dump to disk and load back in
with SELECT INTO OUTFILE and LOAD DATA INFILE.
- Perrin
On Thu, Jul 9, 2009 at 3:55 PM, Walton
On Fri, Jun 5, 2009 at 9:09 AM, Aaron Fischer wrote:
> So I build the query in the order that I want it displayed. That is display
> 221593, then 221591, then CC3762, etc. However, when the query is executed
> it looks like it automatically sorts the codes in ascending order, so I get
> 221591, t
On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd wrote:
> So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I
> propose valid and SHOULD the optimiser recognise this and be expected
> to just find the 2 rows by searching on the primary key?
Not according to the docs:
http://dev.mys
On Fri, May 22, 2009 at 1:22 PM, Chris W <4rfv...@cox.net> wrote:
> Of course study to profile is a one to many relationship. How do I run an
> update to set p.`Date` equal to s.`Date`?
This is covered in the docs for UPDATE. Read that and come back if
you're stuck.
- Perrin
--
MySQL General
On Tue, Mar 17, 2009 at 12:42 PM, BobSharp wrote:
> These have been written successfully with Sub-Queries,
> I would like to know how they can be done with only JOINs ?
http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html
- Perrin
--
MySQL General Mailing List
For list archives: ht
2009/3/12 Carl :
> I am still a little puzzled about how we could have a relatively large set
> of records (100,000+) and yet not cause any table to be locked as the server
> has only 8GB of memory.
What's the relationship you're implying between memory and locking?
Multi-version concurrency doesn
I'm afraid enums are useless for anything except compressing your
data, since they don't reject bad values. If you want to limit a
field to a set of values, you need to use a lookup table and a foreign
key constraint.
- Perrin
On Fri, Mar 6, 2009 at 1:35 PM, David Karr wrote:
> If I define an e
2009/3/4 Carl :
> However, when I had all the pieces in the query
> (copy attached), I could easily see it was locking tables using the Server
> Monitor in Navicat.
I don't know what that is, but I think you'd better look at something
closer to the bone, like SHOW INNODB STATUS.
> Explain (copy a
On Wed, Mar 4, 2009 at 11:23 AM, Thomas Spahni wrote:
> SQL_NO_CACHE means that the query result is not cached. It does not mean
> that the cache is not used to answer the query.
Oh, right, he's looking for this:
SET SESSION query_cache_type=off;
- Perrin
--
MySQL General Mailing List
For lis
On Wed, Mar 4, 2009 at 10:27 AM, Morten wrote:
> Hi, I was hoping that using SQL_NO_CACHE would help me bypass the query
> cache, but judging from the below it doesn't.
You probably just brought the data into the cache and are not hitting
the query cache.
- Perrin
--
MySQL General Mailing List
My guess would be that your table is too small to bother using an
index on. There's some information in the MySQL docs about when it
chooses to use an index. For small tables, using one makes the query
slower.
- Perrin
On Tue, Mar 3, 2009 at 7:58 PM, David Karr wrote:
> I'm using MySQL 5.0.67-
On Tue, Mar 3, 2009 at 10:53 AM, Carl wrote:
> A query that is selecting data for a report locks the files that it accesses
> forcing users who are attempting to enter transactions to wait until the
> select query is finished.
Is it an INSERT INTO...SELECT FROM? Those lock. Also, have you
ver
On Tue, Feb 3, 2009 at 12:24 PM, Jerry Schwartz
wrote:
> Somebody, I think it was somebody from MySQL, said that you should never put
> anything into a WHERE clause that could be put into the ON clause of a JOIN.
> My guess is that this helps with the optimization, but it seems
> counter-intuitive
On Fri, Jan 30, 2009 at 9:32 AM, lance raymond wrote:
> basically I have an old fc5 machine running mysql 4.1 and a
> newer server running centos5 / mysql 5.0.45.
So, different hardware, different OS, different database server...
could be anything. I suggest you run EXPLAIN plans for the slow
qu
SELECT EXISTS(
SELECT * FROM WHERE
)
- Perrin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
On Thu, Oct 23, 2008 at 10:31 PM, Fayland Lam <[EMAIL PROTECTED]> wrote:
> B one SQLs with some operation
> SELECT col FROM table WHERE $where
> while $count is scalar @cols and real cols is splice(@cols, $start, $rows)
If you're talking about Perl/DBI, doing that normally loads the entire
result
On Mon, Oct 20, 2008 at 10:15 PM, Sangeetha <[EMAIL PROTECTED]> wrote:
> Gosh I wonder
> why mysql does not support SEubqueries.. It just hangs in the copying to tmp
> table status. Atleast I know whats wrong... Thanks very much
It supports them, but won't optimize them well until 5.2. There's
so
On Thu, Oct 16, 2008 at 3:40 PM, Reyna.Sabina <[EMAIL PROTECTED]> wrote:
> A test using mysql -e "LOAD table ..." was ran to see
> if "LOAD' will give an error when loading a record with
> the same primary-key of an existing record in the table.
Do you mean LOAD DATA INFILE? It will give an erro
On Wed, Oct 8, 2008 at 11:56 AM, Jim Lyons <[EMAIL PROTECTED]> wrote:
> Indexes speed up joins. Foreign keys should be indexes themselves, so they
> can also speed up joins. If the FK is not an index, it won't help. So,
> index your FKs
If you add a FOREIGN KEY constraint in MySQL 5+ it adds an
On Tue, Sep 23, 2008 at 10:09 PM, kalin m <[EMAIL PROTECTED]> wrote:
> i.e. i'd like to (assuming in the above example that _type_ and _amount_
> are column names) sum all the amounts but count only the ones with a certain
> type, all other amounts that are different type should not be part of the
On Tue, Sep 23, 2008 at 9:29 PM, kalin m <[EMAIL PROTECTED]> wrote:
> count(if(a.Type = "Signature Based Return", a.amount,'')) group by
> order by
I think you're looking for sum().
- Perrin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscri
On Thu, Sep 4, 2008 at 6:43 PM, Josh Miller <[EMAIL PROTECTED]> wrote:
> We'd like to prove InnoDB and move onto that storage engine for the
> transaction support, MVCC, etc.. but we're finding that performance is poor.
Well, thousands of large InnoDB database users prove that the engine
itself ha
On Thu, Sep 4, 2008 at 4:26 PM, Josh Miller <[EMAIL PROTECTED]> wrote:
> We're seeing a significantly higher percentage of IO wait on the system,
> averaging 20% now with the majority of that being user IO. The system is
> not swapping at all.
O_DIRECT may not be the best setting for your hardwa
On Thu, Sep 4, 2008 at 3:23 PM, Ryan Schwartz <[EMAIL PROTECTED]> wrote:
> I'll have to crack open my copy - haven't read through it in a while
If you have the first edition, I recommend getting the newer one. It
has a lot more tuning info.
- Perrin
--
MySQL General Mailing List
For list archi
On Thu, Sep 4, 2008 at 12:15 AM, Ryan Schwartz <[EMAIL PROTECTED]> wrote:
> We're seeing a huge surge in our qps and I'd like to make sure we're tuned
> as well as we can be. I'm wondering if I've got some variables maybe set too
> large (is that even possible?) ? We do have a fair bit of innodb, s
On Fri, Aug 29, 2008 at 4:57 AM, walter harms <[EMAIL PROTECTED]> wrote:
> Since diskspace is plenty i thinking about to use the name directly. does
> anyone has any idea
> what is the performance penalty ?
http://www.mysqlperformanceblog.com/2008/01/24/enum-fields-vs-varchar-vs-int-joined-table-w
On Thu, Aug 28, 2008 at 1:14 PM, Jose Estuardo Avila <[EMAIL PROTECTED]> wrote:
> My point is that on my process lists there are no writes being done at that
> time only reads and actually only one read all other reads are locked as
> well as writes.
Sure, that's because the reads are in line behi
On Thu, Aug 28, 2008 at 10:59 AM, Jose Estuardo Avila
<[EMAIL PROTECTED]> wrote:
> I understand that reads are locked by writes but nowhere does of mention
> that reads also block reads.
How could they not? You can't simultaneously read and write the same
data -- the read would get half-written g
On Wed, Aug 27, 2008 at 8:21 PM, Jose Estuardo Avila <[EMAIL PROTECTED]> wrote:
> Hi, I've been trying to find information on how myisam handles locks. I
> though myisam had locking only on writes and not on reads.
No, readers block writers. This true of any system that only has read
and write lo
On Wed, Aug 13, 2008 at 7:30 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote:
> Can u please guide me to any good books or URL for mysql sql tunning..
http://www.highperfmysql.com/
- Perrin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.
On Sun, Aug 10, 2008 at 10:54 AM, Kevin Waterson <[EMAIL PROTECTED]> wrote:
> I have 3 tables (schema below) with categories, questions and answers.
> Each category can of course have many questions in it. Also, the
> answers are multiple choice, so each question can have several
> related answers.
On Sat, Aug 9, 2008 at 8:10 AM, John Smith <[EMAIL PROTECTED]> wrote:
> Now here's the question: I need to lock certain rows, so that no other
> client can read or write that rows (I want those clients to wait until the
> transaction is commited or rolled back). I don't want to lock the entire
> ta
On Fri, Aug 1, 2008 at 7:32 AM, Rudolf Lippan <[EMAIL PROTECTED]> wrote:
> How would you get duplicated data? In one case you have an integer
> and in the other you have the value, but you still have to store one token
> of information with the row.
I meant in the case of storing the value di
On Thu, Jul 31, 2008 at 12:59 PM, Chris W <[EMAIL PROTECTED]> wrote:
> So my question is, is doing that way better than making the query more
> complex with all the joins?
If by "better" you mean "faster" then yes, it probably is marginally
faster. It would be simpler to just use the actual value
On Thu, Jul 31, 2008 at 8:00 AM, Fish Kungfu <[EMAIL PROTECTED]> wrote:
> Ideally, I was hoping COUNT() could work like this, BUT it doesn't of
> course:
>
> mysql> SELECT COUNT(SELECT aviName,MAX(dateTime) ,count(*) FROM
> aviTrackerMain WHERE DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP
> BY
On Thu, Jul 31, 2008 at 11:38 AM, Mr. Shawn H. Corey
<[EMAIL PROTECTED]> wrote:
> I don't see how that can be; with ENUM the DB has to set aside enough
> bytes for the longest identifier.
ENUMs are stored as integers.
> The only advantage of ENUM is that
> the data is in the same table; you don't
On Thu, Jul 31, 2008 at 9:46 AM, Jerry Schwartz
<[EMAIL PROTECTED]> wrote:
> Other than the fact that an ENUM can have many more values than a SET, is
> there any particular reason to choose one over the other?
The only use for ENUM is to make your data smaller. It offers no
protection against il
On Mon, Jul 21, 2008 at 2:43 PM, mos <[EMAIL PROTECTED]> wrote:
> I thought if MySQL found a duplicate key on the insert, it would
> automatically update the existing row that it found with the results from
> table1 if I left out the column expressions in the update clause. But
> apparently it doe
On Mon, Jul 21, 2008 at 11:44 AM, mos <[EMAIL PROTECTED]> wrote:
> I can't specify all of the columns in a Set statement in the
> OnDuplicate clause because I don't know what the column names are and there
> could be 100 columns.
Write code to do it. There is no way around specifying the co
On Sun, Jul 20, 2008 at 12:12 AM, mos <[EMAIL PROTECTED]> wrote:
> Is there a way to get "Insert ... select ... On Duplicate Update" to update
> the row with the duplicate key?
That's what it does.
> Why can't it do this?
What makes you think it can't?
- Perrin
--
MySQL General Mailing List
On Wed, Jul 9, 2008 at 12:47 PM, Brian Dunning <[EMAIL PROTECTED]> wrote:
> Can someone spot a way to improve the performance of this query? I see that
> every time it runs, it's "Copying to tmp table" and then "Creating sort
> index" and taking way too long.
You're sorting by a computed field. T
On Sat, Jun 21, 2008 at 8:34 AM, Joe Pearl <[EMAIL PROTECTED]> wrote:
> Thanks, but this is not the result I want.
I really think it is. You seem to be misunderstanding how GROUP BY
works. In any other database than MySQL, the SQL you wrote would
simply give an error. In MySQL, it gives you ran
On Fri, Jun 20, 2008 at 10:50 PM, Joe Pearl <[EMAIL PROTECTED]> wrote:
> I want to get back only the most recent entry for each person and I don't
> care about the order. I want the result to show Jim with the acq_date of
> "2008-01-03", Mary and Sally with the location and date for all of them.
On Tue, May 20, 2008 at 7:05 AM, Wakan <[EMAIL PROTECTED]> wrote:
> can someone could explain where are problems in this query:
>
> EXPLAIN
> SELECT ID
> FROM ven_tes
> WHERE ID IN (SELECT ID FROM ven_tes WHERE ID_ven=6573)
If that subselect only returns a single result, try using = instead of
IN.
On Thu, Apr 24, 2008 at 2:54 AM, Sebastian Mendel
<[EMAIL PROTECTED]> wrote:
> IMHO not in this case, cause it is just a simple "WHERE field IN ()"
I'm pretty sure that just looks like a bunch of ORs to MySQL. If it
didn't use the index with OR, it won't use it with IN.
What usually works is to
On Wed, Apr 23, 2008 at 9:22 PM, D Hill <[EMAIL PROTECTED]> wrote:
> Can anyone shed some light if I should index wite_desc to speed things up?
No, since you don't use that column at all. If you're not on MySQL 5,
upgrading to MySQL 5 will help. Otherwise, you're best bet is to
rewrite the quer
On Tue, Apr 22, 2008 at 11:41 AM, Bruno B. B. Magalhães
<[EMAIL PROTECTED]> wrote:
> I thing
> the most problematic part of those queries are the date range part, should I
> use a different index only for this column to maintain the index small?
My experience with doing data warehousing in MySQL
On Fri, Apr 11, 2008 at 4:01 PM, Victor Danilchenko
<[EMAIL PROTECTED]> wrote:
> Oooh, this looks evil. It seems like such a simple thing. I guess
> creating max(log_date) as a field, and then joining on it, is a solution --
> but my actual query (not the abridged version) is already half a
On Tue, Apr 15, 2008 at 4:21 AM, Sebastian Mendel
<[EMAIL PROTECTED]> wrote:
> use UNION
You can't use UNION to add the results of two queries. It would
return two rows.
- Perrin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysq
On Mon, Apr 14, 2008 at 5:40 PM, Jonathan Mangin <[EMAIL PROTECTED]> wrote:
> select
> round(sum(my_menu.carb * units) + sum(simple.carb),2)
> from itemized inner join simple using (uid)
> left join my_menu on itemized.personal_id = my_menu.id;
>
> Instead of 218.3 this returns 602, which is
>
On Sat, Apr 5, 2008 at 9:28 PM, Jonathan Mangin <[EMAIL PROTECTED]> wrote:
> select itemized.day_date as day_date,
> round(sum(my_menu.carb * units) + simple.carb,2)
> from itemized inner join simple using (uid) inner join my_menu on
> itemized.personal_id = my_menu.id where itemized.uid = 'me'
On Thu, Apr 3, 2008 at 2:28 PM, Dre <[EMAIL PROTECTED]> wrote:
> 1) Several sources seem to suggest MyISAM is a good choice for data
> warehousing, but due to my lack of experience in a transaction-less world,
> this makes me a little nervous.
MyISAM has the advantage of very fast loading. It's
On Feb 12, 2008 12:28 PM, Frederic Belleudy <[EMAIL PROTECTED]> wrote:
> select video_id from videos_innodb where state='QUEUE' limit 10 FOR UPDATE;
> => it's waiting for the first to session to commit, so I cannot get
> other videos with the same state!!
>
> commit;
> => I get 10 video_id.
On Feb 11, 2008 4:46 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
> Thank you for the link. Is there some way to get only the headlines and
> a summary for all entries? Reading through the entire contents by month
> and finding the misleading captions is hard work for such masses of
> content. The s
On Feb 10, 2008 5:30 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
> My problem is that the sub-select in line 7 ("SELECT 1") takes a rather
> long time. (When I remove it, it's much faster.)
This is a known issue with EXISTS/NOT EXISTS subqueries in MySQL,
which has some fixes slated for MySQL 6.
On Feb 6, 2008 6:40 AM, Britske <[EMAIL PROTECTED]> wrote:
> SELECT * FROM prices WHERE prices.productid IN (SELECT id FROM priducts
> ORDER BY id LIMIT 0, 1000)
>
> However, I'm getting an error-message stating that Limit is not allowed in a
> subquery.
> How would you approach this?
SELECT *
FRO
On Jan 31, 2008 1:30 PM, Jerry Schwartz <[EMAIL PROTECTED]> wrote:
> That's rather what I feared. I'm trying to avoid code, mostly because this
> should be a one-off run.
If it's a one-shot deal and you don't mind it taking a little time, I
think something like this would work:
SELECT customers.e
On Jan 30, 2008 5:25 PM, Jerry Schwartz <[EMAIL PROTECTED]> wrote:
> The problem starts when I try to fetch the memos. I want one email address,
> two or more customer_ids (so I can tell them apart), and all of the memos
> for each customer_id (preferably using GROUP_CONCAT(). In other words, this
On Jan 22, 2008 7:18 PM, Mont McAdam <[EMAIL PROTECTED]> wrote:
> In my opinion it should return every record from the table where `method` is
> equal to NULL, ZERO or EMPTY STRING, as this table doesn't allow null, and
> the field type is varchar, this would be EMPTY STRING's only.
Although it do
On Jan 11, 2008 7:22 AM, Sebastian Mendel <[EMAIL PROTECTED]> wrote:
> i can only find one source in the manual, where MySQL is using more than on
> index:
>
> http://dev.mysql.com/doc/refman/6.0/en/index-merge-optimization.html
Uh, how many sources do you need? It uses multiple indexes, just lik
On Jan 10, 2008 5:40 PM, Eben <[EMAIL PROTECTED]> wrote:
> I want to be able to do count of the total records brought back by this
> query... but since I am using the group by I can't use COUNT() in the
> desired way.
Assuming what you want is all the data from your current query plus an
additiona
On Jan 9, 2008 8:34 AM, Sebastian Mendel <[EMAIL PROTECTED]> wrote:
> yes, as written in the mentioned article the test is only relevant with
> correct used indexes, but MySQL does not use more than one index, so this
> query cannot all be done with indexes
Well, first of all, MySQL 5 does use mor
On Jan 9, 2008 4:33 AM, Sebastian Mendel <[EMAIL PROTECTED]> wrote:
> usually ... but i do not know of any index capable of having FULLTEXT and
> 'normal' fields in one index
Does that matter? It would have to be doing a full scan for
SQL_CALC_FOUND_ROWS to work out well.
- Perrin
--
MySQL Gen
On Jan 4, 2008 5:51 PM, Eben <[EMAIL PROTECTED]> wrote:
> The resultset is paginated on the front end using the
> SQL_CALC_FOUND_ROWS functionality...
Usually a bad idea:
http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
- Perrin
--
MySQL Gener
On Jan 3, 2008 4:23 PM, Tanner Postert <[EMAIL PROTECTED]> wrote:
> When I try to add a force index (PRIMARY) after the media table to try and
> make is use PRIMARY, rather than TYPE, the optimizer switches and uses no
> key at all.
It usually knows better than you do about indexes.
> I've tried
On Dec 31, 2007 3:05 PM, donr2020 <[EMAIL PROTECTED]> wrote:
> Sorry, I didn't type the subqueries quite correctly. They all have the same
> WHERE part (in this case, "WHERE Col1 = X" that essentially "joins" all the
> queries.
It still doesn't make sense to me. Count queries don't return
anythin
On Dec 30, 2007 1:50 PM, donr2020 <[EMAIL PROTECTED]> wrote:
> Our search engine does a master query INNER JOINed to a series of COUNT (*)
> subqueries that return what the number of results would have been had the
> user chosen different "filters" (or no filter at all). As an example:
Hmm. Why a
On Nov 27, 2007 10:21 AM, mos <[EMAIL PROTECTED]> wrote:
> At 05:57 PM 11/26/2007, you wrote:
> >The second query might be faster due to caching.
>
> This can be verified by executing:
>
> RESET QUERY CACHE
>
> before executing the second query. This will clear the queries from the cache.
No need
On Nov 13, 2007 3:32 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
> I found the Oracle reference and it says that locks can never lock
> queries, so reading a table is possible in any case.
No, you just have to use FOR UPDATE and it will block.
- Perrin
--
MySQL General Mailing List
For list arc
On Nov 13, 2007 11:39 AM, Baron Schwartz <[EMAIL PROTECTED]> wrote:
> InnoDB can also lock the gap, which will prevent new rows that would
> have been returned by the SELECT. The manual has more info on this in
> the section on consistent reads in InnoDB. FOR UPDATE will do what you
> need.
Inte
On Nov 13, 2007 4:53 AM, Yves Goergen <[EMAIL PROTECTED]> wrote:
> From that page:
> > Sometimes it would be useful to lock further tables in the course of
> > a transaction. Unfortunately, LOCK TABLES in MySQL performs an
> > implicit COMMIT and UNLOCK TABLES. An InnoDB variant of LOCK TABLES
> >
On Nov 12, 2007 5:58 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
> BEGIN TRANSACTION
> SELECT MAX(id) FROM table
> INSERT INTO table (id) VALUES (?)
> INSERT INTO othertable (id) VALUES (?)
> COMMIT
>
> First I find a new id value, then I do several INSERTs that need to be
> atomic, and especially
On Nov 12, 2007 5:24 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
> Damn, I found out that I need table locking *and* transactions.
What makes you say that?
> Maybe I'm really better off using a sequence (like the one PostgreSQL
> offers and like it is available as an add-on for Perl [1]).
That P
On Nov 12, 2007 2:43 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
> SELECT COUNT(*) FROM table WHERE name = ?
> -- a short delay which is long enough for a concurrent request :(
> UPDATE table SET name = ? WHERE id = ?
I think that even with SERIALIZABLE isolation level, this won't lock
anything if
On Nov 12, 2007 1:25 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
> When I start a transaction, then find the maximum value of a column and
> use that + 1 to write a new row into the table, how do transactions
> protect me from somebody else doing the same thing so that we'd both end
> up writing a
On 8/1/07, Les Fletcher <[EMAIL PROTECTED]> wrote:
> SQL_CALC_FOUND_ROWS isn't an option for us. Right now I am doing two
> queries, but I am just trying to see if there is a better way to do the
> count query than to just turn it into a dervied table and count the
> results i.e. SELECT COUNT(*) F
On 8/1/07, Jerry Schwartz <[EMAIL PROTECTED]> wrote:
> Will SELECT SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() do what you want?
Be careful. While it will give the correct number of rows that would
have been found if LIMIT had not been used, it makes the server
compute the whole result set, disab
On 7/23/07, mos <[EMAIL PROTECTED]> wrote:
Load data will of course be much faster. However to obtain the maximum
speed you need to load the data to an empty table, because then MySQL will
load the data without updating the index for every row that's added, and
will instead rebuild the index only
1 - 100 of 113 matches
Mail list logo
