Indexed Totals

Indexed Search Capability
I'd like to have indexed search capability on column A, column B, or
columns (A,B) for a given table. According to the MySQL manual, a
multi-column index of (A,B) will provide "leftmost prefix" indexing, so
that I can search on column A uniquely or columns (A,B) together, but
not on column B uniquely. Therefore, I would need a second index on
column B.
My question: would there be any benefit, whatsoever, to having a
separate index just for column A, or is that completely redundant to
the multi-column index (A,B)?


View Replies !

---

Query Failing With Indexed (or Not)
I've got a bit of an issue here I'm hoping somebody will be able to help me with.

I have a large DB (1 million plus rows) and need to do a query with dual 'order by' AND limit.

I haven't found a good way of doing this efficiently.

If you would like to get a better idea of what I'm running,

Basically what I have is a table for bands, and a table for concerts. Here's a simplified version of the query (so too keep it concise and hopefully not overload with unnecessary info).

My 'SELECT' statement looks like this
Expand|Select|Wrap|Line Numbers

View Replies !

---

On Duplicate Key Update And Indexed Fields
The table has an autoincrement field that is the primary field. It also has id, date, and course_number that are indexed fields. Since the ON DUPLICATE KEY UPDATE how will I Insert and on duplicate update? The ON DUPLICATE KEY UPDATE works only when the field is the primary field. Will it work if the field(s) are indexed?

View Replies !

---

Slow Count(*) On Many Indexed Rows
we have a 10 million rows table. One field 'stamped' is either 'yes' or 'no'.
About half the rows are 'yes' and half are 'no'.
Table is indexed on 'stamped'.
We just need the count on 'yes'.

SELECT count(*) from T where stamped='yes'

or <>'no' or >'n'... is very slow.

Is there a better approach / query to get a fast result?

View Replies !

---

MySQL Timing Out? Really Slow Queries, Already Indexed.
For whatever reason, MySQL does not seem to be executing. So, I ran a few stat functions and here is what I got:

Uptime: 220372
Threads: 39
Questions: 18748899
Slow queries: 808
Opens: 28723
Flush tables: 1
Open tables: 4096
Queries per second avg: 85.078

The "open tables" worries me for a few reasons. First, because it is 4kb (exactly, since it is divisible by 1024, evenly). This makes me think that maybe I am hitting a ceiling.

Second, there are a few variables in MySQL that equal this, and maybe it's a cap on something. Here are the variables that equal 4096:
query_cache_min_res_unit = 4096
table_cache = 4096
transaction_prealloc_size = 4096

Do any of those have anything to do with this? Am I even close? Are any of those values from mysql_stat a big deal?

View Replies !

---

Optimising Bulk Inserts On Large Indexed Table
I'm trying to optimise large imports into a large table with keys. Here is the story so far. Can anyone suggest ways of creating a scalable solution?
>>

I want to allow periodic real time imports of up to 100,000 rows into a table with more than 2,000,000 rows. The table has 2 text indexes.

I use a 'load data infile' which is quick. Or was, until the number of rows in the table grew and the inserts slowed down because of index rebuilding (I assume).

I then started disabling and enabling the keys before and after the import, and ran the enable part off as a background process so the user didn't have to wait for it.

However, 'enable keys' locks the table, which makes selects and other operations on that table impossible during the process (currently about 2 mins).

My system variables look like this:

[mysqld]
set-variable = max_connections=500
safe-show-database
ft_stopword_file=''
ft_min_word_len=3
default-character-set=utf8
init-connect='SET NAMES utf8'
query_cache_type=1
query_cache_limit=2M
query_cache_size=64M
bulk_insert_buffer_size=64M
key_buffer_size=128M
log-slow-queries
delay-key-write=ON

View Replies !

---

Invalid Distinct Recordset Returned On An Indexed Column
I have a table with about 1.2 million records. I have an index set on a column.

For close to two years, this query has worked perfectly fine:

SELECT DISTINCT `Mgmt_Area` as thevalue, `Mgmt_Area` as valueid from qcdata ORDER BY thevalue

Note, this is a programmatically generated query based on some user selections. This query actually is used to create a listbox. The index has 62 separate values.

This query would usually return: Code:

View Replies !

---

