MYSQL :: How To Optimize Mass Data Queries

Weekly Mass Data Relocation
I've got a table located on my website and localhost. The table
maintains a size of about 200MB. The table holds a running 4 weeks of
data (50MB/week). I have a winapp that updates my localhost db each
week and currently, I run a backup on the table using the MySql
Administrator... then I run restore on my website's mysql server.
This deletes the whole table and reinserts each row. This isn't needed
and is a long process.

Is there a way to select a range of rows from the localhost table and
"migrate" those rows to my website's mysql server without following the
process I just described?

Export Table Data For Mass Labels
My goal is generate large lists of mailing labels that are based on:

Name
Address
City
State
Zipcode

I created the table and fields, implemented a submission form, and incorporated a link for an admin to download the data into Excel but thats as far as I got.

Is there a better or more thorough approach? I believe that in the end, I want to download this into Word so I can do a mail merge.

Less Queries Or Less Data
is it better / faster to have rows that hold more data, (ie putting in a varchar username instead of an integer userID to save having to query another table to get the username)
or is it better the other way around? (of course unless the username could be changing which in this application the username cannot be changed)

Backslash In Data/query And Like Queries
mySQL 4.0.20a WIN32

Need help with backslash syntax/like queries..

Query1:

select * from dbtable1 where Filespec like 'http://www.10291.com/%'
returns : http://www.10921.com/sikhnet/register.nsf/ram/radioG2/$File

Query2:

select * from dbtable1 where Filespec
='http://www.10921.com/sikhnet/register.nsf/ram/radioG2/$File'
returns 0 records

Query3:

select * from dbtable1 where Filespec like '%\%';
returns 0 records

What am I missing here....?

Front-end To Create Reports And Queries, And To Load Data Into Thedatabase
I've been trying to find a freeware front end to the MySQL database to
create reports based on queries, and some sort of front-end which I can
format for other users to use to load data into the db. Is there anything
out there for those functions?

Front-end To Create Reports And Queries, And To Load Data Into The Database
I've been trying to find a freeware front end to the MySQL database to
create reports based on queries, and some sort of front-end which I can
format for other users to use to load data into the db. Is there anything
out there for those functions?

Mass Installation
I need to install the same database on 40 accounts, all with the same database name, and same table in it... any way of doing this? I've got access to WHM.

Mass SQL DELETE
managed to get put in charge of a forum that uses it extensivly.
Currently, the forum is getting bogged down by the 1.5 million posts so I have archived and wish to delete them.
I have tried a lot of different variations on DELETE, all of which time out. I am left SELECTing the TOP 20000 and then highlighting and deleting them.
I would like to find a quicker, more efficiant way of deleting 1 million rows from a single table (tblThread) that won't timeout.

Mass Insertion
I have a user event manager where they would like to be able to insert recurring events. This is to say that the user specifies that an event would recur (for example) every week at the same time, and then the script would insert all these seperate events. Is there anyway to use a single query to do something that inserts a certain amount of data into the proper records and sets the dates correctly? Or do I need to recursively contruct a query to insert all the records at the code level?

Mass Insert
I have written an application in delphi which performs a mass insert (1000+ lines). However, I am inserting each line into the table one-by-one, e.g.

INSERT INTO invoice_data VALUES (...)

Is there any way I can insert all 1000 in one go? Is this a better way to perform a mass insert?

Help With Mass Update Query
I have a field in my table (1000 records) called company_number. I've just been informed that all records that start with "1" should actually start with "0". So, for a record where compnay_number = 1000400 the number should actually be 01000400. Can anyone suggest how I would approach updating each record via one SQL statement?

SELECT *
FROM `myTable`
WHERE `company_number` LIKE '%100%'
... then what?

Another Way To Mass-upload Than LOAD ??
My ISP gives me phpMyAdmin to get in contact with my MySQL-db
but sadly enough the "IMPORT TEXTFILE TO TABLE" do not work
(gives errormessage "#1045 access denied" and I do not know if it will be fixed,
everything else seems to work allright)
so I'm looking for an other (easy) way to upload 3000 + more records.

Column Alias On Mass
Is possible to retrieve all columns and alias them all at once. Eg, normally
you would write

select * from products which would return

id | name | price
-----------------------
1 | Test | 14.00

but I want to be able to say

select p.* from products p, so that it returns the columns as such

p.id | p.name | p.price

Obviously I can do this manually as such

