Solved

MySQL Table Structure for Approval of Records

Posted on 2006-10-29
24
592 Views
Last Modified: 2008-02-26
Hi,

I have a table for example...

TABLE _items
int id
varchar(255) title
text description
tinyint user_approved default '0'
tinyint admin_approved default '0'
int(10) expiration_date

index on id
index on (user_approved, admin_approved, expiration)

This table is used to store articles with a title, description, and an expiration date.  Articles should only show if they are both approved by the user and admin, and they are not expired.

My question will this suffer a performance hit with large amounts of records say 100,000?

Or would it be better to have a seperate table TABLE _items_pending that holds any "non-approved" records.  Then when they are approved insert them into the main _items table.  This way the main _item table does not need to have all SELECT statements query on the approved, admin_approved, expiration index?

Keep in mind this could be expanded so that articles have comments, comments have ratings, etc. and used in a much larger application.  So im wondering for future development which path is correct as far as scaling and speed go?

Thanks!
0
Comment
Question by:killer455
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 11
24 Comments
 
LVL 14

Expert Comment

by:racek
ID: 17829890
Remember - with 2 tables is it question about
2x insert and one delete if approved OR 1 x insert and 1 x delete for not approved
With 1 table : 1x insert and 1 x update OR delete...

100.000 records is not so much :-)
but: instead for index on ID - it is better with PRIMARY KEY
 expiration_date - use DATE or DATETIME ( and NOT int(10))
(expiration, user_approved, admin_approved) is better then (user_approved, admin_approved, expiration)

CREATE TABLE _items (
id int  not null primary key,
title varchar(255),
description text ,
user_approved
tinyint default '0',
admin_approved default '0'
tinyint , expiration_date DATE,
KEY expiration_date(expiration_date,user_approved,admin_approved));
0
 
LVL 14

Expert Comment

by:racek
ID: 17829897
CREATE TABLE _items (
id int  not null primary key,
title varchar(255),
description text ,
user_approved tinyint default '0',
admin_approved tinyint default '0',
expiration_date DATE,
KEY expiration_date(expiration_date,user_approved,admin_approved));
0
 

Author Comment

by:killer455
ID: 17830016
(expiration, user_approved, admin_approved) is better then (user_approved, admin_approved, expiration)
>> Why would this be better?  Just curious.

instead for index on ID - it is better with PRIMARY KEY
>> Yes my mistake.

Do you think it will ever experience any performance issues in SELECT? as there will be many more selects in this application than inserts/deletes.

Also SELECTs will be used throughout the script using JOINs and other queries so im assuming you will almost (almost not never) have to tag on to the end of the query something like: WHERE user_approved=1, admin_approved=1, expiration_date>'$todays_date'