Counting Totals
I have a table of messages. Each message has a me_date datetime. I want to get a count of the number of messages every day in the last 30 days - even the days when there were none. How can I do this in a single statement?
so far I have:

select count(me_id) as a, to_days(me_date) from messages where to_days(me_date)>to_days(now())-90 group by to_days(me_date)

but this doesn't include the 'zero' days (days when there were zero messages).

View Replies !

---

Totals/Mean Values
I assume this is a very simple question, I just don't know the answer!
I am planning on setting a MySQL database for some real estate property that has already been sold. The user will enter the information for each parcel sold for homes, land, etc.
What I want to do is:
a) Total the price columns for each and take the average price - have all of this calculate automatically
b) Also call on these total values from an intro page displaying the totals for each year
First, is this possible?
Second, how would I go about doing this? Do I need to add extra fields to the table for these totals, or are there MySQL commands to do the arithmetic

View Replies !

---

Calculating Totals From Two Tables
I have three tables: invoices, invoicedetails, invoicepayments

The fields are:

invoices
--------
InvoiceNo
InvoiceDate
CompanyNo

invoicedetails
--------------
InvoiceNo
ProductNo
Quantity
UnitPrice

invoicepayments
---------------
InvoiceNo
PaymentDate
PaymentAmount

For each row in invoices there will be 0 or more rows in
invoicedetails and invoicepayments, with the InvoiceNo field linking
everything together (i.e. one to many relationship between invoices
and invoicedetails and invoicepayments).

I need a query that will give me a list of invoices that still have
money outstanding on them.

To manually do this I would loop through the invoices table and for
each InvoiceNo I would gather all the matching rows in invoicedetails
and invoicepayments. To get the invoice total I would multiply
Quantity by UnitPrice for each invoicedetails row. To get the total
paid I would add up the PaymentAmount. Then I'd compare the invoice
total and the payment total and if the payment total was less than the
invoice total I'd know that there was still some money outstanding on
that invoice.

Now, how do I write a single query to do this? I using MySQL 4.0.20
(unfortunately because I don't control the server I can't upgrade to a
version of MySQL that support subqueries). I'm guessing I'll need to
use the SUM() function to add things up, and GROUP BY to group the
invoicedetails and invoicepayments so I only get one row per invoice.

I can get a total for each invoice by using the following query:

SELECT invoices.InvoiceNo, SUM(Quantity * UnitPrice) AS InvTotal
FROM invoices, invoicedetails
WHERE invoices.InvoiceNo = invoicedetails.InvoiceNo
GROUP BY invoices.InvoiceNo

However I'm at a loss as to how I would modify this query to also
total up the invoicepayments to give me a PaymentsTotal and then
calculate the difference between the InvTotal and the PaymentsTotal to
figure out if there is still money outstanding.

View Replies !

---

Getting Totals(or Percentage) Of Each Field
Say i have a select statement which selects 5 fields and displays the results as follows:

field1 | field2 | field3 | field4 | field5
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40

how can i add one more row to the output, which would calculate the total of each field and display at the bottom of the table??

for above example, the output should look like:
field1 | field2 | field3 | field4 | field5
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
Total 50 | 100 | 300 | 400 | 200

View Replies !

---

Computing A Difference In Totals.
I am using php/MySQL 4.0 ..

If I had a table consisting of:

team l points
-------------------------------------
teamA l 15
teamA l 10
teamA l 5
teamB l 5
teamB l 10
teamC l 5

if (mysql_query("SET @rank = 0;", $conn))
{
if ($result = mysql_query("SELECT @rank := @rank + 1 AS Rank, team, SUM(points) as ttl FROM table GROUP BY team ORDER BY ttl DESC, TM ASC;", $conn))

How would I get the result below?

rank l team l points l behind
--------------------------
1. teamA 30 0
2. teamB 15 15
3. teamC 5 25

I know how to implement the ranking of the teams .. I do not know how to get the point difference for the behind column. Is there a way to set the ranking to handle ties? (Example: 1,2,2,4,5 etc.)?

View Replies !

---

Showing Totals And Subtotals In One Row
I have a table with the following fields:

ContractID | CustomerID | ProductID | Quantity

For each Contract there is one record: Who has ordered which product in what quantity.

Now I'd like to generate a report that shows:

- which products were ordered (SELECT ProductID ... GROUP BY ProductID)
- at most (SELECT ... SUM(Quantity) AS Quantity ... ORDER BY Quantity DESC)
- and from which customers. (SELECT CustomerID, Quantity ...) GROUP_CONCAT(...)? Subquery?

Sample-Output:

P_ID - Quantity - Customer's quantities
----------------------------------------
1230 - 10'000 - A: 2'000, B: 8'000
1240 - 8'000 - A: 7'000, C: 500, D: 500
1120 - 6'000 - C: 6'000
...

How shall I build the SQL statement?

View Replies !

---

Getting Totals From 5 Tables In One Query...
This is something that has been puzzling me for a few weeks.
I have 5 tables in the database that I want the total rows count from. Now I know I could do 5 queries and use 5 mysql_num_rows to return the result but I feel sure that there is a better/easier/more efficient way of doing it.

i am guessing that it has something to do with joins but mysql really isn't my thang!

I Have tried something like:

select
count(hotel.id) as atb,
count(trainer.rec_id) as det,
count(club.id) as ml,
count(activity.id) as sl
from
hotel, trainer, club, activity
but that returns the totalled amounts added together which is why I figure a join is needed.

My aim is a online stats panel something like:
Leisure Clubs Onsite: 2442
Trainers Onsite: 232
Hotels Onsite: 1978

View Replies !

---

Select Statement, Grouping By Totals
I have an enormous database and I'd like to count how many times a unique record appears, then order the results based on that. For example:

select a, b, count(a) AS TOTAL from table GROUP BY a ORDER BY TOTAL DESC;

+-----------+------------+----------+
| a | b | Total |
+-----------+------------+----------+
| z | 2004-01-14 | 24 |
| x | 2004-01-05 | 22 |
| b | 2004-02-11 | 20 |
-------------------------------------

Meaning z appeard 24 times, x 22, and so on. This only returns the totals, not each row in itself. I need to have each row returned based on the amount of times it appeared. I obviously have to keep GROUP BY in there so I'm unable to ORDER BY TOTAL returned.

View Replies !

---

Selecting Totals For Multiple Dates
I have a form where a user can input two dates and I want to get a sum of the day's data for each of the days separately.

So far the closest I've come is:

// to display the total for one day
SELECT sum( hplmnmoc )
FROM `inRtccCallType`
WHERE host='wilsle03'
AND date='2007-05-25'

OR

// to display the total for all days
SELECT sum( hplmnmoc )
FROM `inRtccCallType`
WHERE host='wilsle03'
AND date BETWEEN '2007-05-24' AND '2007-05-31'

With what I have so far I can either display one day's total or else a total for the whole period. Can anyone tell me how to get the totals for each day individually without having to perform multiple queries.

View Replies !

---

Totals Query Based On Days
If I have a table with a ProductID, Quantity, & DateTime field, & would like to have the sum of the Quantity calculated per product per day with blank days being accounted for even if zeroed out, how would I go about accomplishing this in one query?

Example result for ProductX:

View Replies !

---

Fiscal Year Totals - How To Calculate?
I am given the month number for the fiscal year. For exmaple, "4" indicates the fiscal year begins April 1 each year. April 2, 2005 would be Fiscal Year 2005. March 30, 2005 would be Fiscal Year 2004.

With the following table structure:
TABLE_A
id
date
amount

My current set up is like this, based on calendar year:

PHP

// get a list of years in the db
$years = $dbh->getCol("SELECT DISTINCT(YEAR(r.date))
FROM table_A r
ORDER BY YEAR(r.date) ASC");

foreach ($years as $s) {
    $yearSum = $db->getOne("SELECT SUM(r.amount)
    FROM table_A r
    WHERE YEAR(r.date) = '$s'");
    //echo something here
}

ISSUE1:
I need to calculate the total for each fiscal year. For example, April 1, 2004 - March 31, 2005. AND April 1, 2005 - March 31, 2006, and so on and so on for all years.

ISSUE2:
I need to calculate the total for each MONTH within each fiscal year. For example, during the fiscal year April 1, 2004 - March 31, 2005, what was January's total, Feb's total,... For each fiscal year.

View Replies !

---

Gathering Totals From Multiple Tables
I've got a little bit of an issue which I need a little bit of mysql-guru help with. I want to get the top 5 users who have authored the most articles, and how many articles each has authored (total). The problem is, I have 3 tables in which I store the articles. I've got articles_faq, articles_kb, and articles_ref.

Each of these has an auto_increment field 'id', and an 'author' field (there's obviously more fields, but they aren't relevant).

How would I get this data in a single query? Is it even possible using MySQL?

Although I'm improving my MySQL skills and knowledge quite a bit, this is beyond what I'm capable of, that's for sure.

View Replies !

---

Query To Return Totals Of 1-5 Votes, Even If That Number Is 0
query to return totals of the votes:

SELECT count(good) as numgood, sum(distinct good) as scoregood, sum(good) as totgood
from survey where jobclass = 'Salaried' group by good

That returns this:

numgood | scoregood | totgood
27 | 4 | 108
70 | 5 | 350

What I need are results that include no votes cast for the other values:

numgood | scoregood | totgood
0 | 1 | 0
0 | 2 | 0
0 | 3 | 0
27 | 4 | 108
70 | 5 | 350

View Replies !

---

Viewing Date Range Then Adding Column Totals?
this is probably my most complex question to date. Basically i have a table that stores order information for products. What i need to do is:

- Specify a Date range
- Count number of rows in that range
- Get column totals for that range
- Return Array with column totals eg, if the array was named $total, $total['column1'] would be the column 1 total :)

This is a large table with many columns so here is what i had planned:

//OPEN CONNECTION HERE, SET DB
//First query gets date range:
$result = mysql_query("SELECT * FROM D_Orders_Columbus WHERE odate > '" . $startdate . "' AND odate < '" . $enddate . "'");
//now we get number of rows:
$num_orders = mysql_num_rows($result);

After that i get stuck, i need it to ADD the column values together, for this i assume i will need to set the column types to 'SMALLINT' (i dont assume anyone will order 32000 items :p). How can i get mysql to total all the columns that can be (eg. have number types) and then return an array with the totals?

View Replies !

---

How Do I Generate Results Based On Totals Of Another Table But For 1st Table?
This is what I want to do:

1- I have Two tables: polls_created and votes

2- Table polls_created is like:

poll_id
owner
poll_subject

3- Table votes has the votes issued for a given poll, like this:

vote_id
poll_id
vote
vote_date

So what I need to do is to look at these 2 Tables and generate results based on values of these 2 tables.

How do I then generate this result:

MySQL Code:
SELECT poll_id, owner, poll_subject, COUNT(vote_id) AS number_of_votes FROM polls_created, votes
"sorted by polls that have gotten most number of Votes"

Of course "sorted by polls that have gotten most number of Votes" is not real MySQL

View Replies !

---

Querying For Transaction Totals And Last Transaction Date
I have a list of currency transactions made by users. I need to generate a list of users along with their transaction total (sum for each user) AND the date of their last transaction.

Sound doable?

MySQL 4.1

Data looks like this:

user, amount, date
==============
1, 50, 2003-11-23
2, 34, 2004-10-04
3, 45, 2005-08-30
3, 98, 2006-04-02
3, 76, 2000-02-03
2, 91, 2000-12-04
1, 11, 2003-11-05
3, 22, 2003-03-06
4, 34, 2006-03-07
5, 45, 2006-06-24

I figure I can group by userID but how do I get the date of the most current transaction?

Using the data above, the query would return:
1 (user) 61 (subtotal) 2003-11-23 (last transaction)
2 (user) 125 (subtotal) 2004-10-04 (last transaction)

View Replies !

---

"Totals" Row For Columns
Looking for the result below from the sample table listed. Using MySQL 4.0/php. ROLLUP is not supported in my version of MySQL ...

table1
--------------------------
Person l Units l Amount
--------------------------
person1 l 7 l 11
person1 l 8 l 11
person2 l 13 l 6
person2 l 13 l 7
person3 l 11 l 7

SELECT Person, SUM(Units), SUM(Amount) FROM table1 GROUP BY Person

person1 l 15 l 22
person2 l 26 l 13
person3 l 11 l 7
"totals: l 52 l 42" = desired result

View Replies !

---