select p.id, p.name, p.price from products p ....

But that would take a lot of big queries as some of my tables have 50
columns.

Mass Left Join
I would like to join a lot of tables but they all share the same structure. All of them have the same id column. I found a solution, but I'm almost sure there is a more efficient way. My solution is like this:

select id,c1,c2,c3,c4,...,c29 from t1 left join t2 using (id) left join t3 using (id) left join t4 using (id) ...

Mass Updates To A Table
I've got a web-based form that will allow people to update a database
that is displayed as a table. Let's say that there are several columns
across titled Red, Blue, Green... and several rows titled 01, 02, 03...
In each cell, they can fill in some simple text data.

The only way that I can think of to update the database with this
information is using two imbedded loops in Perl:

pseudocode:
for($color=1;$color<=10;++$color) {
for($number=1;$number<=10;++$number) {
update table xyz set value=$value[$color][$number]
where color=$color and number=$number;

This would mean that it would have to execute 100 update statements.
Perhaps this is no big deal and I shouldn't worry about it, but since
I'm relatively new to sql, I'm guessing that there's probably a more
streamlined method to do this.

BTW - I've seen a lot of people asking questions that were vaguely
similar to mine in this group, and they've been told to go to the php
group, even though I didn't see any php-specific code in their
questions. I don't use php and don't have it installed on my server.

Sending Out Mass Email
I have about 10,000 members with emails stored in a database and would like to be able to send emails out to each of them. I'm not sure at all how it would be done though.
I tried a for loop using MySQL queries but none of it worked.
Does anyone have any suggestions on how to do this? I would love to send out a newsletter to my users.

Mass Update Tool
we have so many mass upload tools, load from the file, load from other database built in.
How about mass update tools?
Say I have a table have 3000 products in it.
I want to update the price of these 3000 products with a new table (or excel sheet) of new price by matching the product code (key).Is there a tool for that?

Mass Replace Text In String
I want to replace a few letters in a string throughout my database column and using:

UPDATE products SET pID = REPLACE (pID, "BSC", "BRC")
Did not do the job.

The Strings are variations of 33-BSCE7_14_16 all having BSC that needs to be changed to BRC

Is there a way to use something like "*BSC*" to have it found and replaced?

Mass Converting Database Entries
I have an phpbb board and some old MOD changed some entries that it shouldnt. I need to change 1300 entries to default value. How can i do that without having to change one by one manually?

Mass Rename Of Table Prefix
Is it possible to mass rename alot of tables with the prefix bak_ to x_ ?

Mass Replace Characters With MySql
I want to mass replace three htlm character sets in one field in all records in my db. The field name is Subtopic and the db name is ViewTest. I want to replace with in all records. Is there a way to to this?

Mass Replacing Info In Tables
fairly new to mysql and wondering is there any way to mass replace info in one of my fields. I have a town based member website and they enter their town which goes into the town field of my member table. Problem is the town has officially changed it's name and I am looking for a way to just mass replace everyone's town name in the field. For example all users are from Billington and I want to mass replace every members town field with New Billington the new town name. I understand that I can open up each members profile and type in the new town but I have about 1000 members and was looking for an easier way.

Want To Speed Up Mass INSERTS When There Is Processing Of The Insert_id()...
I am importing data from a large plaintext data set and it is going very slowly. I need to acquire and process the insert_id from each record so I cannot use INSERT DELAYED or LOAD DATA INFILE, for which I could speed things up by increasing the bulk_insert_buffer size. I'm not sure if there is really anything that can be done, but I thought I'd ask.

The looping process is like this.

1. insert row into table 1
2. get insert id
3. one or more inserts on table 2 using the insert_id

Repeat 2000 times or so per page load, because PHP just dies if the file isn't processed in small result sets. It is partly slow because table 1 has several indexes and table 2 has one. The indexes cannot just be added at the end either. In the end there will be +30million rows in table 1 and probably more than 100million in table 2 so if I tried to add the indexes at the end php would die off long before the script could complete.

Mass Update Of All Database,table,columns For Collation?
Has anyone ever accomplished a mass update off all databases, tables and
columns for collation?

If I try to change the collation/character set for the mysql daemon it
breaks all of our queries because they conflict with the swedish default
that they have been created with. The problem is we have about 90 databases
with hundreds of tables. Is there an easy way to update all of these at once
and that would allow me to start with the correct default at startup.

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.

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:

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

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.