Thanks Sergey.
The query is much slower with ref. Do you think if a composite index on
firstname and lastname would solve it? Table has 164+ million records which
makes me reluctant to create a new index due to the time required for index
creation unless I am pretty sure that the new index would
What kind of queries are being run and what type of data is stored?
There are a number of factors which causes MySQL to use on disk temporary
tables instead of in memory tables. (If there a BLOB or TEXT columns in the
table for example).
As a starting point you could (if you have the memory in
D. Dante Lorenso wrote:
All,
I am using MySQL currently, but am starting to think that maybe I
don't really need to use an RDBMS. The data I am storing ends up
getting indexed with Sphinx because I have full-text indexes for about
40 million records.
I have an items table that is heavily
i use MyISAM storage,the MYI file is more than 500Gb. most of the sql is
select with many 'and','or','order by',for example:
SELECT
fref,fstart,fstop,fsource,fmethod,fscore,fstrand,fphase,gclass,gname,ftarget_start,ftarget_stop,fdata.fid,fdata.gid
FROM fdata,ftype,fgroup
WHERE fgroup.gid =
I'm fairly sure that the longblob column will prevent MySQL from being able to
use 'in memory temp tables' regardless of whether it is included in the SELECT.
In an ideal world I would move that longblob to a separate table.
How big are the tables fdata,ftype,fgroup? Can you post the results of
Hi all,
I was informed by our security team about a recent attack attempts
related to the following MySQL issue/bug:
MySQL_Check_Scramble_Auth_Bypass (see tinyurl.com/y8vjbmm fro more info):
Systems Affected: MySQL 4.1 prior to 4.1.3, and MySQL 5.0.
We have MySQL Server 5.0.45 on RHEL 5.3,
yes,you are right,the longblob is already move to a separate table fdna,it
is about 10Gb/database the fdata is about 30Gb/database ,the fgroup is about
10Gb/database.the MYI file is almost the same or much bigger than the MYD
file.
show create table for fdna is:
| fdna | CREATE TABLE `fdna` (
Hi!
Facundo == Facundo Garat fga...@strixsolutions.com writes:
Facundo i don't really thinks this is about open source or not.
Agree, this has to do about competition and that Oracle, the leader in
revenue for databases are trying to buy MySQL, the leader in users
just to kill it off.
The 'order by' clause is forcing MySQL to use a temporary table; as a
test could you try running the query without the order by clause? Does
it run quicker? MySQL must use a temporary table if you 'group by' or
'order by' a column not contained in the first table of the select,
sometimes you can
Ok... in the file system, my MySQL files are located in /var/lib/mysql/
I have a database (lets say 'test') so it gets its own directory
/var/lib/mysql/test/
Now, all the tables go in this folder. (I know, we *should* all know
this...)
if the database is on another disk, could it increase
Yes, you can symlink it. How much performance benefit you get will depend on
hardware and traffic.
===
John Daisley
MySQL 5.0 Certified Database Administrator (CMDBA)
MySQL 5.0 Certified Developer
Cognos BI Developer
Telephone: +44(0)1283 537111
Mobile: +44(0)7812 451238
Take one perfectly functional production server running 5.0.77. It has been
up and running under load for quite some time. I am using xtrabackup for
backups and suddenly three or four days ago backup stop running.
Investigation shows that the socket file '/tmp/mysql.sock' is no longer
there. The
At 07:32 PM 12/14/2009, you wrote:
All,
I am using MySQL currently, but am starting to think that maybe I don't
really need to use an RDBMS. The data I am storing ends up getting
indexed with Sphinx because I have full-text indexes for about 40 million
records.
I have an items table that
Presumably someone deleted it :-) You can try an 'lsof -U |grep mysql' to
see if any processes still have it open. If the mysqld process still has it
open, it's probably not the server.
Why do you keep it in /tmp, btw ? My guess would be that someone (or some
process) decided it was time to clean
Thanks Johan. Yes, it shows the mysqld holding it open. Not suprising really
I guess. I did check the cron jobs and the history file searching for
something/someone who deleted it, but didn't find anything. Still, that is
what it looks like happend. I will move the sock file to /var/run/mysql so
Michael Widenius wrote:
Hi!
Facundo == Facundo Garat fga...@strixsolutions.com writes:
Facundo i don't really thinks this is about open source or not.
Agree, this has to do about competition and that Oracle, the leader
in revenue for databases are trying to buy MySQL, the leader in
On Mon, Dec 14, 2009 at 8:32 PM, D. Dante Lorenso da...@lorenso.com 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
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.
keith
On Tue, Dec 15, 2009 at 11:57 AM, Perrin Harkins per...@elem.com wrote:
On Mon, Dec 14, 2009 at 8:32 PM, D. Dante Lorenso da...@lorenso.com
wrote:
I
On Tue, Dec 15, 2009 at 11:58 AM, Keith Murphy bmur...@paragon-cs.com 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
Hi,
I am stuck with a suposedly simple query:
- i have two tables (:
PROGS
id_prog
name
EVENTS
id
id_prog
name
How can i list all records from PROGS with a sum of how many events each
have? I want to find the progs that are empty.
I remember something about using NULL, but i cant remember.
Hi Miguel,
You'll need to use LEFT JOIN, that will show all records that match and a row
in the second table will all values NULL where there is no match. Then you
find all those rows that have no match in your WHERE clause.
Regards,
Gavin Towey
-Original Message-
From: Miguel Vaz
I have a situation where I need to always get a row returned even if no match
is in the table (only 1 or many rows are acceptable).
I can use:
select a, b, c from mytable where a = 'yarp';
and might get 20 rows if there are matches, but I at least need 1 default row
back...
using :
select
Cantwell, Bryan wrote:
I have a situation where I need to always get a row returned even if no match
is in the table (only 1 or many rows are acceptable).
I can use:
select a, b, c from mytable where a = 'yarp';
and might get 20 rows if there are matches, but I at least need 1 default row
On Wed, 16 Dec 2009 07:39:09 Cantwell, Bryan wrote:
I have a situation where I need to always get a row returned even if no
match is in the table (only 1 or many rows are acceptable).
I can use:
select a, b, c from mytable where a = 'yarp';
and might get 20 rows if there are matches, but I
Dear MySQL users,
MySQL Server 5.5.0-m2, a new version of the popular Open Source
Database Management System, has been released.
The -m2 suffix tells this is the second milestone according to our
milestone release model, also called Betony.
You can read more about the release model and the
yeah,it runs faster if without order clause,as it do not use filesort here.
because of the huge data,it takes lots of time to copy them to tmp table or
even to the tmp table on the disk when use filesort, It also led to the
higher io wait! i am trying to increase the variable
26 matches
Mail list logo
