MYSQL :: Check Table And Repair Versus Optimize Table

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 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.

Repair Table
Is there a way to issue a "REPAIR TABLE table_name" command to all
tables at once? Something like "REPAIR TABLE *"

REPAIR TABLE
Can REPAIR TABLE be used for rebuilding indexes on an InnoDB table? The only documentation I have seen is for MyIsam. There are 30 million records which has taken me 9 days to populate. I've made a backup but want to make sure this will work before I try it.

Can't Repair A Table
It seems I have a corrupt table and have tried the REPAIR TABLE command but during the operation I get this return.
mysql> REPAIR TABLE users;
+-----------+--------+----------+-------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+--------+----------+-------------------------------------------+
| sdc.users | repair | error | Can't open file: 'users.MYD'. (errno: -1) |
+-----------+--------+----------+-------------------------------------------+
1 row in set (0.00 sec)

Repair Corrupt Table
I am using xoops (a CMS) and MySQL on a website with not so much traffic. I am using "phpMyAdmin - 2.8.0.3-Debian-1" with "MySQL - 5.0.21-Debian_3-log" on a Debian unstable.
Three tables have gotten corrupt recently and won't let themselves be repaired. When I use repair it says status OK, but if I try to delete a record or if I analyze the table again, it says that the table was reported as crashed and that it needed a repair. The only useful message I get, if I analyze the table is about wrong key at page 111616.

Indexing Table Forces Repair By Sorting
I have a problem whereby i am loading a large table into MySQL - approx 3 million rows

I then create indexes on most of the columns including normal and full text indexes on certain columns that I need to sort and search.

The indexing operation takes an age (about 7-8 mins) on each column, whether it is a normal or fulltext index. Example:

mysql> show full processlist;
| Id | User | Host | db | Command | Time | State | Info
|
| 1196 | root | PAULS:2078 | registries | Query | 457 | Repair by sorting | CREATE INDEX VALNAMEidx ON FULLTAB_113 (VALNAME
(64)) |
5 rows in set (0.01 sec)

I have tried SET MYISAM_SORT_BUFFER_SIZE=1024000000 - this has no affect

I have also tried creating the indexes when I create the table but I get the same thing.

The tables are populated by load data local infile and no additional rows are added once populated I am running MySQL 5.0.11 on a dual core pentium 2.4Ghz with 2GB RAM Any ideas how I can speed this up?

Mysqlcheck: Table Is Marked As Crashed And Last Repair Failed
I'm using the command "mysqlcheck --all-databases --auto-repair --silent"
and I see these warnings and errors (listed below) on a daily occurrence.
What could cause these indexes to be out of order? Another thing I don't
understand is why an increase in the number rows constitutes a warning?

Hoping to here from MySQL users who have came across this. I'm using the
MyISAM storage engine and MySQL 4.1.11 on Red Hat Enterprise Linux.

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 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?

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 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?

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?

Check If A Table Has Been Updated
I'd like to find a way to check if any modifications has been made to a table. I'm using mysql 4.1. I'm trying to find the least resource intensive method of doing this.

Check Table For An Entry?
I'm currently working on some rudamentry administration, namely an IP ban list. I want to make it so that if the user trys to ban an IP that is already in the list, the entry will be removed instead. what would be the best way to go about this?

Check If Table Exsists
Am creating an install.php page to create the tables for a database. After running the code I want to make sure the tables were created, how do you check is a table exsists in mysql?

Check If A Table Exists
is there any way to check if a table exists in mysql? i am using a graph program in vb, but if the table it is trying to graph doesnt exist it breaks down! i need some sort of check so as the offending table will not be added to the query if it doesnt exist?

Check Empty Table In Where Clause?
the following where clause (expurgated) suits my needs right up until the table emailman is empty (which it frequently is) and then it returns an empty set (which is bad;-)
this is actually an AND emailman... but don't I think the rest is relevant.

WHERE emailman.related_id != contacts.id How can I modify this to allow for emailman to be empty? Something like...

AND (emailman.related_id != contacts.id || NOT EXISTS (emailman.id) )

How To Check Empty Columns In A Table
If i press a button, i have to check if certain columns(fields) in a table are empty.If they are empty, i want to send an e-mail to the e-mail address which is already in this table.If there are no empty columns, then i want to do some calculation using another table.

How Do I Check If An Table Index Exists Via PHP ?
I was wondering if it's possible to verify if an table index already exists or not? As it's possible to add indexes multiple times.

Check If Row Exists In Anotehr Table
Is there a way to check if a certain value exists in another table without using something like

