MYSQL :: Usage Of OPTIMIZE Command

What Does OPTIMIZE Do
I use phpMyAdmin and constantly see the OPTIMIZE TABLE option on the structure page with a number of bytes highlighted. You press the button and the bytes are gone! Great!!!

But what exactly did that just delete? Should you optimize or just leave the databases as they are? Is there a chance of corrupting the data in a table when you run this?

Optimize This Sql
optimize this sql

SELECT DISTINCT(ibf_posts.author_id), ibf_topics.* FROM ibf_topics LEFT JOIN ibf_posts ON (ibf_topics.tid=ibf_posts.topic_id AND ibf_posts.author_id=25869) WHERE ibf_topics.forum_id=4 and ibf_topics.approved=1 and (ibf_topics.pinned=1 or ibf_topics.last_post > 0) ORDER BY pinned DESC, last_post DESC LIMIT 0,30 .

Optimize
the following query takes forever on 80,000 records. I was wondering if there was anyone that sees how I could speed this query up.
SELECT c.products_model AS model, SUBSTRING(warehouse_sku FROM 4) AS sku, a.products_id AS products_id, products_viewed
FROM products_description AS a, products AS b, products_to_warehouses AS c
WHERE a.products_id = b.products_id
AND b.products_model=c.products_model
AND warehouse_id = 2
ORDER BY products_viewed DESC;

/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#

Optimize And Tuning
i was reading a post from long ago, and suggest for better performance at the mysql server to check the queries adn then make an optimization. i have this db that's eating lots of resources, and usually reaches 90% for the cpu and almost everything is used by the mysql.

i supposse itÂ´s a matter of tuning vars, wich one would you suggest to tackle first, and what others next in an myisam engine

Shorten/optimize This?
I have 3 fields I want to search:

categories table's Title field.
items table's Title and Info fields.

The titles are < 25 chars, and Info < 50 characters.

So, I figured a LIKE '%SEARCH%' is best... is there something else I should do?

Right now I split up each word in the search, so a search for "multiple word search" gives me these two queries:

SELECT Id, Title FROM categories WHERE Title LIKE '%multiple%' OR Title LIKE '%word%' OR Title LIKE '%search%'

SELECT Id, Title, Info FROM items WHERE Title LIKE '%multiple%' OR Title LIKE '%word%' OR Title LIKE '%search%' OR Info LIKE '%multiple%' OR Info LIKE '%word%' OR Info LIKE '%search%'

How Can I Optimize This Query?
tables:
---------
branch:
PK = branch_ID
(has many)
galleries:
PK = gallery_ID
FK = branch_ID
(has many)
photos:
PK = pic_ID
FK = gallery_ID

query:

select x,y,z
from galleries join photos
on photos.gallery_ID = galleries.gallery_ID
where galleries.branch_ID = 1
order by photos.img_weekHits desc,photos.img_hits desc
limit 0,1;
explain says the following:
---------
table: galleries
type: simple
possible keys:3
ref: const
rows: 200
extra: Using where; Using temporary; Using filesort
---------
type: simple
table: photos
ref: galleries.gallery_ID
rows: 35
extra: Using where

I need to run this query for each branch of my website (currently there are 7). the query gets the most popular picture for the week from that branch.

All fields used in where, joins and order by are indexed.

The photos table has 40 000+ rows in it.
The galleries table has 500+ rows in it.

The query takes about 3-500ms on my laptop to run for my busiest branch, and less for less busy branches.

The page that runs the query is cached, so the query is only run every half hour or when new content is added or the cache is flushed.

Is there any better way i can run this query, or alternatively achieve the same result with putting less load on the server?

Can I Optimize This Process?
I have a script that reads data records from MySQL and creates a table of all the records and their subsequent data for viewing. Each page of the script will only show 10 records, but with over 10,000 records, there are many many pages of records... thus I have pagination links at the bottom.

Now for the pagination to work correctly, I have to count the total number of records in the specified query, but to display only the 10 records per page, I have to run the same query a second time and add the LIMIT clause with start and display limit parameters. So right now I am calling the same query essentially twice, only the second time I specify the LIMIT clause. These queries are rather huge with multiple joins and many many columns of data.

Is there any way I can optimize this process and get the total record count of the specified query for pagination purposes, but also be able to limit the start and display count for each page as aforementioned. Or is this something I just have to use 2 seperate queries for?

Optimize Mysql
how to optimize my mysql server. I'm using DELL 2850 with 3Gb cpu and 2Gb RAM, Redhat Enterprise 3 and MySQL 4.1. Every 30-50 minutes mysql will be busy & very slow. Code:

Optimize Problem
I have a large'ish mySQL table, very text heavy (although no fields
are larger than varchar 255). I do a bulk update once a day, which
probably removes a 1/4 of the records, replacing them with fresh
records. I find my table swiftly becomes massively inefficent, with
the reported overhead soon exceeding the actual size of the table.

