MYSQL :: Optimizing Slow Queries On Millions Of Records

Slow Queries, 4 Million Records, Need Educated Advice!
I have created an app a few years back to store some records in a DB.
According our calculations we were never to exceed 500,000 records in
the DB. Seems we were off by a decimal point or so.

I set up a FreeBSD box with MySQL three years back and it has been
filling up. One table has over 4,000,000 records. Yes, four million.

As a web developer and not a DBA, I have struggled to upkeep the server
the best I can. As of the last one million records the server has been
struggling to keep up with multiple requests and as you can imagine the
user base is growing too.

Hardware:
Dual Xeon 3.06 Ghz
4 GB ECC RAM
800GB RAID5 SATA array

Software:
FreeBSD 5.3
Apache 2.0
PHP5
MySQL 5.0.2

Basically I have to perform a search on one of two columns in this huge
table (10 columns, 4 million rows).

The table is MyISAM with a single primary key that is used largely for
updating row data.

Most records are ten digit numbers for one column and a ten digit
varchar for the other, but sometimes either column can be a series of
characters up to 100 chars long, so each column is set for
varchar(100).

The action performed is

SELECT count(*) FROM tableName WHERE col LIKE "%123%"

then...

SELECT * FROM tableName WHERE col LIKE "%123%" LIMIT 0,25

with "%123%" being any random string typed into a search window.

Would MySQL Handle Millions Of Records Tables
I already found on the MySQL web site that some users did have good
results with tables of some millions records.

But, what I want to do is store some hundreds millions records in a
table. 190 millions to start and maybe a lot more after. Does someone
already use MySQL with such a quantity of data ?

I was also asking myself how a simple SELECt query like this one below
would perform on such a table.

SELECT * FROM MyTable WHERE MyPrimaryKeyField = Value;

I think that with indexes this shoudl be fast despite the fact that
the table is huge.

An other kind of query i'd like to do is :

SELECT Count(*) FROM MyTable WHERE ABooleanField = True;

I expect this one to take several minutes, maybe hours. Is my guess
right or could it be lot faster/slower ?

Optimizing Indexes And Queries
I have a rather large table with about 5 million unique records. I am trying to make a query that lists the unique artists in the table.

there are a set of links A B C D E F etc that when they click on them displays the artists name that starts with the letter they clicked on.

I display them in a php page that displays 50 records at a time with a little index of links at the bottom where I can have them click on a page number and show the next or previous 50 records. There are approximately 178,610 unique artists in the table the table structure is as follows: Code:

Optimizing Range Queries
I have a database table with about 400,000 rows, and each has an x and y co-ordinate as a float. I am doing a query such as:

SELECT COUNT(*) FROM property WHERE x BETWEEN 57.003445 AND 58.134994 AND y BETWEEN 0.93572455 AND 1.42349875;

This tends to take around 800ms even with an index on (x,y), which is far too long. Any thoughts?

Optimizing LIKE Queries : E.g. Filename LIKE '%filename_part%'
I have implemented a ftp search engine which stores the names of all files present on the FTP servers in LAN and searches for them on request.

The database has roughly 15 lakh records.For searching filenames I need to use the following select query:

select * from filenames where filename like '%filename_part%' ;

This query takes around 10-15 seconds to be executed. Normal indexing seemed useless for this query.So I have tried to use full-text indexing.I retrieved results within fraction of second but it does not retrieve all possible results and retrives only partial results.I am using mysql-4.0.15 . Is it by any chance defect of this version and has been implemented better in higher versions.The match query I used was:

select * from filenames where match(filename) against('+filename_part%' in boolean mode);

Optimizing Queries Of Larger Databases
I'm trying to find out if there is a "better" way to do what I'm trying to do here.
I have a table with about 2 million entries in it. What i need to do is find entries with a given ID and return the result for a few of the fields. The way i'm doing it is simply:

select field1, field2 from table where id = 2000000;

