Hi Ben,
It sounds like what you're looking for is an ENUM value:
http://dev.mysql.com/doc/refman/5.0/en/enum.html
Bear in mind when using this data-type that if you do want to add a new
value (such as a new state/country), you will have to perform an ALTER
TABLE statement, which can take some ti
Rene,
How are you querying the database during normal use? What kind of
applications are you using?
~Jeffrey Santos
On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier <[EMAIL PROTECTED]> wrote:
> Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229
> Opens: 489 Flush tables: 1 O
10% of queries are web-based (Apache/PHP).
30% of queries are from command-line PHP scripts that get executed
(average 1/second -- they end with mysql_close() btw).
60% of queries are from command-line PHP scripts that run continuously
(in a loop, with sleep()), acting on incoming socket data.
it's all a bit too general, we could be asking continual questions
until someone asks the right one.
However, I would put some debugging into the 30% scripts to check they
complete before the next one starts, as if one script takes slightly
longer (especially if the queries are the same) to
Appreciate the suggestions, some of which I've done. The processlist
typically just shows the known PHP command-line scripts that run.
Maybe 8-10 on average, 20 max.
Here's a strange thing: If I stop all the requests to MySQL (shut down
Apache, and exit all the commandline PHP scripts), MyS
In case a bit more data might help, here's what the server looks like
right now, while experiencing the strange high-CPU load:
VM_STAT sayeth:
Mach Virtual Memory Statistics: (page size of 4096 bytes)
Pages free: 534327.
Pages active: 331233.
Pages inactive:
nothing on a server is weird, just not understood yet :-)
so, 'show processlist' comes up with nothing. Does 'ps auxw' show
any php processes still active? Does 'netstat -atp' show any
established connections to mysql?
How long does it take to re-create the problem? You've restarted
On Mon, Sep 22, 2008 at 5:12 PM, Ben A.H. <[EMAIL PROTECTED]> wrote:
>
> There are various other fields that I believe could be handled like this
> for
> a cumulative performance boost. For example: country, state/province,
> gender, industry, occupation, ethnicity, language are all options that
>
On 9/23/08, David Ashley <[EMAIL PROTECTED]> wrote:
>
> For example, for the 50 states, a lot of programmers would put this logic
> in
> the web script and just store the two-letter postal code in the database
> table (but with no separate table for "states"). The mapping from "MI" to
> "Michigan"
On Tue, Sep 23, 2008 at 11:13 AM, Olexandr Melnyk <[EMAIL PROTECTED]> wrote:
> On 9/23/08, David Ashley <[EMAIL PROTECTED]> wrote:
>>
>> For example, for the 50 states, a lot of programmers would put this logic
>> in
>> the web script and just store the two-letter postal code in the database
>> t
> It sounds like what you're looking for is an ENUM value:
>
> http://dev.mysql.com/doc/refman/5.0/en/enum.html
>
> Bear in mind when using this data-type that if you do want to add a new
> value (such as a new state/country), you will have to perform an ALTER
> TABLE statement, which can take some
Hi Rene,
This smells like an old freebsd issue with a non thread safe
get-host-by-name issue and possibly other thread issues. Since Mac
OS/X/Darwin is a freebsd 4 branch it is a good bet they are the same.
Is it possible for you to try adding "skip-name-resolve" to my.cnf.
Alternatively you
On Tue, Sep 23, 2008 at 6:13 PM, Olexandr Melnyk <[EMAIL PROTECTED]> wrote:
> Plus, if the same query is run very often and table is almost static,
> chances are high that the result will be in query cache.
>
Just realized that I haven't mentioned that this sentence is related to
storing states i
Hello all,
Thank-you for all of your help, I was really surprised by the speed &
quality of responses. Below is a table I've created based on some reading I
did following everyone's suggestions (I hope the table shows correctly)...
I'm leaning towards the pure Relational as I like having all da
I figured that was what you meant... I guess my table didn't work (see above
message...don't ya' love plaintext :-O)...
Has anyone ever tried to benchmark the difference between utilizing ENUMs
vs. traditional relational databasing? I would think ENUM is ideal for items
I specified at the begin
hi all...
can somebody explain why a conditional count like this one doesn't work:
count(if(a.Type = "Signature Based Return", a.amount,'')) group
by order by
or
if(a.Type = "Signature Based Return", count(a.amount),'') group
by order by...
thanks...
--
MySQL
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
no, not really... sum is part of the query:
count(if(a.Type = "SBR", a.amount,'')), sum(a.amount) group by..
i want this:
go through the records.
count only the ones the have type = "SBR"
sum all amounts
etc...
etc...
group by date with rollup
i.e. i'd like
It doesn't work because you are counting values. An empty string is
still a value. Perhaps you are trying to SUM instead? If you are
really looking for a count, you also should use sum.
sum(if(a.Type = "Signature Based Return", 1,0))
That will return a count of those records where a.Type = "
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
try something like : count(if(a.type = 'SBR', 1, null))
count will ignore null value .. and only count the one with 'SBR' value
br,
Leo
On 24/09/08 12:09, kalin m wrote:
no, not really... sum is part of the query:
count(if(a.Type = "SBR", a.amount,'')), sum(a.amount) group by..
i wa
On 09/23/2008 02:42 PM, Ben A.H. wrote:
I figured that was what you meant... I guess my table didn't work (see above
message...don't ya' love plaintext :-O)...
Has anyone ever tried to benchmark the difference between utilizing ENUMs
vs. traditional relational databasing? I would think ENUM is
22 matches
Mail list logo