So my questions are:

(a) why does this happen?
and

(b) is there some programmatic way to perform the OPTIMIZE from my
java updating process?

Deletion And Optimize
I have a summary table which gets updated every half hour. Each half hour I delete the previous record for that day and the new records are summed up at the end of each half hour and gets inserted. This table has no primary/unique keys but only indexes.

After the insertion I do a optimize. This generally takes some time to optimize. Is it good idea to optimize the table after each deletion. I would like like improve the performance of my servers and also the query retrival time.

Optimize Query
What indexes are required to speed up the following query.

SELECT COUNT(*) AS cnt FROM company WHERE state = 'Maharashtra'
AND city = 'MUMBAI' AND companyName REGEXP '^D' GROUP BY parentid;

Optimize Search
If you want to retrieve data in a 10000 rows-table w/ simple WHERE clause, eg: select this where col1=val1 and col2=val2, this'll take forever (I would think, I never worked a database with such capacity.), 'cause all rows are accessed sub-sequentialy.
Are there ways to optimize this search? I read in some articles one way to do it is by indexing, but I don't quite understand how this indexing works,

Optimize Table
Optimize table in mysql is nice!! but seems to require table names.
Any way I can run it for my entire db? I like to do this in a batch
process from time to time.

FYI, I was having SERIOUS perf. problems on my box, until I optimized
the tables that had huge number of rows deleted. Don’t believe the
mysql documentation that this is not needed.

OPTIMIZE TABLE
Is it safe if the 'OPTIMIZE TALBE [table_name]' query is issued frequently(every 30 second)?

The MySQL manual,[13.5.2.5. OPTIMIZE TABLE Syntax], says,
"In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it is not likely that you need to do this more than once a week or month and only on certain tables."

Optimize Query
I'd like to optimize this query:Code:

SELECT *
FROM `links`
WHERE active = "1" AND mainweight != 0 ORDER BY Rand()*(1/mainweight) LIMIT 5

I have a database of links wich has 3 000 rows. I'd like to select weighted random links from it (mainweight is the weight of the link). On my serwer this query is executing 0.8 sec. When i have 50-100 users on my site this is a problem.

OPTIMIZE Database
I've recently noticed that my database has some overhead, and i would like to go about solving this issue, a short search on this forum noted something about running OPTIMIZE on the database or something like that, is there anyone that may know what exact command i need to run to OPTIMIZE the database (via PHPMyAdmin)

Optimize Problem
I have a large'ish mySQL table, very text heavy (although no fields
are larger than varchar 255). I do a bulk update once a day, which
probably removes a 1/4 of the records, replacing them with fresh
records. I find my table swiftly becomes massively inefficent, with
the reported overhead soon exceeding the actual size of the table.
So my questions are:
(a) why does this happen?
and
(b) is there some programmatic way to perform the OPTIMIZE from my
java updating process?

Optimize - NULL V.s. 0 Or ''
To optimize a fairly large table with variable length fields, is it generally better to use a default value of NULL or an empty string?

ex: for char types, use NULL in stead of ''
or for numeric types, use NULL in stead of 0

if there was a notable advantage/disadvantage to a lot of NULL values in a large table, and if so whether it is due to disk storage requirements or processing time/overhead?

Optimize Question
if the following is the best way to construct a table for searching x, y, and z coordinates
(especially for ranged search retrievals <, >, <=, >=, etc)
Code:

create table map (
x smallint unsigned not null,
y smallint unsigned not null,
z tinyint unsigned not null,
t smallint unsigned not null,
primary key(x, y, z)
)

The t column is simply the data at the xyz coordinate

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.

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.

Optimize Rand() On A Big Database
I am using RAND() and I know it is killing my MySQL performance. My database is currently at 200K rows and growing quickly. Here is a general mysql SELECT that is looped about ten times depending on the page.

PHP

if($drange == "thisweek") {
$subquery = "SELECT articles.title, articles.link,
articles.date, site.site_name
FROM articles,feeds,site
WHERE articles.article_id != $articleid AND
(week(articles.date)=week(now()) AND
year(articles.date)=year(now())) AND
articles.feed_id=feeds.feed_id AND
feeds.sub_id=$subid AND feeds.site_id = site.site_id
ORDER BY RAND() LIMIT 3";
}

I am trying to optimize this to NOT use RAND(). For this query, it is looking at all the latest additions over the past week. I have thought about running this query only selecting the IDs of the rows, then randomly selecting 3 numbers out of the selection and running the query again just to select the rows. But would that be faster?