Is this correct? (as far as getting articles that should display to the public.

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 14

Accepted Solution

by:
racek earned 150 total points
ID: 17830065
(expiration, user_approved, admin_approved) is better then (user_approved, admin_approved, expiration)
>> Why would this be better?  Just curious.

user_approved, admin_approved has very bad selectivity just 2 possible values.

Do you think it will ever experience any performance issues in SELECT? as there will be many more selects in this application than inserts/deletes.

Delete costs much more than insert

Also SELECTs will be used throughout the script using JOINs and other queries so im assuming you will almost (almost not never) have to tag on to the end of the query something like: WHERE user_approved=1, admin_approved=1, expiration_date>'$todays_date'

Yes and therefor is expiration_date first in index

0
 

Author Comment

by:killer455
ID: 17830132
What happens when you start throwing together all kinds of other queries together with this index though?

For instance put a fulltext index on (title, description)

Then you do a query checking for keywords in title/description but also making sure the article is approved/not expired?  I am just worried that once you start combining this with other queries its going to slow down.  Sure its probably fast on its own.

0
 
LVL 14

Expert Comment

by:racek
ID: 17830229
100.000 is not so much, but if you are worried...

On the first table can you create fulltext index..
CREATE TABLE _items (
id int auto_increment not null primary key,
title varchar(255),
description text );

CREATE TABLE admin_items (
id int  not null primary key,
user_approved tinyint default '0',
admin_approved default '0'
tinyint , expiration_date DATE,
KEY expiration_date(expiration_date,user_approved,admin_approved));

IF you will join this two tables, MySQL can use both indexes
0
 

Author Comment

by:killer455
ID: 17830665
expiration_date DATE

Why DATE and not int(10) if im storing a unix timestamp?

0
 
LVL 14

Expert Comment

by:racek
ID: 17830670
DATE or DATETIME is easier to use for datum - mathematics like
DATEDIFF, YEAR(), MONTH()
0
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 17832929
You can always use the UNIX_TIMESTAMP() and FROM_UNIXTIME() SQL functions to convert from date fields and timestamps and back.

The database is much better at figuring out how to store stuff than you are, pretty much.  I used INT fields for timestamps for a while, and it hurts in three ways:
1.  Mathematics on the date are a pain in the neck, and sometimes can't be done in SQL (short of converting it to a date field, in which case why not just lead with that)
2.  Other applications that may later use the database will have a hard time with it (may not seem like an issue, but why risk it, basically)
3.  Diagnostic queries are a pain.  You can't write SELECT * and be done with it; you have to convert the date field every single darn time, and it gets annoying.  (-:
0
 

Author Comment

by:killer455
ID: 17836895
So if you are doing a search using the fulltext key i mentioned above ALONG with the admin_approved, user_approved, and expire date key this is not going to cause a problem with 100,000 or 1,000,000 records?  It seems that 5 keys is a lot to SELECT by is it not?

0
 
LVL 14

Expert Comment

by:racek
ID: 17837197
No of record in the table is not so important, what is important is the period length. If you start your index with date, and you will check all values for one day, MySQL will scan only one days data.
0
 

Author Comment

by:killer455
ID: 17837346
But if you are doing expiration > todays_date it could be scanning all rows if none are expired right?

0
 

Author Comment

by:killer455
ID: 17837369
Also what if you then throw in an ORDER BY title statement to this query

0
 
LVL 14

Expert Comment

by:racek
ID: 17837372
NO only records after todays_day. You can check it with explain...
0
 
LVL 14

Expert Comment

by:racek
ID: 17837388
ORDER BY sorts only rows in results.
0
 
LVL 14

Expert Comment

by:racek
ID: 17837409
ORDER BY sorts only rows in the result of query, but if your index contents all you nead for your result, all operations are provide on the index
0
 

Author Comment

by:killer455
ID: 17837416
Yes but im saying that if all rows are after todays date (no expired items) then all rows are scanned.

0
 
LVL 14

Expert Comment

by:racek
ID: 17840122
yes :-) but depending on columns in idex and columns in query, Mysql can scann only index
0
 

Author Comment

by:killer455
ID: 17840468
Ok last thing with the DATETIME field in mysql so if date_expire field is of type DATETIME and you do something like  date_expire > NOW() does mysql do this comparison correctly?

0
 

Author Comment

by:killer455
ID: 17840512
Also say you want to set date_expire = 0 if the article needs to be set to never expire.

Does this mean you have to now query WHERE date_expire > NOW() OR date_expire = 0
Does this require 2 scans of the table to do?  Will it cause a speed issue?

0
 

Author Comment

by:killer455
ID: 17840513
Points increased
0
 
LVL 14

Expert Comment

by:racek
ID: 17840745
>> Does this mean you have to now query WHERE date_expire > NOW() OR date_expire = 0

You can use UNION like

SELECT ... WHERE date_expire > NOW()
UNION
SELECT ... WHERE date_expire = 0; // you mean '0000-00-00' ?
0
 

Author Comment

by:killer455
ID: 17843436
Why is the UNION any better than doing WHERE date_expire > NOW() OR date_expire = 0
Yes im just thinking of some way to set an article to never expire.

0
 
LVL 14

Expert Comment

by:racek
ID: 17843994
try it with EXPLAIN, OR may be ok??
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question