table), or
credential_id (since you're using that in your WHERE clause already).
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
primary key" error, then you can "select url from tb where
md5='' ", and compare the retreived url with the one you want to insert.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
problem putting all your shows in one table.
It'll also make it a lot easier to generate queries that cover all shows if
you want to generate summary reports.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
or otherwise protected against
multiple simultaneous access.
http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html
As for debugging, you should be able to write things to stderr which will
show up in the mysql logfile, or you could open your own logfile and write
to that.
--
Dan Nel
(status) as part of the
> result?
>
> so:
>
> 20 paul 2,3,1,20,9
> 19 john 20,9
> 75 mark 1,20,9
You want GROUP_CONCAT:
http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General
sensitive. You
can always do REPLACE(LOWER(text),from,to), though, at the expense of having
your result string lowercased on you. If you need to preserve case, try the
stored function at http://forge.mysql.com/tools/tool.php?id=135 .
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL G
ections will not change. When the
server restarts, it will fall back to whatever you have set in my.cnf, or
the default value if you don't have anything in my.cnf.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/m
n a different machine.
Try -DWITHOUT_SERVER=1 ; it's not documented, but matches the autoconf
--without-server flag, which was documented.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:ht
In the last episode (Oct 16), Kailash R said:
> Nice input Dan. Let me run some checks. My query is as follows:
>
> select group_concat(Field1), field2, field3 from table1 group by field2,
> field3 into str;
> @sql = concat("select blah ... where field1 in ' ,str);
ysql commandline. In batch mode, mysql
generates tab-delimited output which is easier to process.
o --table, -t
Display output in table format. This is the default for interactive
use, but can be used to produce table output in batch mode.
--
Dan Nelson
acket value. I am able to create variables containing
long strings with no problems, at least:
mysql> set @a=repeat('a',1024*1024*10);
Query OK, 0 rows affected (0.95 sec)
mysql> select length(@a);
++
| length(@a) |
++
| 10485760 |
+----+
1 row in s
formance though,
since mysql will have to stop accessing some tables to open others.
http://dev.mysql.com/doc/refman/5.5/en/not-enough-file-handles.html
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscri
at can
> specify what index to use as a hint, but this particular flip was
> particularly disastrous. It seems odd that the query optimizer would
> choose to scan a 3.5 million entry table instead of a 20,000 entry table.
Can you post the EXPLAIN EXTENDED output for your before and af
think you need to swap your arguments to DATE_FORMAT. The date comes
first, then the format string.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
5(uuid());
+--+
| md5(uuid()) |
+--+
| 6faefaf3f7bb9ba0d1e7a44cf6a9b1da |
| 740135ab69a1825630aeaf475b39f8b8 |
| 5c91a9132ad3e49e098e41d573de8e00 |
+--+
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Maili
the column definition is simply the maximum
allowed size.
http://dev.mysql.com/doc/refman/5.5/en/char.html
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
me other intermediate DNS client on your machine, bouncing that should
work. If not, you'll need to bounce mysql.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
In the last episode (May 12), HalÃsz SÃndor said:
> >>>> 2011/05/12 13:06 -0500, Dan Nelson >>>>
> In the last episode (May 12), Rocio Gomez Escribano said:
> > I found it,
> >
> > mysql> select userID from user where datediff(now(), userPay
ymentDate out of the function,
so if you have an index on that column mysql can use it:
select userID from user where userPaymentDate > (now() - interval 1 year)
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
+-+
| 1 |
+-+
1 row in set (0.01 sec)
See http://dev.mysql.com/doc/refman/5.5/en/xml-functions.html for more info.
> This is an example of the field (i need the 1 in cpu data):
>
> |
>
>
> |
--
d has both a long and short form. The long form is not
case sensitive; the short form is. The long form can be followed by
an optional semicolon terminator, but the short form should not.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
within the mysqld server
rather than the mysql client, and if there is a difference between your two
mysql commandlines it won't be noticed.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
On 2011-1-16 20:22, Jørn Dahl-Stamnes wrote:
Hello,
I got a table that store information about which photo-albums that a client is
viewing. I want to get the N last visited albums and use the query:
mysql> select album_id, updated_at, created_at from album_stats order by
updated_at desc limit
e system on a day that it's taking a long time to
run, run some "show processlist" commands and see if there are any INSERT or
UPDATEs running.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
e this should work (although the 2nd query won't be able to
use any indexes):
SELECT parent FROM post WHERE id = 10 into @parent;
SELECT id from post where FIND_IN_SET(id, @parent) > 0;
If you normalize your table so that you have one row per relation:
+--+-+
| id | parent |
+---
|
+-+-+
| -107.6898803711 | -107.6898780000 |
+-+-+
1 row in set (0.00 sec)
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
pace" model, and you cannot recover unused space without
dumping all your tables, deleting the ib_data* files, and restoring.
MySQL 5.5.5 has finally switched the default to innodb_file_per_table=on, but
if you are running any older version, you will need to set that value in
your config fil
ES ...)
> Sep 29 03:05:02 pse05 mysqld[14640]: *** (1) WAITING FOR THIS LOCK TO BE
> GRANTED:
>
> So I'm thinking we could use the DELAYED or LOW_PRIORITY.
INSERT DELAYED only works on MyISAM, MEMORY, and ARCHIVE tables. You'll get
a 1616 error if you try it on Inno
t; not, how can I adjust the space requirements for these tables so they
>> don't take up so much additional space?
Expect to see anywhere from a 1.5x to a 3x increase in size when converting
from myisam to innodb, depending on your field types and indexes. It's the
penalt
+--+---+
> 4 rows in set (0.00 sec)
>
> mysql> select p.weight from Passengers p;
> ++
> | weight |
> ++
> | NULL |
> | NULL |
> | NULL |
> |155 |
> ++
> 4 rows in set (0.00 sec)
>
> TIA,
> Victor
--
to be at the beginning or
the end of the block, so the block gets split in half and each new block
starts out 50% full.
http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-structure.html
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
PAGER set to 'date'
mysql> select "hello";
Wed Aug 25 15:43:46 CDT 2010
1 row in set (0.00 sec)
mysql>
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
pdated, resulting in an anomalous situation
You could use a trigger that updates C whenever A or B changes, to make sure
it stays in synch.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://list
gt; +--+
> | |
> +--+
> 1 row in set (0.00 sec)
>
> what goes with this, how can i ensure that this is NULL?
Your url may be the empty string "". If it was really NULL, you would see
"NULL" in the resultset. Try
SELECT * FROM product WHERE url=&quo
In the last episode (Jul 27), Dan Nelson said:
> In the last episode (Jul 27), Mike Spreitzer said:
> > If I want to try to actually hold a 2GB table in RAM, is there anything I
> > need to set in my.cnf to enable that?
>
> Just make sure your key_buffer_size is large eno
ry IO stats for the filesystem as a whole.
> In `vmstat` output, I thought "bi" is in terms of fixed-size blocks, not
> I/O commands.
It looks like on Linux, "bi" and "bo" are the total disk throughput in
kbytes.
--
Dan Nelson
dnel...@a
d the index. You
can find this number by setting key_buffer_size to a huge number (32GB for
example), running "LOAD INDEX INTO CACHE" for your index, then running "show
status like 'key_blocks_used'; ".
http://dev.mysql.com/doc/refman/5.1/en/load-index.html
--
ad 64GB of memory? I wouldn't expect 2gb to be noticed at
all :)
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
t; many rows as there are in the fldsnd table. I expect the result to be no
> larger than the fldrcv table. So it looks like the index is making this
> query run about as fast as can be expected, right? It did not take
> anywhere near 9 hours to make the fldrcv table ... so why is it taki
m the
default of 8 (try 16). That will make the estimate more accurate and
hopefully mysql will pick the right index consistently.
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_stats_sample_pages
Another solution might be to create another index on (c1,c2), since that
index is the
PDATE table SET column = replace(column, regex '%-%', ' ') where id = xxx;
You don't need a regex for that. REPLACE(column, '-', ' ') should do what
you want.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For
rrently-held locks, but there isn't :(
is_used_lock() will return the mysql connection ID of the session holding
the lock, so what I am expecting to see is zeros, meaning that for some
reason your mysql connection is getting dropped after your first query
completed. Locks are held for the lifet
irst available packet. That query shouldn't even
take 2 seconds, unless you have 50k rows in the table and no index on
work_data...
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
e password during
authentication. It's all done with hashed versions. You'll probably have
to cache the original password used to make the connection, if you need to
use it again later.
http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html
--
Dan Nelson
dnel...
s its tables. If they aren't in order for some reason, then
you can rename the groups of files into "a.frm", "a.MYI", "a.MYD", "b.frm",
"b.MYI", "b.MYD", etc, then run "show create table a" etc to determine the
table layout
om/doc/refman/5.1/en/mysqld-safe.html#option_mysqld_safe_syslog
Next best solution:
tail -F -n 0 /path/to/mysql.err | logger -p daemon.notice -t mysql
Run that before starting mysql, and any lines written to the log file will
get redirected to syslog.
--
Dan Nelson
dnel...@allantg
at mysql_store_result(), mysql_num_fields(),
mysql_field_count(), mysql_fetch_row(), and mysql_fetch_lengths(). There's
a simple code fragment to print a resultset on this page:
http://dev.mysql.com/doc/refman/5.1/en/mysql-fetch-row.html
--
Dan Nelson
dnel...@allantgroup.com
It works great for me. After working out the bugs and adding the spatial
index I am now searching in the 0.05 second timeframe vs. minutes
otherwise.
Dan
On Sun, 2 May 2010 23:39:41 -0700, Rob Wultsch wrote:
>>>> >> >>> On Sat, May 1, 2
x27;))'));
(I also created a new GEOMETRY lsd_poly column rather than the poly POLYGON
one).
Now I need to figure out the rest of it...
Dan
On Sun, 2 May 2010 14:00:16 -0700, Ted Yu wrote:
> Have you declared poly to be of spatial type ?
> Cheers
>
> On Sun, May 2,
NOT NULL
On Sun, 2 May 2010 14:00:16 -0700, Ted Yu wrote:
> Have you declared poly to be of spatial type ?
> Cheers
>
> On Sun, May 2, 2010 at 1:03 PM, dan wrote:
>
>>
>> Tried it but no luck:
>>
>> mysql> UPDATE `grid` SET poly = GeomFro
Sun, 2 May 2010 12:54:07 -0700, Ted Yu wrote:
> Have you tried replacing GeomFromText in place of PolygonFromText ?
>
> On Sun, May 2, 2010 at 10:59 AM, dan wrote:
>
>>
>> I am still lost... I tried this:
>>
>> UPDATE `grid` SET poly = PolygonFromTex
4145, 49.07756615 101.36764145, 49.07756615 101.36211395) |
| POLYGON(49.08123016 101.36211395, 49.07756615 101.36211395, 49.07756615
101.36764145, 49.08123016 101.36764145, 49.08123016 101.36211395) |
| POLYGON(49.08489418 101.36211395, 49.08123016 101.36211395, 49.08123016
101.36764145, 49.0848
mal lat / long data.
Dan
On Sun, 2 May 2010 06:43:13 -0700, Ted Yu wrote:
> I think you may have seen this:
> http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html
>
> On Sat, May 1, 2010 at 11:12 PM, dan wrote:
>
>>
>> Can any one hel
nds a specific point.
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
On Sat, 01 May 2010 15:28:46 -0500, mos wrote:
>
> SELECT * FROM `grid`� force index(section) WHERE n > 49.012 AND s <
49.012
> AND e >
> 110.0244 AND w < 110.0244;
>
> It should give you the answer around 0.1 seconds. Give it a try. :-)
>
> Mike
It actually makes it worse by ab
> 49.012 AND s < 49.012 AND e > 110.0245 AND w
< 110.0245; but takes an average of 15 seconds
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
long) to find the specific area that the point is located in.
I tried a simple index with my n,e,s & w but it still takes along time to
run such a query i.e. phpmyadmin times out. Any idea on the best structure
for such a query?
Thanks,
Dan T
--
MySQL General Mailing List
Fo
ed' ORDER BY DATE LIMIT 10
which will return the 10 oldest red products, even if they are the 10 newest
records in the table.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
changed
> > test> CREATE TABLE `lines` (id int unsigned NOT NULL PRIMARY KEY)
> > ENGINE=InnoDB; Query OK, 0 rows affected (0.20 sec)
>
> Yup, that was it. I'm migrating from Postgres, so it never occured to me
> that "lines" might be reserved.
On the
OW_PRIORITY keyword to your DELETE statement; that will keep the
DELETE from moving to the front of the queue if there are other SELECT
statements pending.
http://dev.mysql.com/doc/refman/5.1/en/delete.html
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For l
48 GB of memory and they all live just fine without swap.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
better than myisam
> tables for most OLTP users, and as your number of concurrent readers and
> writers grows, the improvement in performance from using innodb over
> myisam becomes more pronounced.
His scenario is "perhaps updated once a year", though, so crash recovery and
m
xist?
MySQL doesn't provide binaries, but the FreeBSD ports tree lets you build it
yourself, and has ports for MySQL 3.23, 4.0, 4.1, 5.0, 5.1, and 5.5.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To uns
your app does). RAID-10 is just a mirror so it doesn't have to worry about
that.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
gt;' '
> ordno qty
> 1
> 3
> 5
> 'aaa' 18
> 'b' 20
>
> select * from t1 where ordno>=' '
> ordno qty
> 'aaa' 18
> 'b' 20
>
> mysql version 5.
=' ';
+---+
| ordno |
+---+
| |
| |
| |
| 000 |
| abc |
+---+
5 rows in set (0.00 sec)
mysql> select * from table1 where ordno>' ' or ordno=' ';
+---+
| ordno |
+---+
| |
| |
| |
| 000 |
| abc |
+-
ions almost always treat subqueries as dependent, even ones that are
obviously not. The 6.0 branch was a significant improvement, but that
branch has been killed off, and there's no indication of the fixes being
backported to 5.x .
--
Dan Nelson
dnel...@allantgroup.com
, MySQL, ASP
> WAMP - Win, Apache, MySQL, PHP
> WWSHM - Win, Windows Scripting Host, MySQL
> WTM - Win, Tomcat, MySQL
> ...
>
> Are there other stacks on MS that you have heard of or are using?
WWJD - Windows, Websphere, Java, Derby? :)
--
Dan Nelson
dnel..
shes, and other operations completely
within MySQL. The events will also fire the same whether the server is
running Unix or Windows.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
;NGV21","NGV22","NGX09","NGX10","NGX11","NGX12","NGX13","NGX14","NGX15","NGX16","NGX17","NGX18","NGX19","NGX20","NGX21","NGX22","NGZ09","NGZ10",
> "NGZ11","NGZ12","NGZ13","NGZ14","NGZ15","NGZ16","NGZ17","NGZ18","NGZ19","NGZ20","NGZ21","NGZ22");
>
> Running query b gives me a result set as follows:
>
> | 2010-01-15 | NYMEX | NGZ22 | 8.9620 | 8.9680 | 8.9620 | 8.9680
> | 0 |
> ++++-+-+-+-++
> 86765 rows in set (4.46 sec)
>
> I then because I want to generalize query b I continue by creating query c
> as follows:
> mysql> select * from endOfDayData where endOfDayData.market like 'NYMEX'
> and endOfDayData.symbol IN (select names.symbol from names where
> names.market like 'NYMEX' and names.name like 'natural gas {%');
>
> Query c seems to have good syntax as neither the command line mysql
> interface nor the gui spit it back but it literally takes forever to run;
> I've waited at least twenty minutes and not got anything back. I'm running
> Ubuntu 9.10 on an intel core i7 with 4GB RAM and 12GB swap... the process
> monitor doesn't even flinch so I'm not thinking hardware here... why is the
> sub-query running so slow?
MySQL's subquery optimizer is pretty bad. I bet if you explain that query,
mysql thinks the subquery is dependant. That means that it will run the
subquery for each row of the outer query, even though it's obviously not
going to change from row to row. Your best bet for now is to do what you're
currently doing with queries A and B.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
haps his hospital ID is not 1234, or cMmrcashworker is not 2, or
cCurstatus = 'complete'. Try
SELECT updateDate,cCurstatus,cMmrcashworker,cHospital,cHospital1,cHospital2
FROM med_patient WHERE updateDate between '2010-01-01' and '2010-01-28'
order by i
27;outter' query using PHP's implode() or
> something. I suspect this would be significantly faster no?
IN() using constants should be very efficient.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
tml
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_open_files_limit
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_table_open_cache
http://dev.mysql.com/doc/refman/5.1/en/table-cache.html
http://bugs.mysql.com/bug.php?id=17646
http
users entered a blank space
> which is then counted as a character and is thus not null.
>
> This is causing all kinds of havoc for them as Oracle apparently still
> sees this as nulls.
I'm not sure what export method you're using, but you should be able to
fix t
ur case you would want to group by the first three
columns only).
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
to cache the entire
index, but not the data, so you need to do one disk seek per lookup). Next
best would be enough to cache all but the leaves of the index (requiring one
index and one table seek per lookup); this depends on your key size but
200MB should be enough.
--
Dan Nelson
n the mysqld error logfile (in your
data directory, it's the only file ending in .err). I don't think any of
the storage engines keep track of when the last analyze was done, though.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives:
me (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
27;,
> > `description` text collate utf8_bin NOT NULL,
> > PRIMARY KEY (`line_number`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
>
>
> - michael dykman
>
>
> On Sat, Oct 17, 2009 at 2:20 PM, Dan Saul wrote:
> >
n` text collate utf8_bin NOT NULL,
`daily_charge` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4 ;
Thank you in advance.
Dan
mysqldump --no-create-info --compact --where 'rowid between 100 and 1050' mydb
oldtable
Note that the inserts include the table name, so if you're loading into a
different table, you'll need to rewrite the output with sed, or maybe create
a "select * from oldtable" v
e, and the first batch of
inserts pulls most of the index and some of the table data into RAM, which
makes for much faster lookups on the next run. What do top and iostat stats
show on both runs? I'd expect heavy disk usage and little CPU on the first
run, and light disk and heav
awback here, if it's more at the
operational level of old Berkely DB 1.x-style databases (where one has to
manually maintain the index, there are no transactions, etc). SQL-based
databases sit on top of the lower-level table engines and handle all that
work for you.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
e. Assuming your input string is in mm/dd/yyy format, you would want a
format string of "%m/%e/%Y %r".
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ure.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-record.html
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
nd matching rows. In
general, you want an index on any fields used in a WHERE clause.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
you run just the "select ..." part, is it slow also? Do you have an
index on pat1.assignee? What does an EXPLAIN on the select print?
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
no: 145)"
$ perror 145
MySQL error code 145: Table was marked as crashed and should be repaired
$
You need to repair your table:
http://dev.mysql.com/doc/refman/5.1/en/repair.html
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archive
le containing all tables in all databases, then filter it on your
WHERE clause.
In your case, caching the results in your own table might be the best
solution.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
= "D")
> Primary key: id (int)
> Indexed on: Comm_id (varchar(6))
>
> create temporary table tmp type = heap
> select distinct 3 filterid, m.id, "" GroupLevel, 0 GroupCum
> from main m
> left join receipt r on m.id = r.mainid
> left join campaccommon.co
+-+-+---+--+---+
> | 1 | SIMPLE | app_user | ref | user_id | user_id | 8 |
> const |5 | |
> ++-+--+--+---+-+-+---+--+---+
> 1 row in set (0.01 sec)
--
eking for the same row will
> find it using the index?
Yes.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
l
only has to jump to the 495536 quiz_id section, and all the user_ids are all
right there.
That should cut your query time by 50% (since you still have to do 68 seeks
to the table rows to fetch result_id). If you also add result_id to your
compound index, then mysql will be able to get all its inf
like to insert a record
> into a entirely separate table in a separate DB.
insert into smsgw.outbox [...]
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
its values by SETting
NEW.fieldname:
IF NEW.NASPortID = 21 THEN
SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets);
SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets):
END IF;
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
M table where numbers LIKE '8,%' or numbers LIKE '%,8,%' or
> numbers LIKE '%,8'
Even better:
SELECT * FROM table WHERE find_in_set('8',numbers);
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set
--
Dan Nelso
se the MySQL thread does not end although complied with
> LINUX_THREADS.
Try building without LINUX_THREADS; that option shouldn't really be used
with FreeBSD 6 or newer.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://list
In the last episode (Jun 30), Dainis Polis said:
> Hi MySQL fans!
>
> Is there way to create subj ?
http://dev.mysql.com/doc/refman/5.1/en/adding-functions.html
http://dev.mysql.com/doc/refman/5.1/en/adding-udf.html
http://dev.mysql.com/doc/refman/5.1/en/udf-aggr-calling.html
--
--+---+---+-++--+---+
Note that the queries have flipped and aren't nested anymore (id is 1 on
both queries). The first query uses the ename index and estimates it will
return one row. The second query uses the mgr i
n user_id, but after in, order use temporary table,
> How to optimize it?
Mysql should have been able to use the index here, I think. Please post the
output of "create table user", a sample query, and its EXPLAIN output.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
1 - 100 of 2073 matches
Mail list logo
