MYSQL :: Optimize Problem

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;

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:

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

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

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?

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)

Optimize UPDATE Query With Subquery
Users(users_id,...,pictures_average_grade) - 5,000 rows
Pictures(..,average_grade,users_fk) - 50,000 rows

One User can have many pictures. Each picture have average_grade which is being continually changed. Once a day i would like to compute for each user average grade of his pictures and update column. users.pictures_average_grade. I wrote the following query:
UPDATE users AS u SET u.pictures_average_grade= (SELECT AVG(average_grade) FROM pictures WHERE users_fk=u.users_id).

The problem is that the query is being processing very long (after 15 minutes i resing and stop the query).

How To Optimize Mass Data Queries
I am trying to optimize the speed of a query that calculates bias/mean absolute error of forecasts against observations. The forecasts are issued hourly, and make hourly forecasts to the 48th hour. Furthermore, there are about 20 different forecast methods. On top of that, there are 12 different sites. What this adds up to is a really large database. The forecast table has 400K rows, while the observation table as 25K. In order to calculate the above statistics for a given time period, one must pull a lot of data based on the date range. The worst case scenario is that 60,000 rows must be pulled from the forecast table (all stations, methods, and 30 days of fcsts). The problem doesn't seem to be the speed of the query (<.1 sec) as the query is only on the Primary Key (time,station,method), but rather the throughput of the data. I am using PHP to handle the data and present it to the end user. I would estimate that about 15Mb has to go through the pipe before it's processed (ie. converted to an array, and compared to the observations - note, an observation would correspond to say, f010 of a forecast 10 hours earlier, so a parallel join is impossible). The machine I am using is forced to swap in order to execute the script, but it is an old machine (500Mhz processor, 128Mb Ram) that's used for development.

Are there any ways of possibly compressing the inbound data, especially while it is being processed? Right now, I create the big 4-D array before I do the statistics (which are grouped by method), would it help to split up the data feed by method? PHP seems to be most sluggish during the query mysql_query(...), rather than the mysql_fetch_array() looping.

How To Optimize MySQL 4.0.24-log On 10 Mbps Pnet
i hv a database driven portal which derives all the information & data from a database server which is on a different server and web server & database server are connected on 10mbps PNET.(Private Network)

i need to know by what all means and methodologies can i optimize mysql and make my site faster

this site is totally in LAMP environment:

php=4.3.2
mysql = 4.0.24
Apache=2.0....
RHEL3

Optimize Table And Forced Sorting....
I've optimised a table but I'd like a table to be optimized and sorted according to the primary key in asc What should I do?

ANALYZE, OPTIMIZE, CHECK, REPAIR
when to use the ANALYZE, OPTIMIZE, CHECK, REPAIR commands. From what I can tell, ANALYZE and OPTIMIZE are for making the table optimal to increase performance. CHECK and REPAIR are used to find & fix corruptions. ANAYLZE takes a lot less time to run than CHECK.

The questions I have are:

1. What is the general rule of thumb on regular (daily) maintenance of a MySQL DB?

2. Will ANALYZE report table corruptions? If so, I don't even need to call CHECK, I can directly go
do REPAIR?

3. If the anwer to #2 is no, meaning I have to run CHECK TABLE, should I separate the optimization exercises from the find-corruption-and-repair routines?

Optimize MySQL Server Connection
I'm looking to make my site faster, and the first place I want to tackle is my MySQL server. I have no local or shell access to this server, so i'm not sure what I can do, essentially i have access via written code in PHP and phpMyAdmin.
Basically, the first thing i want to do is run a benchmark to see where the server is now, and what commands are slower then others, and the like... I found This. But it is if you run your own server.

How To Optimize Frequency Of Disc Write Operations
I need to optimize frequency of disc write operations for inserted or updated data on MyISAM tables. How can I tune MySQL for large records block disc flushing instead of record-by-record flushing by default?

Some DBMS parameters have effect on disc flushing

("flush", "flush_time", "delay_key_write"), but any of these parameters does not allow to reduce intensity of disc write operations. MySQL 5.0 (Win32) is used.

Reducing File Size (optimize Table)
I have had a DB where I added a huge amount of test data. Now, after having it processed, the DB contains only the date it will normally need. The point is that I can free up more than 1 GB, which will never be used again...

I was told that "Optimize table my_table" should do the trick. I know that it orders tables etc... and should be able to free up unused space.
Well, if it does, then it does not work for me.

How can I reclaim my 1 GB of disk space?

The Most Optimize Way To Store And Seach Chinese Character In MySQL 4.1
Currently i store the chinese character in unicode decimal format
(&#<number>;). In this way, I can display and search the chinese
character correctly. However, this will take more storage space. Is
this the most optimize way?

Check Table And Repair Versus Optimize Table
I am confused about the difference between running a CHECK table and REPAIR(if necessary) versus running an OPTIMIZE table. It's not very clear in the documentation, but seems that OPTIMIZE table does a check and repair in addition to the other functions performed by optimize.

I have inherited some legacy code that does a check and repair and optionally follows it with an optimize. That seems redundant to me and I would like to streamline the code to make it an "either-or" selection.