SELEC * FROM tbl WHERE someid = 'somevalue';

and seeing if it returns false

I ask this because I'm running a kind of search query where it selects all records from a table where this field is this, that field is that, etc.

So for example

SELECT * FROM members WHERE id='this' || name='that' || height='67' || etc..etc...

now one of the attributes is a zip code.

In another table, i just ahve a list of like a billion zip codes. Ifi want to check if the zip code exists in this list in this other table, how would i do so while preservingn the above format in one call?

Check Table Wildcard Syntax?
I need to do CHECK TABLE on a lot of tables (actually on all tables in
several databases), and hoped to do something like

mysql> CHECK TABLE reports.*

which ddn't work. The only thing that seems to work is using
cut'n'paste (or some scritpting) to get a comme seperated list. Is
there really no "smart" way of doing this?

Check Table Command - When To Use Which Checking Option
I'm trying to decide which check table option to use for a DB-health-check script for my product. This script is to be run periodically to find and repair damaged tables. My product uses only MySQL, i.e. it does not interact with another DB. I need to find the check table option that would find damaged tables with the highest possible performance.

The 13.5.2.3 CHECK TABLE Syntax documentation states the following:
MEDIUM Scan rows to verify that deleted links are okay. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.

What are they referring to with the word "links"? Are they talking about links to MS Access tables? How do I know if my tables have links? Does the MEDIUM option scan rows only to check deleted links? If so, if I don' t have "deleted links", should I not use this option? By running in MEDIUM mode, will the tables be locked while "check table" is working?

For the other options - CHANGED, FAST, QUICK - will "check table" lock the tables? What access do I have to the tables while check table is running?

Table Design Question? House Table, Owner Table, Code Violations Table - Best Way?
Given the tables:

HOUSE
house_ID
address

OWNER
owner_ID
name
telephone...

HOUSE_OWNER_JOIN
?

CODE_VIOLATION_HISTORY
house_ID
violation_ID
violationStatement
...

My goal is to be able to track code violations of the house PER owner.

For example, I need to display a page that shows the current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like:

House 1009283
Address
Past history (link to the following)

House History
2001-01-04 Owner: John Smith Code Violation: Gutter issue
1999-06-01 Owner: John Smith Code Violation: Faulty Steps
1998-03-02 Owner: Sam Spade Code Violation: Driveway carcks
1990-01-12 Owner: Keith Sledge Code Violation: Grass untidy

For the design of the HOUSE_OWNER_JOIN table, I thought of two ways I could go on this and this is where I need your help.

Option 1:
Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way:

HOUSE_OWNER_JOIN
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

then I could look up all code violations by date and associate them with their rightful owner.

==================================================
Option 2:
Have the HOUSE_OWNER_JOIN table be the primary keeper of identity data by adding a new primary key and changing the CODE_VIOLATION_HISTORY table to reference that table by chaning the referencing key from house_ID to house_owner_ID:

HOUSE_OWNER_JOIN
house_owner_ID
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

CODE_VIOLATION_HISTORY
house_owner_ID
violationStatement
...

Create Table Error. #1064 - You Have An Error In Your SQL Syntax; Check The Manual That Corresponds
I have been trying to create two tables with the SQL below. I have the SQL in file and tried to import it to PhpMysqlAdmin in my control panel. The wierd thing is that the first table gets created while the second one is not created and error is thrown instead. The error message is

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ....

Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP

$gettray = mysql_query("SELECT trailers.title,
trailers.link,
trailers.movie,
movie.title AS mtitle
FROM trailers,movie
WHERE trailers.movie=movie.word
ORDER BY trailerid
DESC LIMIT 6",$connm);

It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table.

I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet.

If the row does not exist in 'movies', the program than uses the entire trailer title like so

PHP

if($ttray['mtitle']) {
$newttitle = explode("-",$ttray['title']);
$newttitle = array_reverse($newttitle);
$ttitle = $newttitle[0];
$ttitle = $ttray['mtitle'] ."- ". $ttitle;
} else {
$ttitle = $ttray['title'];
}

Thanks
Ryan

How To Check For Date/time Ranges Within Record (check For Schedule Conflicts)
You want to check scheduling conflicts and you have a record like:

appointments(table):
apptID
beginningDate
endingDate
beginningTime
endingTime

It's easy enough to check if a time is within that record. Say you want to
check if 8:00am to 10:00am is available, you would use this:

SELECT apptID
FROM appointments
WHERE (beginningDate = '2006-01-19' OR endingDate = '2006-01-19')
AND ('08:00:00' BETWEEN beginningTime AND endingTime
OR '10:00:00' BETWEEN beginningTime AND endingTime)

BUT, what if you have an all-day appointment (8:00am to 5:00pm) and there
exists an appointment already scheduled from 10:00am to 11:00am. The above
query would not find it.

Another question is what if the appointment is more than two days. Say, it's
from Monday - Wednesday from 8am to 5pm. The above query would not
successfully catch it if you wanted to schedule an appointment on Tuesday.

(I might be able to generate a date range using PHP, don't know if that's
the best way)

MySql Versus
I run often large queries in ms access. In short this is a selection on a large sourcefile (1.500.000 records). On this selection he does a lot of enrichtment manipulations with functions (eg. make the legal form of an enterprise to a standard = ltd or limited company should become LTD).
This is a time consuming action and I wonder If I should use mysql database (online or local) can this speed up the operations. In other words, in something known, can somthing be said about the MySQL performance

JOIN Versus Subqueries
I'm always looking to expand my skills and knowledge, and as such I'm currently working on rewriting several of my old apps that make atrocious abuses of MySQL queries. I've pounded out several of them into single statements using subqueries, but on this forum I've noticed very little subquerying and a lot of JOINing.

So my question is, what are the benefits/costs of subqueries vs JOINs? When would you use one over the other, and why? If this is too general to answer here, could someone point me toward a tutorial or somesuch that could edumecate me?

Version 5 Versus 4.0.24 Compatability
Started as a 5.0 user and developed DB n 5.0 and a propossed Hosting Service said I should use the same version on their DB Server (4.0.24).

What anticipated problems could I experience if I upload tables from 5.0 and are their differences in the data files created in 5.0 ?

Guess the question is would 5.0 files and tables be backward compatible?

If I remove 5.0 and install older 4.0.24 do I have to redo the tables?

MyISAM Versus Innodb
Now, each blog is stored in a separate row, in a table called blog_table.

This single table would be the most active one and would grow to large size in the coming years.

What is the most advisable engine for this table - Innodb or MyISAM ?

Also, I do need FULLTEXT search, and since Innodb doesn't support FULLTEXT indexes, are there 3rd party tools that could do the indexing.

InnoDB Versus MyISAM
I have a MySQL server (version 4.1.9-nt via TCP/IP) with a few databases. One of my databases is quite small. It only has three tables of less than 500 rows, and it is less than half of a kilobyte when backed up. All the tables in this database are InnoDB because that was the default in the MySQL Administrator.

Ninety percent of the data lies within one column inside one table. It is this column that I want users to be able to search and return results somewhat similar to the way a popular search engine would. My research has brought me to the idea of using FullTextSearch which apparently only works in a MyISAM database (correct me if I'm wrong). So I've thought about changing this one table in my database to MyISAM but I have no idea what that change means to me in terms of support, maintenance, or headaches. So my first question is, what are the differences between InnoDB and MyISAM? Are they hugely different or are there only minor subtleties.

Secondly, this brings up the question of backups and restores. Right now I have an InnoDB backup that is scheduled nightly. So my second question is how do I backup/restore a database that contains InnoDB and MyISAM tables? Or should I change all my tables to MyISAM? I really like how the InnoDB backup is a simple unencrypted flat-file. This keeps me sleeping soundly at night. Will the other backup methods be as simple?

Create Virtual Table During Query Or Load Table To Memory From Text File
I have access to my application database via a secure link, so I cannot link a local database to it, only run queries on the remote server and download the result.

The database is missing some useful information, and I cannot add a table at this stage. I would like to achieve the result by creating a virtual table or the like. ...

Delete Rows From Table A, Which Dont Exist In Table B (base On Column X)
I have 2 tables identically structured.

A & B

Table A, has column: Product (product code) as primary key

Table B doesn't.

Apart from that they have the exact same fields.

There's also a column: supplier

I want to

Delete * from table A, where does not exist in B (based on column: Product) & where supplier = apples

So to elaborate. Table A is my main table, but it now contains outdated products from supplier apple. Table B has the latest list of products from supplier apple. So I want to remove old products from A that supplier apple no longer makes.

mysql version 4.0.27

Inserting Multiple Items (# Unknown) Into A Table Using Primary Id From Master Table
In my database I have a master table for products using a auto-num id column. One product may have several purchase options (items) which have to be entered into another table.

To enter a new product and items I use a form that gathers the information for the master product and one item. This information is then inserted into the database. For the items I use mysql_insert_id() to get the id into the items table.

My challenge is getting this same id for additional items. Once the user has input the initial product and first item they hit a submit button to call a form for adding any additional items, and this is repeated until all items for that product are entered.

The new items are inserted into the database okay, but always with an id of 0. The mysql_insert_id() no longer works. Is there a way to pass this id from the previous insertion to the new one by passing a variable? or echoing input type"hidden"?

Or even better yet, Im wondering if I can ask the user at the time of entering information for the product how many items that particular product has and then bring up that many form fields to enter multiple items? Can this be done using something like an incremented loop to call form fields?

Multi Table Query, Grab More Than Single Row From Secondary Table
I have a query like so:

PHP Code:

$selectt = mysql_query("SELECT trailers.trailer_id,
trailers.trailer_title,
trailers.description, trailers.file_url,
trailers.runtime, trailers.views,
trailers.rating, trailers.tt, trailers.keyw, trailers.date,
tags.snub,
films.film_title, films.studio, films.keyw, films.image
FROM films,tags,trailers
WHERE trailers.trailer_id=tags.trailer_id
AND trailers.film_id=films.film_id
ORDER BY trailers.trailer_id DESC LIMIT 8",$dbh);

I know this is pretty sloppy, I have multiple 'tags' per trailer ID, and I was hoping to grab all of them in a single query; maybe into an array? So that it shows the row, and then all the tags that belong to it.

Possible?

ALTER TABLE Deleted The Rows - Error: Table Is Full
Today when I tried to insert data in a table i received the error that 'table is full'. On SHOW TABLE STATUS, I noticed the size of table is grown to 4GB (rows 5359211).

mysql> SHOW TABLE STATUS LIKE 'messages' G
*************************** 1. row ***************************
Name: messages
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 5359211
Avg_row_length: 801
Data_length: 4294967288
Max_data_length: 4294967295
Index_length: 45783040
Data_free: 0
Auto_increment: 5406252
Create_time: 2007-04-20 18:26:38
Update_time: 2007-08-22 09:55:22
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Here is the table structure.

CREATE TABLE `messages` (
`id` int(11) NOT NULL auto_increment,
`subject` varchar(255) NOT NULL default '',
`message` text NOT NULL,
`attachment_path` varchar(255) default NULL,
`new` tinyint(4) NOT NULL default Ɔ',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

On searching I found the this link.
http://dev.mysql.com/doc/refman/4.1/en/full-table.html

According to manual i executed the following query

MySQL
ALTER TABLE messages MAX_ROWS=20000000000;
I skipped AVG_ROW_LENGTH from the query, I was confused what should be the value for this.

After executing the query, when i check the table status it displayed totally different picture. Now there were only 170109 rows left.

mysql> SHOW TABLE STATUS LIKE 'messages' G
*************************** 1. row ***************************
Name: messages
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 170109
Avg_row_length: 649
Data_length: 110563036
Max_data_length: 281474976710655
Index_length: 1961984
Data_free: 0
Auto_increment: 5409214
Create_time: 2007-08-23 10:07:08
Update_time: 2007-08-23 13:41:57
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=4294967295
Comment:
1 row in set (0.00 sec)
What could be the reason of this rows deletion?

I am on 32 bit system.
MySQL version: 4.1.18-standard-log
Operating System : CentOS 3.x
Memory: 4 GB DDR

3,000,000 Unique INT(11) Low-Hi Ranges In My TABLE, Am I Stuck With FULL TABLE SCAN?
I have a table with 3,000,000 different IP netranges.

each row has a unique range: ie: 1xxxxxxx-1xxxxxxx2
..

I try to find the primary_index when querying WHERE var BETWEEN lowIP and highIPrange.

Since there all of the netranges are unique, it seems like my INDEXES are useless, so the only way to optimize this would be to horizontally partition the data? The query is extremely slow ~1 second in MyISAM, and ~.50 second in MEMORY table format.

I'm assuming the only option is to horizontally position the data to improve performance, am I correct?

ERROR 1005: Can't Create Table './db/table.frm' (errno: 150)
I found the key to solve this problem in:

http://darkstar.ist.utl.pt/mysql/do...onstraints.html

You'll probably need an INDEX for that new foreign key you are declaring in
older versions this isn't neccesary but in latest ones it is a restriction.

You can have more info about the error description if you have root access
by typing
mysql> show innodb status;

LATEST FOREIGN KEY ERROR
------------------------
030807 1:27:10 Error in foreign key constraint of table database/table:
There is no index in the table database/table where the columns appear
as the first columns. Constraint:
foreign key (key) references database.table(key) on delete cascade)
type=innodb

also if you type:
shell> perror 150

you obtain:
150 = Foreign key constraint is incorrectly formed

I hope this to be enough...

Trying Abort One Select Accessing One Table Locked By Lock Table.
I'd like to configure one time to abort one transaction that are accessing one table locked by lock table write command ex:

Transaction A
mysql> start transaction;
mysql> lock table mytable write;

After I'll start new transaction B (another connection)

mysql> start transaction;
mysql> select * from mytable;

It's will become long time to wait...

My transaction B never "die".

I tried to configure innodb_lock_wait_timeout=20 in my.ini without success,

Copying Column Names And Properties From An Existing Table To A New Table?
I've got a table with 42 columns, set to different types (string, datetime, date, integer). It has 8 million rows. I want to make a new table with the exact same columns, but with no data in it. Is there an easy way to do this without re-defining every column?

Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary)

I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's.
I need to do this several times and have tried it several ways to no avail.

Table A
---------
companyID int(10) pKey
legacyID int(10) old legacy pkey

Table B
---------
bAID int(10) pkey
companyID int(10)
legacyID int(10)

Table A has values for both companyID (unique key) and legacyID.
Table B has values for bAID (unique key) and legacyID but companyID is empty.

I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship.

I need a query that will update ALL rows.

Transform Or Pivot(?) Or Crosstab(?) Table In MySQL. One Data Table.
I have a table in the form:

year | season | hits

2004 | Summer | 42
2004 | Autumn | 43
2005 | Spring | 51
2005 | Summer | 52
2005 | Autumn | 53
2006 | Spring | 61

I want to generate a transform or pivot or crosstab - I'm not sure what the correct term is - to return the data in form

year | Spring | Summer | Autumn
2004 | ------ | ----42 | ----43
2005 | ----51 | ----52 | ----53
2006 | ----61 | ------ | ------

Could anyone suggest a mysql query to generate the required output?

Utf8_general_ci Versus Utf8_unicode_ci Character Set.
utf8_general_ci Unicode (multilingual), case-insensitive

utf8_unicode_ci Unicode (multilingual), case-insensitive

(Above are character sets shown by phpMyAdmin.)

Can you please explain what is the difference between utf8_general_ci and
utf8_unicode_ci ? What are the effects of choosing one over the other when
designing a database ?

Performance Of VARCHAR Versus CHAR
Does anybody have experience with the performance of CHAR in
comparison with VARCHAR? I´ve converted the table from varchar
(dynamic format) to char (fixed row length) and the total size of the
data doubles (not counting the keys).

The database structure is as follows, the database has about 0.5 mio.
entries right now with many intensive queries. As I have heard, fixed
rows are faster.. but what about the influence of the total disc space
on performance? Code:

1 Simply Query Problem. Please Help With Insert Into 1 Table From Another Table
I have Table A that has some records already in and then I have Table B that does a few things and gets updated regularly.

With Table A, I want to get it updated every 8 hours or so with new data that has been inserted into Table B.

I will do this using the Cron.

But what would be the best insert query to use so that it does the process really quick.

The query that I have so far is:

Quote:

mysql_query("INSERT INTO table a (id, title, descrip) SELECT id, title, descrip FROM table b where app=1");

With the above query the id's match in both tables. But I only want the new records to be inserted into Table A where app=1 in Table B and the row is not already in Table A.

How can I add to the query so that it does this.

Query Table A And Return Records Not Found In Table B.
Given table A and table B.  Table A is a master table and table B is a list of authority or reference which is fed by selecting records from table A (via an UI I wrote in PHP).

The interface is working fine but I am left with one problem.    If I need to edit my table of authority, the source table (table A) should return ONLY entries not previously selected.

That said, if the A.key is in B.key, records from table A matching B.key should be omitted.

I could do this by looping through the query results from table A and querying table B, if no match, show A.key.  This I think is a bit antiquated and possibly too involved.

Is there a joint query command I can use?

ALTER TABLE `table` ORDER BY `column_a` DESC
I try to get better performance by implementing this:

http://dev.mysql.com/doc/mysql/en/alter-table.html

"ORDER BY allows you to create the new table with the rows in a
specific order. Note that the table does not remain in this order after
inserts and deletes. This option is mainly useful when you know that
you are mostly going to query the rows in a certain order; by using
this option after big changes to the table, you might be able to get
higher performance. In some cases, it might make sorting easier for
MySQL if the table is in order by the column that you want to order it
by later."

I run this SQL query on one of the table: