I'd appreciate some advice on how best to handle a biggish dataset
consisting of around 5 million lines. At the moment, I have a single
table consisting of four fields and one primary key:
partcode varchar(20)
region varchar(10)
location varchar(50)
qty int(11)
PRIMARY KEY (partcode, region, lo
You might consider adding qty to the index and have so your queries would be
satisfied with the index lookup alone, saving an extra step since the
database won't then go access the data (just the one field, qty).
You might also consider making all field non-null and, if you keep the
fields as char
SELECT ProductName FROM Products
WHERE ProductScore > 100
ORDER BY CASE WHEN ProductScore = 125
THEN 0
ELSE 1
END, ProductScore
But this query won't use an index, so it would be a good idea to do this in
two queries
2008/10/24 Tompkins Neil <[EMAIL PROTECTED]>
> Hi
>
> I've the following
Hi
I've the following basic query like
SELECT ProductName FROM Products
WHERE ProductScore > 100
ORDER BY ProductScore
However, how can I order by ProductScore, but ensure the product with ID 125
is at the top ? Is this possible.
Thanks
Neil
On Fri, Oct 24, 2008 at 6:59 AM, Mark Goodge <[EMAIL PROTECTED]> wrote:
> I'd appreciate some advice on how best to handle a biggish dataset
> consisting of around 5 million lines. At the moment, I have a single table
> consisting of four fields and one primary key:
>
> partcode varchar(20)
> regio
Following on from my email below I now need help with the following
problem. Here is a list of my sample data
Date ProductID ProductNameProductScore
Quantity
2008-11-10100 Red Light
0.05 10
2008-11-11100
SELECT ProductID,
ProductName,
AVG(ProductScore * Quantity) AS a
FROM Products
GROUP BY ProductID
ORDER BY a DESC
2008/10/24, Tompkins Neil <[EMAIL PROTECTED]>:
>
> Following on from my email below I now need help with the following
> problem. Here is a list of my sample data
>
> Date
Hi
This works, however I still want to be able to list the whole list like
because I need to display it on the screen, but in the ordered together i.e
all RedLights, all BlueLights etc
a Date ProductID ProductName
ProductScore Quantity
%2008-11-
Could give us sample values for "a" field? Should it contain the same thing
as in the query I've sent?
2008/10/24, Tompkins Neil <[EMAIL PROTECTED]>:
>
> Hi
>
> This works, however I still want to be able to list the whole list like
> because I need to display it on the screen, but in the ordered
Hi
Thanks for your quick reply. The sample value for "a" would be like a
average of integer. e.g 6.01, or 10.19.
Neil
On Fri, Oct 24, 2008 at 2:49 PM, Olexandr Melnyk <[EMAIL PROTECTED]> wrote:
> Could give us sample values for "a" field? Should it contain the same thing
> as in the query I've
Still doesn't make much sense to me. Could you show us how to calculate it
for some of the rows above?
2008/10/24, Tompkins Neil <[EMAIL PROTECTED]>:
>
> Hi
>
> Thanks for your quick reply. The sample value for "a" would be like a
> average of integer. e.g 6.01, or 10.19.
>
> Neil
>
> On Fri, Oct
Hi
Basically from the query below, it would only return one product like
RedLight. But I need to return a list of all products, ordered by a
SELECT ProductID,
ProductName,
AVG(ProductScore * Quantity) AS a
FROM Products
GROUP BY ProductID
ORDER BY a DESC
On Fri, Oct 24, 2008 at 2:53 PM, Ol
Yes, that's exactly what the link from Hassan said to do.
Interestingly, what is not stated in that link is that you must add
entries in mysql tables 'db' and 'user' for '127.0.0.1' (a.k.a.
'localhost') if you need to access the database from the local server.
Remote access via static IP (assu
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
Not on my testing system (Win-XP). I test on Win-XP but deploy on Unix.
Not only that, but the production application servers are separate from
the database server, so I never use localhost in production anyway.
But on the XP test server, I had to add the 127.0.0.1 entries to 'db'
and 'user' -
Hi Mark, all!
Mark Goodge wrote:
> I'd appreciate some advice on how best to handle a biggish dataset
> consisting of around 5 million lines. At the moment, I have a single
> table consisting of four fields and one primary key:
>
> partcode varchar(20)
> region varchar(10)
> location varchar(50)
Anybody know if there's a way to change a primary key field
that is not auto-incremented, turning on auto-increment but
preserving the values that are currently in it?
TIA,
Paul W
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysq
I have a Char column with text data like "ab:cdef:h" and I'd like to count
the number of ":" in the column. I can't find an easy way to do it. I
thought there should be a MySQL function to do this. Any suggestions?
TIA
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.c
I have a Char column with text data like "ab:cdef:h" and I'd like to
count the number of ":" in the column. I can't find an easy way to do
it. I thought there should be a MySQL function to do this. Any suggestions?
select length('aa:bb:cc:dd')-length(replace('aa:bb:cc:dd',':',''));
btw ever
A quick and dirty way would be to use an expression like
length(x) - length(replace(x, ":", ""))
Here's some code that I actually ran:
set @x = "aa:bb:cc:dd";
set @colons = length(@x) - length(replace(@x, ":", ""));
select @x , @colons;
On Fri, Oct 24, 2008 at 5:16 PM, mos <[EMAIL PROTECTED]
Hi.
I think you had a mistake for the server types.Mysql-nt.exe is the only
exexutable program on windows.So you just pay close attention about it.
On Mon, Oct 6, 2008 at 8:35 PM, Steven <[EMAIL PROTECTED]> wrote:
> > Steve
> (n)
> Martin!
>
>
> > if you want apache and mysql (and possibly PHP
Show the details of your hardware us.
On Thu, Oct 2, 2008 at 3:02 PM, Krishna Chandra Prajapati <
[EMAIL PROTECTED]> wrote:
> Hi,
>
> Just try the below command on console. It will give that the error is
> exactly related to what.
>
> $perror 5
>
> What is total ram in your box.
>
>
> On Thu, Oct
You may see the section named group by optimization on the document.
On Tue, Sep 30, 2008 at 4:44 AM, Rob Wultsch <[EMAIL PROTECTED]> wrote:
> Glancing over things I suggest:
> ALTER TABLE browse_nodes_to_products ADD INDEX(browse_node_id,product_id);
>
> (if product_id has greater cardinality pu
Make sure your log_bin_trust_function_creator is on.
On Sun, Sep 28, 2008 at 3:04 AM, Jesse <[EMAIL PROTECTED]> wrote:
> I'm trying to use existing functions from a restored database from 5.0xx to
> 5.1, and get an error about the mysql.proc table is missing or corrupt. The
> mysql.proc table app
Any additional tools will satisfy your demand.
On Mon, Oct 13, 2008 at 7:34 PM, Sudhir Menon <[EMAIL PROTECTED]> wrote:
> My reply was with regards to the answer in this post.
> http://lists.mysql.com/mysql/214827
>
> Anyways thanks for the concern from your end.. Mike :) My query was
> answered
See the usage of the function named field.
On Mon, Jul 28, 2008 at 8:15 PM, Mr. Shawn H. Corey <[EMAIL PROTECTED]>wrote:
> On Mon, 2008-07-28 at 07:32 -0400, Gary Josack wrote:
> > Andrew Martin wrote:
> > > Hello,
> > >
> > > Is it permissible to order a clause such that the search term is the
>
There are some of backup scripts written by me.You can find it at:
http://blog.chinaunix.net/u/29134/article_71953.html
On Wed, Oct 22, 2008 at 4:52 PM, Mad Unix <[EMAIL PROTECTED]> wrote:
> Any one tried the script from HowToForge
>
>
> http://www.howtoforge.com/shell-script-to-back-up-all-mysql
Just use another term will solve your problem.Or you can use pager more in
mysql command line client.
On Sun, Sep 28, 2008 at 12:44 AM, Dan Nelson <[EMAIL PROTECTED]>wrote:
> In the last episode (Sep 27), Alex Katebi said:
> > Hi,
> >
> > When selecting (example: select * from mysql.db ) some o
Create an extra trigger on that table with delete event.Then the deleted
item will be recorded in the database.
On Fri, Oct 10, 2008 at 11:34 PM, Jerry Schwartz <[EMAIL PROTECTED]
> wrote:
> Thanks.
>
>
>
> Although I've been around SQL for quite a while, I've never really gotten
> the hang of se
29 matches
Mail list logo