This takes about a second to execute, which sounds great, except that the script i[m using it in could need to execute a few hundred of these queries within a time limit of about 5 minutes.

What i'm immagining the problem is, is that MySQL is starting at the first entry, and looking for the ID i've requested. Because there are so many entries it takes a while to get to the entry i've told it to return.

Can i make it only search within say.. the last 1000 entries? Or search starting at the end of the table?

Analyze Table - Optimizing Time-consuming Queries
I'm starting a website-counter service, so basically my site requires a lot of mysql activity.

Here are the two tables I use:

CREATE TABLE `traffictrack` (
`id` int(11) NOT NULL auto_increment,
`nr` int(11) NOT NULL default Ɔ',
`ip` int(10) unsigned NOT NULL default Ɔ',
`ndate` datetime NOT NULL default &#55612;&#57200;-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `ndate` (`ndate`)
)

CREATE TABLE `tracker` (
`nr` int(11) NOT NULL auto_increment,
`email` varchar(70) NOT NULL default '',
`password` varchar(220) NOT NULL default '',
`mdate` datetime NOT NULL default &#55612;&#57200;-00-00 00:00:00',
`ip` int(10) unsigned NOT NULL default Ɔ',
`image` varchar(200) NOT NULL default '',
`friendid` int(11) NOT NULL default Ɔ',
KEY `mdate` (`mdate`),
KEY `nr` (`nr`)
)
I have some queries that take up more than 1 second:

PHP

SELECT INET_NTOA(traffictrack.ip) as ip, tracker.image, UNIX_TIMESTAMP(traffictrack.ndate) as ndate, tracker.friendid, ( UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(traffictrack.ndate) ) as dif
FROM traffictrack
LEFT JOIN tracker USING(ip)
WHERE traffictrack.nr = ?'
AND
DATE_FORMAT(traffictrack.ndate, '%Y-%m-%d')=&#55614;&#57158;-07-23'
ORDER BY traffictrack.ndate DESC
LIMIT 20

also:

PHP

SELECT COUNT( traffictrack.ndate ) AS total, tracker.image, tracker.friendid, UNIX_TIMESTAMP(traffictrack.ndate) as timest
FROM traffictrack
LEFT JOIN tracker
USING ( ip )
WHERE traffictrack.nr = ?'
AND (
UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP( traffictrack.ndate )
) < 600
GROUP BY tracker.nr
LIMIT 0 , 30

I believe it's because my tables are badly indexed, I added an EXPLAIN before these two queries, they both ended up with:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE traffictrack ALL NULL NULL NULL NULL 34246 Using where; Using temporary; Using filesort
1 SIMPLE tracker ALL NULL NULL NULL NULL 20972
If someone knows how to speed up my queries, please help.

Slow Queries
I had a working web page that queried 20 tables and returned the data in the form of a table ... all of a sudden this stopped returning the results , and on investigation, up to ten tables, the query time is about 0.01 second ... but as the number of tables is increased to fifteen, the query time increases to 60 second .. and then gets too slow with nothing being returned at all.

A few days ago, this was all working fine. There have been no changes to the code on the web page ....
The version of mysql is 4.0.20, the server is a dual 1 MHz Xeon with 512 meg of RAM, running linux.

The tables have up to 15 fields each, and there is only simple text or numerical values in the fields.

Can anyone suggest what might have changed to suddenly slow down the query ?

What Causes Slow Queries
What causes periodic slow queries? I have checked my slow query logs and for some reason everyonce in awhile, a query thats never slow might be for example, one took 3 seconds to execute and every once in awhile a chat might take 10 seconds of cpu time while rest of the time 0.09...why is it it flexuates so much?

Slow Queries
I just got a new dual opteron system, with raid 01, 2gb ram, and fedora
core linux running 2.4.22-smp kernel. For some reason mysql is running
pathetically slow.

Queries that should take 2ms occasionally take up to
20 seconds. It isn't every time, but almost once per page. The problem
usually occurs with queries accessing the large tables (up to 1gb), but
not always. I've tried 2 versions I compiled myself (with flags
suggested in the readme), as well as the version off the mysql website.
All were 4.0.18, and all had the problem.

Slow Queries
I have set long query time to 2 seconds in my cnf file. Therefore all the queries taking more than 2 seconds are logged in the file called mysql-slow.log and the file size is 20 Mb.
Is there anyway to sort this file and find out the queries those are taking the most time so that I can optimize only those ones

Slow Queries!
I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form.
My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot?
Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?

Enabling -- Log - Slow - Queries
I want to enable --log-slow-queries of MySQL.

How do I do it without restarting the DB server?

Slow View Queries
I have a fairly complex view which, when you execute a SELECT * shows all info very quickly (0.24 seconds to return nearly 3000 rows). However, when I start throwing slightly more complex queries at it, things really slow down - an average query can take 40 seconds to execute.