Optimize SUM() Function With JOIN
SELECT DISTINCTROW marketing.ID, marketing.jobID, marketing.courseID, marketing.type, marketing.date, marketing.mktArea, marketing.firmsType, marketing.indvType, marketing.codes, marketing.specs, marketing.topbox, marketing.finalExport, marketing.notes AS FirstOfnotes, Sum( bookings.amtPaid ) AS SumOfamtPaid
FROM marketing
INNER JOIN bookings ON marketing.ID = bookings.marketingID
GROUP BY marketing.ID, marketing.jobID, marketing.courseID, marketing.type, marketing.date, marketing.mktArea, marketing.firmsType, marketing.indvType, marketing.codes, marketing.specs, marketing.topbox, marketing.finalExport;
EXPLAIN shows:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE bookings ALL NULL NULL NULL NULL 67289 Using temporary; Using filesort
1 SIMPLE marketing eq_ref PRIMARY PRIMARY 4 plt.bookings.marketingID 1
The index is working fine on marketing.ID (the primary key) however i cant find an index which works on the bookings table.
Ive tried adding a key on bookings.marketingID however it shows it up as a possible key but doesnt use it.
Ive also tried creating a composite key of bookings.amtPaid and bookings.marketingID, it uses this key however the number of rows it has to look through stays the same.

I cant see what else I can do to optimize this query.
It takes a while to open as for every marketing.ID it has to work out the SUM of the booking.amtPaid.

Precedence In WHERE Clause - Trying To Optimize
How can I optimize a query like:
SELECT COUNT(*)
FROM ((table1 o
INNER JOIN table2 j ON o.ID = j.ID)
INNER JOIN table3 f ON j.f_ID = f.f_ID)
INNER JOIN table4 c ON c.f_ID = f.f_ID
WHERE j.end = &#55612;&#57200;-00-00'
AND c.dateC > j.startDate
AND c.sub = 'Y'
AND f.l_ID = '$lk_ID'
AND c.show = 'Y'
AND c.dateC BETWEEN '$lastyear' AND '$today'

Do I look at the which parameter in the WHERE clause will have the fewest records and put that first in the WHERE clause? Then the next fewest,...

MySQL Is Slow, Trying To Optimize
It just seems that my system is slow, adding records, etc. I tried to optimize it, saw that there was a my-medium.ini file, read that is was for medium sized system. I replaced it with my.ini (yes I did make a backup, thankfully) and restarted mysql.

Pretty much it hangs. I tried to connect with QueryBrower, did a Select Count(*) and just froze. I admit that I didn't wait for ever, yes I know I Select Count(*) takes a long time but I gave up after waiting 3x as long as I normal did. Also the logs screen froze in administrator.

Optimize Table With LOCAL Arg
I am running mysql 4.0.16. Optimize table does not work with LOCAL or
NO_WRITE_TO_BINLOG options, and gives me mysql error. Any ideas?

Optimize Select Statement
I have a big simple table with 3 fields and 700,000 records.

Table: mytable
Field1 Field2 Field3
name1 initial1 1
name2 initial2 0
name3 initial3 0
name4 initial4 0
... ... ...

i like to be able to do a fast search where it can return the first record that has 0 in field3. If i go through the whole table, it takes too long.

Select * from mytable where field3 = 0; <---- it takes too long to do this

i would like to get the record # 2 because this is the first record that field3=0

Optimize Or Repair Db Tables
I bought a dating script online and been running it for 1 year, has about 1500 profiles, but the site is too slow now. Not about the host because I have godaddy host.
When I ask the script owner, they told me that I need to optimize the db to make tables indexed.
I already tried to go to phpmyadmin, click on all tables, then, select OPTIMIZE table from drop-down list,.... but it does not help. The site is still running too slow to load. I am using high speed intenet.

Optimize A Select For Birthday
I need to select all the clients in my database that celebrate their birthday in the first/last half of the month. The table has an index on the birthday field. I used the next statement:

select buc from clientes where Month(FechaNacimiento)=5 and DAY(FechaNacimiento)<16

to obtain ~13,000 clients, but it takes a while to do it. The explain output says that it will use the index, but estimates the 10 million record scan.
Is there another way to express the WHERE clause to help MySQL optimize the search?

Optimize My Snort MYSQL DB
We are using SNORT IDS which connect to a MYSQL DB, the SNORT IDS has been running for long long time and it got a lot of data in MYSQL DB. Nowadays, the snort web interface has become extremely slow and my friend said it is bcoz there are too much data in MYSQL and MYSQL was not optimized for long time.
May I know how do I do some maintenance or optimize the MYSQL for it to run faster?
I used optimize table, and repair table command for all tables already but the web interface is still slow

Stop Optimize Table
While trying to shorten my files, I tried optimize table - later I
found that I need:

mysqlcheck -u root -p[password[ --all-databases --analyze --optimize
(thanks to Markus Popp).

But it gives this error:

mysqlcheck: Got error: 1044: Access denied for user 'root'@'localhost'
to database 'information_schema' when executing 'OPTIMIZE TABLE ... '

How can I stop this?
The optimise command seem to be in the way (and since I started it, my
computer has got pretty slow)

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:

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?