MYSQL :: Usage Of DELETE In A Query!

MAX() Query Usage
I have a database of marks and I need to find out the maximum mark taken by each student in any subject. For example If am a student and I have got 60 in Language, 70 in Maths, 80 in Science and 90 in Social Studies, I would need the result "90" and the relevant Social Science subject to be displayed so that I know am good at Social Science of all the subjects.

Likewise, my friend got 65 in commerce, 70 in accountancy, 80 in economics and 90 in Applied Maths. As you may see here, the subjects are all different in both these cases. I only need to know this following result

That I am good at Social Studies and
my friend is good in Applied Maths.

The table name is marks. The fields are subject_id, mark_obtained, student_id.

When I try this query.

Select subject_id, max(mark_obtained) from marks group by student_id

In this case, I get the correct maximum mark, but the subject id displayed is incorrect. Can anyone tell me what am doing wrong here?

Getting CPU Usage Of A Query
There are some various queries running on my site that appear to use a whole lot of CPU resources under certain conditions which I haven't identified.

Is there any way I can see which queries are using up how much of the CPU instead of just being able to see the general process id for MySQL? I use cPanel and WHM to manage the server.

Count Query Causing "excessive Processor Usage"
For the second time in the past 6 months my hosts have shut down my site because it was apparently causing a shared mySQL 5 server to max out at 100% usage. I'm not much a programmer, know even less about mySQL and obviously don't have access to the server from the back end but I'd appreciate any advice as to whether it is my query that is causing the problem:

Set rsMail = objConnC.Execute("SELECT * ,(SELECT COUNT(*) FROM comments WHERE comments.submissionID = submissions.submissionID AND comments.commentInclude = 1) as CommentCount FROM submissions WHERE PigeonHole = 'mailbag' AND Status <> 'hold' ORDER BY submissionID DESC LIMIT 20")

Apart from two instances, this query has been working fine on a page that receives between 10,000 and 30,000 visits a day.

The site is running on Windows and coded in ASP but is calling a separate mySQL server.

Help With Delete Query
How do I delete all duplicate names except 1st entry where name is made up of FIRST_NAME (VARCHAR) and LAST_NAME (VARCHAR) from a table named ATTENDENCE?
And as an additional query how would I do this for only duplicate entries made within a 24 hour period if the row also contains a field named ENTRY_DATE (DATETIME)?

Delete Query Question
What would be the MySQL query for the following circumstances:
database name = database1
table = table1
field = field1
table1 has a number of rows, and within each row I want to delete a certain piece of text that appears in field1, call this "text1". What would the the MySQL query to do this?
Something like:
Delete from table1 where field1 equals text1
Also, if I wanted to make this into a cron job, how would I go about doing that?

Problems With Delete Query
i have this query:
DELETE FROM comuni WHERE comune in (SELECT nome from localita)

but if comune='Ancona' and nome='ANCONA' the row in not delete.
Why?

Limit A Delete Query
i have table B with FK to A (many to one)

I want to delete big amount of data from A,B (all the data that already read from the table and flaged as readed)

since i have the FK i try to delete data from B before delete A

i run this query
delete from B using B,A where Query_events.flag='yes' and B.A_B_Hjid = A.hjid

and it is ok . the problem that i want to limit it to 10000 records each execute

so i added delete from B using B,A where Query_events.flag='yes' and B.A_B_Hjid = A.hjid limit 10000

but i have problem with the limit....

Question On Delete Query
I have three tables. One is a person table with a membership_id field (and other fields), one is a many to many table - member_activity - with a membership_id and activity_id fields, and the third an activity table with an activity_id field (plus others).

I am not certain I want to delete records from the activity field because I may need to use them in the future. This leads me to the conclusion that instead of deleting a row of data I should just add a "deleted" field and mark it deleted by doing an update when I "delete" the data. This keeps the old (deleted) data in the database for use later on (some esoteric data mining).

If I use this approach, should the "deleted" field be in the activity table or in the member_activity table? Second, how do I construct a select query that will ignore all deleted records? Would it be something like: select * from activity a left join member_activity ma on (ma.activity_id=a.activity_id) left join person p on (p.membership_id=ma.membership_id) where activity_id="a number" and deleted='N'

LIMIT In Delete Query
I want write delet query who don`t delete 10 lines in table and delete all rest but I don`t know how, I`ll use ORDER and LIMIT but I don`t know how I can limit that with LIMIT!
I know this: LIMIT 10,[some number]
But I want delete all and I don`t know that number (rest after 10 lines.

Mysql Query Delete Row Without Primary Key
http://dev.mysql.com/doc/query-brows...gtabsheet.html

Note:
Queries derived from a single table lacking a primary key or from more than one table are not editable; they are read-only.

...........

how would I go about deleting table data if it doesn't have a primary key? I don't normally mess with any of the mysql/php stuff on the server.. but the current guy that does is MIA.. and I really need to delete an entry.

Delete Query Always Returning True
If there perhaps a setting or something I'm doing wrong.

$db = db::GetInstance();
$query = $db->query("DELETE FROM ens.`galleries` WHERE `id` = '$id' ");
$db->query("DELETE FROM ens.`news_gallery` WHERE `gallery_id` = '$id' ");
$db->query("DELETE FROM ens.`gallery_images` WHERE `gallery_id` = '$id' ");
return $query;
That will always return true, even if query has nothing to delete.

DELETE Query With Left Join?
I want to delete items from a table that are determined with a left join.

using

DELETE ( SELECT * FROM item_colors LEFT JOIN items ON item_colors.item_id =
items.item_id WHERE items.group_id =3 ) FROM item_colors

does not work.

how would be the right syntax?

The SELECT part returns the items I want to delete from item_colors
correctly.

Duplicate Records Delete Query
I have a table with duplicate records but the key of the duplicate records is different, that is:

The key of one of the record has the format:

Rssss

The key of the second record has the format:

ssss

How can I delete both records from the table?

How To Delete Fields From Different Tables With A Single Query
How to delete fields from different tables with a single query?

Phpmysql Query, Delete All People Not Logged In
I wish to run a sql query on my databse

Delete from Table: customers AND Table: customers_info

Where customers_info_id AND customers_id are the same

and

customers_info_number_of_logons = 0

This will remove all users that have not logged on, I would like to run this as a cron job as well but one thing at time....

Query To Select All Fields Using AUTO_INCREMENT, And Then Delete All Data With A Value Of 0?
Got a database with lots of tables using AUTO_INCREMENT quite a bit. Around a week ago a restore was done on this database from a backup made with compatibility mode turned on which removed the AUTO_INCREMENT value from the SQL. Problem I have now is that for all data being submitted to tables with fields using the AUTO_INCREMENT function are being submitted with a value of 0.

I have now fixed my database structure so all the fields that need AUTO_INCREMENT are set to use it, but now I have the problem that most of these fields have data with a value of 0 which is invalid. What I need help with is to write a query that finds all the fields in all tables in the database using AUTO_INCREMENT, and then delete any values of 0. Does anyone know how I can do this, and if so could you show me?

1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network.

I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables.

I've tried following query

DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11;

But it gives ' error in query.

Recovery Data From Delete Query And Multi Table Deletion In MySQL
When I test the Delete multi table function in MySQL,
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] table_name[.*] [,
table_name[.*] ...]
FROM table-references
[WHERE where_definition]

I accidentally delete all data in one table. All data in that table
are gone when I try to select them out in Control Center. But when I
go into the /mysql/data/mydatabase/, I see a MYD, MYI, frm for that
table. And it seems that data is still inside the MYD, althought it's
not formatted correctly. So my first qusetion is whether we can
recovery data from an accidentally delete action, using MYD or some
other log files.

My second question is about the multi table deletion in MYSQL
For example : "DELETE t1,t2 FROM t1,t2 WHERE t1.id=5 AND t2.id=5"
It only runs successfully when all table (i.e. t1 AND t2) have row(s)
whose id is equal to 5. In other words, it would NOT delete rows with
id = 5 in t1 when t2 has NO record with id = 5, both tables must at
least have one record with id =5. So is it the case for multi table
deletion in MySQL or I have done somethings wrong?

/tmp Usage
are there any best practices for /tmp on a std *nix install for MySQL, and if so, what are they ? Also i can't seem to find any more information on what specifically you'd require a LARGE /tmp for in MySQL.

C# Usage
Does anyone know if C# will work with mysql??? I know it does C++, but I don't know if the connectors or whatever are out there for C#

Index Usage
I have two tables, one is Student, which has studentid, personid, grade, fees as its columns. The other one is Person, which has personid, firstname, lastname, address as its columns. When given a studentid, I need to get the student' info, such as
lastname from the db tables.

I can do it with two queries as the following:

1. mysql> select personid FROM Student where studentid=1;
2. mysql> select lastname FROM Person where personid="the id I got above";

Or I can do it with one query:

3. mysql> select p.lastname from Student s, Person p where s.studentid=1 AND s.personid=p.personid

Both works. However, I can't tell which one is actually faster, since MySQL shows me "1 row in set (0.00 sec)" for all these three queries. Both studentid and personid are primary keys, so they are automatically indexed.

Will both indexes be used in query number 3? I saw people said that

MySQL could use in a query only one index...Does it mean only one index will be used in query number 3? I am wondering which one will give better performance.

Max(count(*)) Usage?
I have a table with a structure along the lines of

customerId
itemId
copyId

which form a primary key.

I want to know the max occurence of a particular item, based on copyId.

Ie, if 5 customers want itemId 6, 3 want copyId 1, 2 want copyId 2, should return 3 for itemId 6..

I thought something like

select itemId, max(count(*))
from items
group by itemId

might work, but alack, not the case.

Suggestions in how to do this with one query?

Counting Usage Per Day
I want to record how many times an application is used in a day. Is it possible to create a table with a date field that automatically creates a new record each day with the present date as the primary key.

Memory Usage
I've been analysing the memory usage of our server and each SQL thread seems to be taking up 10mb - since our site is basically a search site that means we're using 10mb per user which means with our 2gb RAM we can take around 120-200 simultaneous visitors.

Other than buying more RAM, what can I do to increase the number of simultaneous connections (ie - how can I reduce memory usage?) By my Calculations we need to be able to accept around 500 simultaneous searchers to meet our target.

We will also be increasing the amount of data we have - currently it is around 1.5 million records but that will be gradually increasing to around 10 million - which I'm sure will also increase the memory load on the server. Right now we have 1.4Gb Data and 380Kb overhead.

CURRENT_DATE Usage
I am trying to access some data from a database based on the date.

here is the SQL:

SELECT internallocation.LocationName, breed.BreedName, animal.DateBroughtIn from breed, animal, internallocation where (breed.ID = animal.BreedID AND internallocation.ID = animal.ShelterLocation AND animal.DateBroughtIn = 'CURRENT_DATE()')

This command returns no results... however, if I put in the date like so:

SELECT internallocation.LocationName, breed.BreedName, animal.DateBroughtIn from breed, animal, internallocation where (breed.ID = animal.BreedID AND internallocation.ID = animal.ShelterLocation AND animal.DateBroughtIn = '2005-06-24')

I get the results I am looking for. How can I use CURRENT_DATE to create a report based on the date, so the SQL does not have to be edited every time to change the day?

Wildcard Usage
Here is an example of the code:

$query = "SELECT * FROM table WHERE field = '*' ";

I want to return all the fields from the table where the field is any.

The program is a little more complicated than this. Basically using a dropdown menu, the user can search all, or a particular field. If they select "search all", I need the ability to insert that into the query statement.

Memory Usage
We have Mysql 5.0 running on RH linux. We have noticed that the swap memory increases but never gets released unless we reboot the server every 2-3 weeks. How would I know whats using this swap memory?
Second question which I think maybe related to the first is that mysql is using a lot of memory when I do a top. Is there a way to limit the memory usage of mysql? I have a large table in mysql about 7 GB and I have 8 GB physical memory and swam is set to use 2GB.

Delete Except Latest 2500 Entries (was "help With Query")
I have a table that holds information on activities.
For each activity I store the following:
ID, When, Description
The ID is the key for the table
The When column holds the datetime when the activity was added

The table gets filled up pretty quickly.
I would like to run a query that will delete ALL the entries except for the 2500 latest ones. The When column should be the one to use when deciding which are the 2500 latest activities

Can someone show me the most efficient way of doing this?

MySQL 4.0.xx Admin And Usage
I've recently taken over my non-profit's Web site design, administration and maintenance with relatively little MySQL experience (although I have learned a great deal very quickly). My question is, does anyone know where I can get a manual or something like the current manual "Building your Own Website Using PHP and MySQL .."(ordered from sitepoint.com site). This manual is basing the MySQL on the most current version of 5. I'm looking for something similar that addresses the 4.0.xx(27 specifically) of MySQL

LEFT JOIN Usage HELP!
Schema:

Table1
-t1field1 --> primary key
-t1field2
-t1field3

Table2
-t2id
-t1field1 --> foreign key of t1field1
-t2field2
-t2field3
-t2field4

Database Values:
Table1
row1: 1,aaa,123
row2: 2,bbb,123
row3: 3,ccc,123
row4: 4,ddd,123
row5: 5,eee,123

Table2
row1: 1,1,aaa,123
row2: 2,1,jjj,123
row3: 3,1,aaa,123
row4: 4,2,hhh,123
row5: 5,3,hhh,123
row6: 6,2,aaa,123
row7: 7,1,jjj,123

Query:
SELECT
t1.t1field1,
t1.t1field2,
t1.t1field3,
t2.t2field2,
t2.t2field3,
t2.t2field4
FROM
Table1 AS t1
LEFT JOIN
Table2 AS t2
USING(t1field1)
WHERE
t1.t1field2 = 'aaa'

Question:
Where do I insert a "WHERE clause" if i wanted to get t2.field2 with a value of 'jjj' only? or my query is totally wrong.

High CPU Usage (99.9%) On RHEL4
i need your help on this issue. I am running a web server in my orginazation internally with mysql-4.1.12 and RHEL4. Now, its been only one day and using 'top' command its showing mysqld process using cpu 99.9% constantly. This is used by say around 4000 users.

My my.cnf file :
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

output of mysqladmin version
..........
...
Threads: 53 Questions: 324297 Slow queries: 588 Opens: 39976 Flush tables: 1 Open tables: 64 Queries per second avg: 67.788

output of mysqladmin extended-status
Variable_name | Value |
+--------------------------------+------------+
| Aborted_clients | 742 |
| Aborted_connects | 35 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 29401860 |
| Bytes_sent | 1049908921 |
| Com_admin_commands | 2352 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 1 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 40895 |
| Com_change_master ...................................................................

Calculating Table Usage
how posiible is it to calculate a table usage in byte.

Analyze Index Usage
I am running a SELECT query that is taking approx. 200+ seconds to complete. I believe I have the tables indexed properly, but apprarently not. How can I determine what indexes a query is using and evaluate the execution plan (as you would in MSSQL)?

CPU Usage Issues On A Windows Box?
As the site receives over 50 simultaneous hits, mysql uses up just about all cpu usage... I have fine tuned everything - can any one offer any suggestions or share similar experiences??

Can I Limit Ram Usage In MySQL?
I just had my server shut down because my hosting company said I was using 350 to 450 megs of ram. This seems nuts to me as I'm only running a shopping cart with about 1000 items with only about 50 visitors per day.

Since they installed mysql on the server, I asked them why they hadn't configured it to work with the parameters of their system.

Their response: "Unfortunately, there is no way to limit the RAM usage for mySQL. I apologize for the inconvenience this may cause however there is no way to do this."

Do they know what they are talking about???

Orders Table - 100% CPU Usage
I'm starting to dive into something that looks like a PHP/MySQL issue that is bogging me down (PHP5, MySQL 4.1, Apache 2 on a Windows XP ws).

(At the end of this message, the PHP code)

We operate a virtual store and there's this administrative tool, loaded by the code included, which lists orders by category (new, shipped, cancelled etc.); on the top of the page, it first shows each category's totals (3 new, 18 shipped etc.). Obviously, these data comes from an "orders" table which, as of today, has 8.103 records.

I did increase already cache and buffer sizes on PHP and that helped tremendously to load the tool *if data doesn't change* in between. When it does, the following happens:

1. CPU usage goes all the way up to 100%, and
2. it takes approx. 45-60 seconds like that until the page ends loading.

I'm no PHP programmer, but it seems to me this has to be related to the code below.

Code:

Lowering Memory Usage?
I was wondering if there's any way I can lower MySQL's = memory

useage? It often uses more than 50000kb which I consider to much.. I'm guessing I should play with my.cnf or whatever it's called (located at = C:/ and windows calls it cardnumber or something)...

Mysqld Running 100 Pct Cpu Usage
I am testing mysql 4.1 on a Windows Server 2003 with a website running on Internet Information Services. I installed mysqld as a service.

As times goes by, mysqld begins to reach 100 pct cpu usage and doesn't goes below that, Windows usage is slow, folders opens slow, navigating locally turns to be slow, and sometimes it makes the server to freeze or restart.

When this begins to happen, I executed the command "Show processlist" and I got the following result. If somebody can point me where is my mistake or where is the problem or any solutions for this, I'm still in a learning process for mysql. Code:

Key_index_size Setting And Usage
I am using MySQL 4.1.12, compiled from source, on a Linux 2.6.11.10 kernel form the kernel archive. My Hardware is a Dual Xeon 2.8Ghz with 8GB of RAM and U320 SCSI disks. The database I am serving is about 6GB total, with the bulk of the data in 2 big tables, about 1.5 GB each.

The index files for these two tables are 1.4 GB and 415MB. In my /etc/my.cnf file I have the key_index_size set to 3GB, but the most that will get used is 1.49GB, either after doing an "Optimize tables" or an "INSERT INDEX INTO CACHE" statement.

My question is: How does MySQL decide how much of the systems memory to use for the key_index_size and any other of the processes allocatable address space. Also, under linux is the allocatable address space to an individual process 3GB or 4GB?

Allowable MySQL CPU Usage
I am running this query (with subqueries) in MySQL under Windows:
SELECT sum(quantity)/(SELECT sum(quantity) from orders), partID from orders group by partID

I noticed that it runs very slowly. It appears that MySQL is only using about 2% of CPU capacity, for some reason. The query takes almost 20 minutes to run on my table (whereas the same query takes only 1 minute in MS Access!).

Is there a way to allow MySQL to use more of the CPU, so these queries can run more quickly?

Usage Of OPTIMIZE Command
I had a table of more than 800'000 rows where I deleted approx 200'000 rows. The documentation says that I shouldn't normally have to use the OPTIMIZE TABLE command, but performance on this table has not improved. Is it a good idea to use it?

Proper MySQL Usage
I'm looking to use MySQL on my next project, but I don't really know how to set it up so it fits my needs.

I am making a Music Script Basically. All I need to store is Artist Name, Artist Videos, and Artist Songs. (just names of the stuff)

Artist Videos & Artist Songs are unknown, size wise. Different artists are going to have different numbers of songs and videos.

Lowering Memory Usage
there's any way I can lower MySQL's =
memory
useage? It often uses more than 50000kb which I consider to much.. I'm
guessing I should play with my.cnf or whatever it's called (located at =
C:/
and windows calls it cardnumber or something)

Measuring Database Usage
I'm trying to write a backup script, but want to be 'resource smart' about it. I'd like to backup my database every hour or 2, but want to look at the current draw on the DB prior to running the backup. If the draw is within pre specified limits, do the backup, otherwise, sleep for a min then try again.
This may be more of a job for a shell script, but I figured I'd give the mysql forum a try.

Showing Disk Usage
How can I see how much disk space is being occupied by an InnoDB table?

Looking into /var/lib/mysql doesn't seem to work anymore for InnoDB tables.

Please tell also if there is a way to see how much space is occupied by
a particular row or column of a table.

4.0.14 Runs CPU Usage Up And Eventually Stop
I am running 4.0.14 on a FreeBSD 4.8-STABLE machine (upgraded to CVS
from last week) and I am having a problem. We run a fairly large website
using PHP so there is always many connections to the database. On 4.0.12
the cpu usage never shot to over 20% and ran extremely fast. After
upgrading to 4.0.14, the cpu goes anywhere between 20-60% and in many
situations will not accept any database connections. After browsing the
website for a few minutes, I will type mysql on the command line and it
will give an error of too many connections. Does anyone know what may be
causing this high load from the upgrade? Again the previous versions did
not do this and ran very well. I could use 4.0.12 but I wanted to report
this in case it continues in future versions.

Single Computer (stand-alone) Usage?
I'm hoping to install MySQL on my home desktop computer (where I'm running Ubuntu Linux) and come close to simulating conditions at work (where we use MS Windows XP/Server/SQL Server). I'm also interested in doing this for an online "SQL Intro" course I'm going to be taking in October...

Is it possible to install MySQL and work from my desktop PC as both the server and the client? If so, can somebody point me towards some documentation?

Configuration And Usage Of The Relation Feature
im just creating a new database for a browser roleplaying game. Maybe you can imagine how complex this can be sometimes. And while i was working on it i remembered a feature of the MS access database. At access you could create relations for various tables, so if u change a field in one table, it is changed for all fields related/linked to it.

Is there a similar feature in mysql, and if yes, is it the "linked_tables" feature?