Slow Queries And Optimization
I am hosting a site with an increasing number of members. Letely the site has become slower because of too many queries are being used.
Is there a way of asking the server which queries are used most often, as well as asking which queries take a long time to process so that I would know where to start my optimization process?

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?

Slow Select Using Count(distinct) In A Table Bigger Than 100000 Records
Recently I started using MYSQL in my enterprise. I made a table which has around 100000 records. The problems is that it is really slow.. Im trying to do a query in which I get the number of distinct users per day.

This is my query:

select date(startedDate) as mydate, count(distinct(Users)) as users from Mytable
group by mydate

It is really simple and it does it correctly but it takes one minute.. One minute is not too much time but i need to insert around 10 000 000 records and thats what worries me.....

1 Table For Millions Of Rows
I have one table which may record possibly millions of hits and i want to know if this is a good idea?

Complex Queries Versus Multiple Simple Queries
I am constructing a database to contain information about stories posted on my site. Information included will be things like title, author(s), genre(s), story codes, synopsis, etc.

I worked out that storing this information properly, so that it can all be searched on, could take as many as ten tables.

My question is this: Is a single complex query really better (more efficient for the server) than multiple simple queries? In other words, I may need the information for as many as 25 or even 50 stories for a single page.

Is it better to get all of the information out of a single, massive, complex query, or is it acceptable to get the information essentially one story at a time, which could mean 25 or 50 simple queries...?

Many Smaller Queries Vs. Large Comlicated Queries
I am wondering if any one can help me. I have a page that will run around 85 smaller queries but if i combine the queries it will go down by almost half. This page is a high traffic page and I don't a complicated query taking up mysql resources while it created a temp table and such. My question is this:

Is it better for mysql to run a lot of smaller queries (ex: simple selects with zero or one join, group by) or one larger complicated query with everything combined.

The thing i have to keep in mind is that the mysql selects are comming off the localhost that the web server is also running on so they share the same resources.

Optimizing My.cnf ?
I have got a server with 320MB ram. This is a free forum hosting server. Many people create own forum every day. So apache+mysql are very important for me.

Could you help me with optimizing my.cnf for my MySQL 4.1.14 server ?

Optimizing
I have a strange performace problem, I can't seem to understand.
It's the follownig query:

PHP Code:

select
tblrelations.ID
from tblcontracts
inner join tblrelations on (tblcontracts.relation_id=tblrelations.ID)
where (tblrelations.ID='106512' or tblrelations.mainrelationid='106512')
order by tblcontracts.ID desc

Need Help Optimizing ORDER BY
I cannot seem to properly optimize this statement used to show topics across all forums that have been recently replied to:
SELECT t.topic_id, t.topic_title, t.topic_sticky, t.topic_views, t.num_posts, f.forum_icon, f.forum_name, p.poster_name, p.post_time
FROM minibb_topics AS t
LEFT JOIN minibb_forums AS f ON t.forum_id = f.forum_id
INNER JOIN minibb_posts AS p ON p.post_id = t.topic_last_post_id
WHERE 5 >= forum_level_to_view && ( forum_site =0 || forum_site =1 )
ORDER BY t.topic_sticky DESC , t.topic_last_post_id DESC
LIMIT 20
I have isolated the issue to the ORDER BY clause. If I remove the ORDER BY clause, the query takes 1/10th of the time to execute (0.150 seconds as opposed to 0.015). Both ORDER BY fields are indexed. Also, if I limit the query to one forum such as, "WHERE f.forum_id = 1", the statement executes in 1/10th of the time.
This is the explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE f ALL PRIMARY,forum_level_to_view,forum_site NULL NULL NULL 12 Using where; Using temporary; Using filesort
1 SIMPLE t ref forum_id,topic_last_post_id forum_id 2 aanime_aanimedb.f.forum_id 651
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 aanime_aanimedb.t.topic_last_post_id 1
This is the table structure:
--
-- Table structure for table 'minibb_forums'
--

CREATE TABLE minibb_forums (
forum_id smallint(6) NOT NULL auto_increment,
forum_name varchar(150) NOT NULL default '',
forum_desc varchar(255) NOT NULL default '',
forum_order tinyint(2) NOT NULL default Ɔ',
forum_icon varchar(255) NOT NULL default 'default.gif',
forum_group varchar(30) NOT NULL default '',
forum_site tinyint(1) NOT NULL default Ɔ',
forum_level_to_view tinyint(2) unsigned NOT NULL default Ɔ',
forum_level_to_post tinyint(2) NOT NULL default Ɔ',
forum_level_to_topic tinyint(2) NOT NULL default Ɔ',
num_topics mediumint(8) unsigned NOT NULL default Ɔ',
num_posts mediumint(8) unsigned NOT NULL default Ɔ',
is_spam tinyint(1) NOT NULL default Ɔ',
PRIMARY KEY (forum_id),
KEY forum_order (forum_order),
KEY forum_level_to_topic (forum_level_to_topic),
KEY forum_level_to_view (forum_level_to_view),
KEY forum_site (forum_site)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table 'minibb_posts'
--

CREATE TABLE minibb_posts (
post_id int(11) NOT NULL auto_increment,
topic_id int(10) NOT NULL default Ƈ',
poster_name varchar(16) NOT NULL default 'Anonymous',
post_text text NOT NULL,
post_time datetime NOT NULL default &#55612;&#57200;-00-00 00:00:00',
poster_ip varchar(15) NOT NULL default '',
post_status tinyint(1) NOT NULL default Ɔ',
PRIMARY KEY (post_id),
KEY topic_id (topic_id),
KEY poster_name (poster_name)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table 'minibb_topics'
--

CREATE TABLE minibb_topics (
topic_id int(10) NOT NULL auto_increment,
topic_title varchar(100) NOT NULL default '',
topic_sticky tinyint(1) NOT NULL default Ɔ',
topic_poster_name varchar(16) NOT NULL default 'Anonymous',
topic_time datetime NOT NULL default &#55612;&#57200;-00-00 00:00:00',
topic_views mediumint(9) NOT NULL default Ɔ',
forum_id smallint(6) NOT NULL default Ƈ',
topic_status tinyint(1) NOT NULL default Ɔ',
topic_last_post_id int(10) NOT NULL default Ƈ',
num_posts mediumint(8) unsigned NOT NULL default Ɔ',
PRIMARY KEY (topic_id),
KEY forum_id (forum_id),
KEY topic_poster_name (topic_poster_name),
KEY topic_sticky (topic_sticky),
KEY topic_last_post_id (topic_last_post_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Optimizing A Query
I want to optimize a very expensive query, the query joins 5 tables, each having tens of thousands of records, always returns about 15000 matches and performs several calculations on each and every records (a datadiff, five time the / operator and two * operators), and to end with, all records (probably about 45000) are grouped and sorted resulting in 15000 records. This takes about 80 seconds, I want to get it down to max. 2 seconds :-D
All indeces are made, so I won't be able to gain anything there anymore. Does anybody has a clue as to how I could proceed here (maybe something with caching, materialized views and triggers?).

Optimizing ORDER BY
Let's say you have a table with 100,000 rows, and a field that can have an integer value from 0 to 100000000.

SELECT * FROM table ORDER BY field DESC LIMIT 15

Optimizing Another Query
I have another Query, that I don't manage to optimize. It's hard. I tried to first readout the ID's and then readout the contents, but this is not faster ...

Here is the Query:

SELECT o.id AS oid, o.time AS otime, o.disNaviReferer AS showR,
o.disNaviToplist AS showT, o.disNaviPartner AS showP, o.description AS odesc,
o.type, o.used, o.html,
c.id AS cid, c.name AS catname,
e.id AS eid, e.beschreibung AS linkdesc, e.flag, e.freigeschaltet AS etime,
e.previewPic as prev, e.hits
FROM own_content o
LEFT JOIN link_entries e
ON e.link = o.id
LEFT JOIN link_cats c
ON c.id = e.cat_id
WHERE o.used = 1
AND type = 4
ORDER BY
etime DESC
LIMIT 0, 30
Here ist the table structure:

--
-- Tabellenstruktur fÃ¼r Tabelle `link_cats`
--

DROP TABLE IF EXISTS `link_cats`;
CREATE TABLE `link_cats` (
`id` tinyint(2) unsigned NOT NULL auto_increment,
`name` varchar(100) collate latin1_general_ci NOT NULL default '',
`cflag` tinyint(2) NOT NULL default Ƈ',
PRIMARY KEY (`id`),
KEY `flag` (`cflag`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=13 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃ¼r Tabelle `link_entries`
--

DROP TABLE IF EXISTS `link_entries`;
CREATE TABLE `link_entries` (
`id` smallint(11) unsigned NOT NULL auto_increment,
`flag` tinyint(2) NOT NULL default '-1',
`eingetragen` int(10) unsigned NOT NULL default Ɔ',
`freigeschaltet` int(10) unsigned NOT NULL default Ɔ',
`cat_id` tinyint(2) unsigned NOT NULL default Ɔ',
`beschreibung` varchar(100) collate latin1_general_ci NOT NULL default '',
`link` varchar(255) collate latin1_general_ci NOT NULL default '',
`extern` tinyint(2) NOT NULL default Ƈ',
`previewPic` varchar(255) collate latin1_general_ci NOT NULL default '',
`hits` int(11) NOT NULL default Ɔ',
`voteAnzahl` smallint(10) unsigned NOT NULL default Ɔ',
`VoteSumme` mediumint(10) unsigned NOT NULL default Ɔ',
`poster` varchar(100) collate latin1_general_ci NOT NULL default '',
`poster_hp` varchar(100) collate latin1_general_ci NOT NULL default '',
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
`mail` varchar(100) collate latin1_general_ci NOT NULL default '',
PRIMARY KEY (`id`),
KEY `cat_id` (`cat_id`),
KEY `flag` (`flag`),
KEY `link` (`link`),
KEY `freigeschaltet` (`freigeschaltet`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6242 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃ¼r Tabelle `own_content`
--

DROP TABLE IF EXISTS `own_content`;
CREATE TABLE `own_content` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`html` text collate latin1_general_ci NOT NULL,
`description` varchar(255) collate latin1_general_ci NOT NULL default '',
`time` int(10) unsigned NOT NULL default Ɔ',
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
`used` tinyint(3) NOT NULL default Ɔ',
`last_used` int(10) unsigned NOT NULL default Ɔ',
`used_by_fl_id` smallint(5) unsigned NOT NULL default Ɔ',
`type` tinyint(2) NOT NULL default Ƈ',
`disNaviReferer` tinyint(2) NOT NULL default Ƈ',
`disNaviToplist` tinyint(2) NOT NULL default Ƈ',
`disNaviPartner` tinyint(2) NOT NULL default Ƈ',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=299 ;
Thank you very much! This query is very important for me to be optimized, because I execute it quite often with diferent data ...

Optimizing Mysql
i have Mysql 5.0 and i have huge amount of data in my database and it i think making my website very slow. i just want toknow how can i improve my database performance and what things i have to do and how?

Optimizing Mysql
Can someone explain to me what is the "Key Effiency" counter in mysql administrator. It basically is flatlined at 100% I have 257M entered in Key Buffer under Startup Variables. The machine has 2Gs of Ram. What would be an acceptable(recommended) amount of buffer size I could use.

Optimizing Imports
I need to have a web application be able to import large amounts of data
(400,000 rows of 10 columns). I know how to script it and have it running in
the background. However I want to know how I can optimize my insert
statements to try to speed things up.

Will it help if I insert multiple rows at a time? If so, is there a magic
number or range?

Optimizing Table
I have a db that's an overflow, meaning all it does is collect data (inserts only). I then have another server that checks for records that may exist there, and if it finds them, pulls them, processes them into other databases, then deletes the overflow record(s).

While this works great, the collection table, on every delete, stats getting overhead(seeing this via phpmyadmin). Over the course of a few hours this can add up to be several megs in size. It does not seem to be effecting performance, however I think it may in time.

My question is, would it hurt anything to run a OPTIMIZE TABLE 'collect' command after every delete?(seems crazy) (this system processes 10-15 records per second) Any other suggestions?

Optimizing Tables?
I was wondering if was possible by running some SQL query to tell if a table needs to be optimized or not. And if a table didn't need to be optimized, and I was to run the optimize table command, would it preduce and ill effects?

Optimizing And Reindexing
I just deleted a bunch of old records from a Mysql table. The table has 32 indexes. Initially the phpMyAdmin showed that the table was 190MB and now it is at 186MB. I expected the size to be much smaller but its not. Do I need to compact mysql? If so can anyone give me an idea? Also, do I need to reindex the table ?

Structure / Optimizing
I'm just after migrating my access database to mysql, the structure and data types are a bit confusing though, I have two tables each one has 22 fields, I'm not sure what data types to use though, I have attached a excel file with the data types, descriptions and examples of data for the two tables,

Optimizing FIND_IN_SET()
This is the case:

I have a table with a row that contains a big csv. I use FIND_IN_SET to query that csv.
This is causing performance issues. The type of the field is TEXT, but TEXT cannot be indexed. Yeah, to fulltext, but than the queries have to be adjusted to MATCH and don't have the working i ment.

My.cnf Optimizing MySQL
Just wondering if anybody has a suggestion for tweaking the my.cnf file to reflect my server set-up. I'm running Dual pentium III 550, 1G Ram, mirrored 8G SCSI HD.

Connections to the server are all through PHP about 90% SELECTS and 10% INSERTS/UPDATES.
It is a college sports site so I get some pretty heavy traffic on game days the rest of the time it's moderate to semi-heavy. Currently I'm using the default settings but get major slow downs on game days.

Optimizing The Queery
I was wondering are there technoques to optmize your queery? Like someone told me id you do search on certian fiels make thme index key. And in you SQL do not join too many tables etc .

Optimizing A RAID
Opteron workstation
8-disk Fibre Channel RAID setup (should run at about 800 MB/s)
I am strictly WRITING to a database consisting of ~10 tables which range in size from 100 MBs to 15+GB. Each INSERT statement contains 1 key and between 5 to 40 double values.
Some of my questions include:
1) Do I set my block size small (to accomodate the single INSERT writes) or big (to accomodate the large DBs)?
2) Is there a system tool that can be used to determine the average block size that I am writing?

Optimizing MySQL
I am using an MS Access front end -> MyODBC <- MySQL back end database configuration where the data is replaced every month. After emptying the old data, I would like to optimize the MySQL tables using VBA code, prior to importing the new data. Would anyone know how to implement this in VBA code, using ADO and ODBC

Optimizing Tables
I have big problem with mysql 4.0.18 . Because my database is growing
up, every night records that are older than some date are deleted. But
size of database on disk isn`t smaller after this operation. There are
some solutions to free space by OPTIMIZE TABLE command, but this
command when executes, does temporary copy of database. How can I free
memory and avoid doing temporary copy of database ( because I don`t
enough free space for this

Optimizing A SELECT Query
SELECT id, gameid, name, type FROM media ORDER BY date DESC LIMIT 5
SELECT * FROM games ORDER by wishlist DESC LIMIT 5'
SELECT * FROM games ORDER by mygames DESC LIMIT 5
SELECT mediaid, count(*) AS number FROM download WHERE date > &#55614;&#57159;-08-14 10:44:43' GROUP BY mediaid ORDER by number DESC LIMIT 5
SELECT * FROM news ORDER BY id DESC LIMIT 14'
SELECT gameid, count(*) AS number FROM views GROUP BY gameid ORDER by number DESC LIMIT 10
All of these queries I have determined are inefficient (using some tools) and they all have the same thing in common. They're all trying to find the "Top 5" or "Newest 10" or whatever from a table, but in doing so they are going through every single value and comparing them.

What other way is there to do this same idea of getting the "top 5" of a certain column values, without the SQL having to check every entry?

Help Optimizing UPDATE Query
I have 1 query in my script that is taking waaaaay too much time.

Is there a way that I can speed up or optimize this query?

Right now the query is taking about 600+ seconds to update 500 rows.

The Query

PHP

$update_from_sas_postmeta = "UPDATE wp_postmeta, sas_postmetaSET wp_postmeta.meta_value = sas_postmeta.meta_valueWHERE wp_postmeta.post_id = sas_postmeta.post_idAND wp_postmeta.meta_key = sas_postmeta.meta_key";$update_from_sas_postmeta_result = mysql_query($update_from_sas_postmeta) or die("MySQL ERROR (update_from_sas_postmeta_result): ".mysql_error());

The Tables

CREATE TABLE `wp_postmeta` (
`meta_id` bigint(20) NOT NULL auto_increment,
`post_id` bigint(20) NOT NULL default Ɔ',
`meta_key` varchar(255) default NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=294705 ;

CREATE TABLE `sas_postmeta` (
`meta_id` bigint(20) NOT NULL auto_increment,
`post_id` bigint(20) NOT NULL default Ɔ',
`meta_key` varchar(255) default NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Sample Data
2, 2, 'page_template', 'default'
3, 3, 'merchant_id', ?'
4, 3, 'merchant', 'SomethingSomething'
5, 3, 'link', 'http://www.website.com/link.html'
6, 3, 'thumbnail', 'http://www.website.com/thumbnail.jpg'

More Details
When the UPDATE query begins to run, the sas_postmeta table will have 500 records in it and the wp_postmeta table will have about 250,000 records in it.

Optimizing Multiple UPDATEs
Basically what I want to do comes down to this:

UPDATE players
SET ranking = 1
WHERE id = 45;

UPDATE players
SET ranking = 2
WHERE id = 30;

UPDATE players
SET ranking = 3
WHERE id = 8;

UPDATE players
SET ranking = 4
WHERE id = 97;

UPDATE players
SET ranking = 5
WHERE id = 12;
I do have a php array containing each player ID with its (new) ranking. However looping through all the players to update their rankings (as shown above) does not seem like a scalable solution to me.

Optimizing A Left Join
SELECT a.x FROM a LEFT JOIN b ON a.fk = b.pk ORDER BY a.d DESC LIMIT 10;

If I were MySQL, I would perform the above query by first ordering table a by a.d, then limiting by 10, then joining with the table b. However it seems that MySQL is doing something much much more difficult, since running this query takes 2 minutes. Here is the EXPLAIN from it, but I'm not quite sure how to read it.

+--------------+------+---------------+------+---------+------+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------------+------+---------------+------+---------+------+------+---------------------------------+
| a | ALL | NULL | NULL | NULL | NULL | 3913 | Using temporary; Using filesort |
| b | ALL | NULL | NULL | NULL | NULL | 9471 | |
+--------------+------+---------------+------+---------+------+------+---------------------------------+
Can you see from this EXPLAIN whether MySQL is doing 3913*9471 operations ? I would think that sorting the 3913 record table + doing 10 seeks on the 9471 record table should be enough. I could easily force MySQL to "do the right" thing by just emulating this join with PHP code,

Optimizing Document Database
I am creating a document database that will be used as the backend for a website. The organization itself is cross-relational in nature, the documents are added to certain "areas" and these areas can be combined as filters to give the users precisely what they are looking for. The design works great for the current active sample (about 1100 documents). However, I began testing with a larger sample (about 25,000 documents) and the query runs way too slowly.

I actually need the database to perform operations on entries for approximately 2 MILLION documents, so the current state is unacceptable. I've placed indices on all of the tables being used, but it's still not producing the kind of speed I want. I believe the problem is in my query, but very well may be in the structure.

Here are the three tables, documents, areas, and area_assignments. Documents and areas are related through the foreign key table area_assignments, and both have a one to many relationship with this table. Code:

Optimizing MySQL For Performance
I am going to be running mySQL on a RAID5 array and am wondering if I
need to worry about splitting up the data and log files on to seperate
channels on the RAID array.

I have heard differing arguments. Some people say that you can install
SQL on the same logial drive as long as you are running RAID5 and not
worry about performance issues.

Others say that you need to split everything up even if you are
running RAID5.

Do I need to split it up in to 2 channels and 2 logical drives or is
one drive OK?

Optimizing MySQL Databases
I bet you're about to say "Well, use the OPTIMIZE function." See, the problem is that the OPTIMIZE function isn't what I'm looking for. In all honesty, I don't even know what the OPTIMIZE function can do, but I know what it can't. Here's an example of the problem

ID | Bytes | Field | ...
---+-------+-------+
1 | 0001 | ????? | (Ignore the data)
2 | 0010 | ????? |
3 | 0011 | ????? |
5 | 0101 | ????? | <-- Here's the problem
6 | 0110 | ????? |

Well, I think you get the idea. The problem is the gap in between the auto_increment value ID from row 3 to 5. What I'm wanting it do is 'compress' the rows and get rid of that little increment gap, like this:

ID | Bytes | Field | ...
---+-------+-------+
1 | 0001 | ????? |
2 | 0010 | ????? |
3 | 0011 | ????? |
4 | 0101 | ????? | <-- Here's the fixed problem
5 | 0110 | ????? |

So, would anyone know any MySQL or PHP to help this out?