MYSQL :: Query Optimization Advice?

Optimization Advice
I'm having Database connection problem on my VPS server runing SMF forum script.
It often displays "unable to connect to database" notification when there is more than 200 users online (in 15 minute period).
I have raised max mysql connection number from 100 to 150, but that didn't help.
Connections per second number has doubled in last month (but traffic is only slightly higher)

Here are some mysql informations, any advice how to get this numbers to normal values is welcome.

Query Optimization Help Please
I am traversing over a table that holds "adspaces" via two aggregate functions to get the total number of adspaces for a publisher and the number of approved ones. The query is very slow and there must be a way to convert the subqueries into joins or anything else. Would really appreciate it if someone could give me a hand.

PHP

$sqlQuery =      "SELECT a.*, " .
                       "(SELECT COUNT(b.id) FROM ".DB_TABLENAME_ADSPACE." AS b " .
                          "WHERE b.publisher        = a.publisher " .
                           "AND b.status_approval <> '" . STATUS_WAITING . "' " .
                          ") AS num_adspaces_total, " .
                       "(SELECT COUNT(c.id) FROM ".DB_TABLENAME_ADSPACE." AS c " .
                          "WHERE c.publisher        = a.publisher " .
                            "AND c.status_approval  = '" . STATUS_APPROVED . "'" .
                         ") AS num_adspaces_approved " .
                  "FROM `" . DB_TABLENAME_ADSPACE . "` AS a "
                . "WHERE (a.status_changes  = '" . STATUS_WAITING . "' "
                .     "OR a.status_approval = '" . STATUS_WAITING . "') "
                .    "AND a.watchlist       != '" . WATCHLIST_WAITING . "' "
                . ";";

SQL Query Optimization Help Please
SELECT sess.id, u.id AS user_id FROM
(SELECT s.id, s.session_id
FROM `ll_session` AS s
WHERE s.online = ཈'
) AS sess
LEFT JOIN `ll_user` AS u ON (sess.session_id = u.last_visit)
What I have so far is this:

SELECT s.id, s.session_id, u.id AS user_id FROM `ll_session` as s

LEFT JOIN `ll_user` AS u ON (s.session_id = u.last_visit)
WHERE s.online = ཈'

Query Optimization
On one of our other production servers we have a write heavy database, which currently seems to be utilizing 80% CPU usage and one of our queries seems to be taking quite some time to actually execute.

SELECT *, CONVERT_TZ(date_trade,'GMT','Australia/Sydney') AS date_trade FROM trade_messages WHERE processed = 'NO' ORDER BY ftid ASC LIMIT 200;

200 rows in set (6.33 sec)
The ORDER BY is a necessary part of the query, we could most likely sort this within the code, however we still experience a 3 second query removing the ORDER BY and still seems to scan a lot of rows.

mysql> EXPLAIN SELECT *, CONVERT_TZ(date_trade,'GMT','Australia/Sydney') AS date_trade FROM trade_messages WHERE processed = 'NO' ORDER BY ftid ASC LIMIT 200;
+----+-------------+----------------+------+---------------------------+-----------+---------+-------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------------------+-----------+---------+-------+--------+-----------------------------+
| 1 | SIMPLE | trade_messages | ref | processed,searchArgs,proc | processed | 1 | const | 112146 | Using where; Using filesort |
+----+-------------+----------------+------+---------------------------+-----------+---------+-------+--------+-----------------------------+
1 row in set (0.66 sec)

EXPLAIN SELECT *, CONVERT_TZ(date_trade,'GMT','Australia/Sydney') AS date_trade FROM trade_messages WHERE processed = 'NO' LIMIT 200; +----+-------------+----------------+------+---------------------------+-----------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------------------+-----------+---------+-------+--------+-------------+
| 1 | SIMPLE | trade_messages | ref | processed,searchArgs,proc | processed | 1 | const | 111536 | Using where |
+----+-------------+----------------+------+---------------------------+-----------+---------+-------+--------+-------------+
1 row in set (0.00 sec)
this is a frequent query we execute, and it would be ideal to optimize this further.

Query Optimization Help Please
I have the following scenario:

* I have n categories
* I have m users
* I have x textlinks

Textlinks are referenced to a category by the ll_partof table.

I am willing to fetch every user (advertiser) for every category and output the date of the last textlink purchased, the number of his textlinks in that category and the amount of money spent in that category.

Here is my query so far. It uses subqueries and is therefore very slow. How can this be optimized? Thanks for any help.

Select
c.name as Kategorie,
(SELECT timestamp
FROM ll_textlink as t
WHERE t.advertiser = u.email
ORDER BY timestamp DESC LIMIT 1
) as last_link,
u.email as Email,
u.forename as Vorname,
u.surname as Nachname,
u.company as Firma,
(SELECT count(id) from ll_textlink as t
WHERE t.advertiser = u.email
GROUP BY t.advertiser
) as num_links,
(SELECT sum(current_value) from ll_textlink as t
WHERE t.advertiser = u.email
GROUP BY t.advertiser
) as budget_links,
FROM ll_user as u
INNER JOIN ll_textlink as t ON (t.advertiser = u.email)
INNER JOIN ll_adspace as a ON (a.id = t.adspace)
INNER JOIN ll_partof as p ON (p.adspace = a.id)
INNER JOIN ll_category as c ON (p.category = c.id)

WHERE
t.extension_possible = Ƈ'
AND t.next_period = '-1'
ORDER BY c.id DESC
l

Query Optimization Help
I've got a query that is trying to find matching rows for two endpoints. Like this...

$sql = "SELECT * FROM my_table WHERE 1234567890 BETWEEN begin_range AND end_range";

The problem is, my_table has about 3.5 million rows, and therefor takes about 4 seconds to run. I've already indexed both the begin_range and end_range fields and I'm just wondering if (other than throwing hardware / memory at the problem) there's anything else I can do?

Is there a better way to query the data?

Query Optimization
I am familiar with the VERY basics of MySQL (in other words, I am not an expert...), and I am currently working to create a simple search engine on our website. Users should be able to search a database of over 150,000 sheet music titles by simply inputting a keyword(s) phrase. That phrase should be searched into different tables at the same time but if the keyword phrase includes more than one word, any word need to be searched.

For example, let's say an user is looking for "Brahms violin concerto". The search engine should display all the records that matches all those entered keywords.

Here are the tables that need to be searched:

|title|composer|instruments|description|

and here is the query I am trying to work on (after have split the keyword phrase into separate keywords):

Quote: SELECT title, composer, instruments, description, price FROM mydatabase WHERE (title REGEXP "(^| )brahms( |$)" OR title REGEXP "(^| )violin( |$)" OR title REGEXP "(^| )concerto( |$)") AND (composer REGEXP "(^| )brahms( |$)" OR composer REGEXP "(^| )violin( |$)" OR composer REGEXP "(^| )concerto( |$)") AND ((instruments REGEXP "(^|;| )brahms(;| |$)" OR instruments REGEXP "(^|;| )violin(;| |$)" OR instruments REGEXP "(^|;| )concerto(;| |$)") OR (description REGEXP "(^| )brahms( |$)" OR description REGEXP "(^| )violin( |$)" OR description REGEXP "(^| )concerto( |$)")) order by title like "%brahms%", title like "%violin%", title like "%concerto%", instruments like "%brahms%", instruments like "%violin%", instruments like "%concerto%" The query works, but it takes too long to be executed... over 10 seconds! I am aware that REGEXP (like "LIKE") don't uses indexes, but I cannot find a different solution to match any possible keyword or part of it.

Query Optimization
I have a table, call it 'table1' and a query which looks like:
Code:

SELECT table1.field1, table2.field2 FROM table1, table2 WHERE BINARY table1.username = table2.username AND table2.status = 'Active'

I also have an index set-up on username in table1 and table2. For some reason, mySql does not use the index in table1 for this query, but if I remove the BINARY keyword in the query, the index is used. The type of index is simply 'Index'. Does anyone know why having a case sensetive comparison in the where clause uses index while case-insensetive does not use the index?

Query Optimization
In many of my project. I create an admin welcome page.
On that welcome screen, there is list of all the manageable module names
with their links & the count of number of records under that module.

Example:
Manage Products 104
Manage Customers 235
Manage Orders 430
& so on....

For the count of these records.. I am putting one query for each module..
like "SELECT COUNT(*) FROM products WHERE enumStatus='A'"
For 15 modules, I have to put 15 queries.

EDIT: One module is for one table of the DB

Query Optimization
This is the query:

$sql = "SELECT DISTINCT(customer_number), upload_date FROM equipment_info WHERE (closed = '0' AND dealer_id ='0') AND ((item NOT LIKE '%Item1%') AND
(item NOT LIKE '%Item2%') AND (item NOT LIKE '%Item3%') AND (item NOT LIKE '%Item4%') AND (item NOT LIKE '%Item5%')
AND item != 'Company Owned Equipment') GROUP BY customer_number ORDER BY upload_date ASC";

yet, I still get this included in the output:

//CI45 Standard Item1 Equip Package, Replacement CI46 LNB Only, Replacement CI33 30 Foot Coax - Yellow

Yet, it was one of the items I wanted it not to match.

Sub Query Optimization
I am running a dated version of mysql, like 3.23.xx, and I have a query:
Code:

$sql = "SELECT * FROM users_info
WHERE first_name != '' AND users_id IN (SELECT DISTINCT id AS list FROM equipment WHERE id != '0' GROUP BY list )
ORDER BY last_name ASC ";

The query is substantial slower than just running a nested query

Small Query Optimization
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed.The query is:

Quote: select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%'

I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.

Help With A Small Query Optimization Using An Index. THANKS!
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed.

The query is:

Quote:

select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%'

I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.

Cross Join Effects With Query Optimization
ELECT count(t.id)
FROM `ll_textlink` AS t, `ll_period` AS p , `ll_user` AS ua , `ll_user` AS up
WHERE
t.actual_period = p.id
AND p.status_approval_publisher = Ƈ'
AND p.value > Ɔ'
AND t.online >= Ƌ'
AND Ƈ' >= (
SELECT count(p.id)
FROM `ll_period` AS p
WHERE p.textlink = t.id
)
AND t.online <> Ɗ'
AND t.current_value > Ɔ'
AND t.advertiser = ua.email
AND t.publisher = up.email
AND ua.rights = Ɔ'
AND up.rights = Ɔ' ;
This is what I am at now:

SELECT count(t.id)
FROM `ll_textlink` AS t
INNER JOIN `ll_period` AS p ON (p.textlink = t.id)
INNER JOIN `ll_user` AS ua ON (ua.email = t.advertiser)
INNER JOIN `ll_user` AS up ON (up.email = t.publisher)
WHERE
t.actual_period = p.id
AND p.status_approval_publisher = Ƈ'
AND p.value > Ɔ'
AND t.online >= Ƌ'
AND t.online <> Ɗ'
AND t.current_value > Ɔ'
AND ua.rights = Ɔ'
AND up.rights = Ɔ' ;
However, the number return is quite a bit higher than the from the first query. Are there any cross-join effects and if so how can I avoid them?

A Lil Advice
i'm looking to build a relational database that will hold product reviews and statistics that i will eventually publish on the web where they will be searchable, etc. i'm working on a mac os 10.3.9.

is mysql the way to go? what app should i be using to create the database?

Advice
I own a company whereby my 50 employees (soon to be 100) all work from home and access our server via terminal services client. These 50 people work on 40 separate Access databases. Some small, some large (over 100 mb).
My IT consultant tells me that I should move to a sql server (which I agree), upgrade to Access 2003, and minimize term server use by moving to a web based input % reporting engine for common employee tasks (due to the load of 100 employees on Access).
- Access 2003 is $200 a user (are there cheaper options)
- We regularly modify the Access databases. Note that I created the databases but I am not a developer. I an very comfortable in Access (its the only database software I have ever used).
So, is MySQL an option for this environment?
I know its a big, ugly question with few details, but I am looking at all options.

Advice Requested
I'm going to be creating a signup form with the standard fields (fname, lname, email, etc), plus user definable fields. Currently, I'm kind of stuck on how to go about doing this.

My users table has an id field (PK), email field, IP address field, etc. I want to give the site admins more control over what they can put into their signup forms. Some people may want to have a website field, others may want to have a field for the user's fav book. Whatever.

Now, if I put these into the users table, I'm now fixed at how many fields I can have, correct? I mean, I can set a determined number of fields and call them custom1, custom2, etc. and let the admin define their user-readable names, but it's still a fixed amount of fields.

I could create a new table with 2 fields - name and value - where name is the PK. That way the admin could enter as many as they want. But how would I then have the same fields in the users table to hold the data?

Best Architecture Advice
Let's say you have 800,000 employees (huge company huh?), 300 sectors, and 3 security levels for each sector. You want to hold the security level for each sector for each employee. I can't figure out the best way to do this. Right now, my company has a table for each sector - each with columns [employee_id, security_level]. This results in 300 different tables and the joining when searching on several sectors gets crazy. I'm trying to figure out an easier way to structure this.

If you have one table [employee_id, sector_id, level_id], because of the many-to-many relationship between employee_id and sector_id - that's going to give you a 240,000,000 row table. If you have one table made up of a columns for each sector holding the security level value [employee_id, sector1, sector2, sector3, etc] you have a table with 301 columns, which will put me way out of my index max limit.

Design Advice
I am looking at designing a mini database to handle subsets of data at my company. You probably know the drill--it's too much to handle with a spreadsheet, but not enough to warrant hassling the DBAs with loading it into our main servers.

So I am looking at managing the data via a local instance of MySQL. The largest file I need to manage at the moment is about 100 columns x 165k rows.

Obviously, I wouldn't build table of that size. That's the whole point of relational DBs.

But here's the deal, at my company, once someone finds a way to do something, it tends to stick. So I'm looking at solving a few temporary problems by managing subsets of data from our main DB.

I have this suspicion that once I am able to do this, the demands will grow, and all the sudden, I'll get stuck managing a whole bunch a data for a bunch of people.

So I want to build the DB right. I've never built a DB before. I just query the main DB.

Without getting into proprietary details, I can say that I am looking at unique IDs that correspond to clients--about 165k, plus all relevant associated data.

What I am tracking is which clients have performed what jobs for us, when, and what the result was.

I need to be able to run reports on this, and I need to add data at the end of each week.

Given all of the above, what general advice do you have? Are there any huge pitfalls to avoid? Any thing that I absolutely should do? Keep in mind that for now, this is just going to be run on a Core2Duo Desktop with 2 gigs of ram, so anything I can do design-wise to speed things along will be useful.

Luckily, I am not in a time crunch. But thanks in advance for whatever advice you are able to come up with.

Advice Sought
I'm somewhat new to database design in MySQL from a Designing from the "Ground Up" Point of view. I mean I've used Databases in a limited capacity, but this is my first time developing one from scratch, and mainly its for educational purposes, so I can eventually do this myself, rather than hit hotscripts, or some other repository but will be for a functional website at the same time.
I have a library of links I would like to display. My client has presented me with a word doc with the links and a description.
I assume I would just create a table with Category,Title,URL,Description as fields, but if I want to present the end user with a dropdown list at the top of the page that allows them to select a category, would I have to create a separate table with just the categories, and link it to the 1st DB?

Advice On How To Structure Tables
I currently have a dynamic site that displays info about different musicians. My database is currently set up with a table that contains all the infor about each musican such as name, type of music, website address etc. i.e all single bits of information.

Now I would like to add a form to allow the musicians to upload one or more mp3 files.

It would be fine if it was just one mp3 file as I would simply have a field in the table to store the name of the mp3 file.

However I am not sure how to deal with multiple mp3 files. Do I just create say 10 fields in the table for a maximum of 10 mp3 files for each musician? This doesn't seem the mosst sensible idea but I am fairly new to php and mysql so I am not sure what alternatives there are.

I would appreciate some advice on this. I was thinking perhaps I could create a separate table to store all the names of all the mp3 files for all musicians but doing this I do not know how I would know which ones are related to each musician.

Also related, should I store the mp3 files in separate folders for each musician or just have one mp3 folder on the server and save them all in here?

Advice On Hierarchical Schema
I'm trying to come up with a scema for a hierarchy of shopping categories.

Each category can have many children categories and many parents. Ie. exist more than once, and at different levels of the hierarchy. There will be a limit of 5 to the depth of the tree, but some branches will terminate sooner. At the end of the branch will be a link to products within that category.

I've read up on hierarchical structures, even read joe celko's book on the subject (I've just recovered from the resulting brain embolism ) but haven't really found solutions that seem to handle the many-many relationships going on, and in the case of the celko book there was a lot that mysql 4 can't do.

I'm using version 4.1.20 (stuck on redhat and it doesn't support 5)

Advice On Plan Of Attack
OK, I have a large web based questionaire form I am constructing using PHP/MySql. There are around 50 questions with each question having anywhere between 4 and 30 possible answers, depending on the question, that the user can select using checkboxes, meaning they can have multiple checkbox answers per question.

My question is what would be the best method to store the checkbox data in MySql, keeping in mind there is also is going to be a search routine that will need to pull the data back out efficiently?

Should I | delimit each checkbox answer in a mysql field for each question so there are 50 fields with pipe delimited responses, or do I make a field in the table for each possible answer (around 750 fields)? Or is their a better method???

With so many checkbox answers, what is the best/ most efficient way to store their responses knowing the data will need to be searchable?

I can get through the programming side of it, although I am not an expert by any stretch of the imagination. Just looking for advice on how to set it up before I get started.

Don't want to do it one way then discover later that the search is too slow because I didn't lay it out right first

Beginners Advice / First Database
I am creating my first database / website and would appreciate advice on how to organize or set up the tables.

what I have is user uploaded documents.

CREATE TABLE `documents` (
`doc_id` int(10) unsigned NOT NULL auto_increment,
`member_name` varchar(255) NOT NULL,
`language` varchar(255) NOT NULL,
`country` varchar(255) NOT NULL,
`pgrating` varchar(50) NOT NULL,
`document_name_file` varchar(255) NOT NULL,
`type` mediumint(2) default NULL,
`document_pathto` varchar(255) NOT NULL,
`category` varchar(255) NOT NULL,
`keywords` varchar(255) default NULL,
`series` varchar(255) NOT NULL,
`title` varchar(255) NOT NULL,
`date` date default NULL,
`votes` mediumint(255) NOT NULL default Ɔ',
`price` mediumint(255) NOT NULL,
`rating` mediumint(255) NOT NULL default Ɔ',
`purchased` mediumint(255) NOT NULL default Ɔ',
`leamount` varchar(255) default NULL,
`leExpired` mediumint(2) NOT NULL default Ɔ',
`userDisabled` tinyint(1) NOT NULL default Ɔ',
`lowrateDisabled` mediumint(2) NOT NULL default Ɔ',
PRIMARY KEY (`doc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

My question is, for searching for these should I make a lot of lookup tables such as?

category id | comic id

some of the documents can be (changed to) private and not displayed in search results.

so I created a table "a_docs"
user_name(id) | active doc (id)

also the visitor can sort by keywords, ratings, category, country, language, latest and type

My question is, should I create a "lookup" table for each of the search criteria and then do something like a join (I honestly don't have a clue what I am talking about yet) ?

I also need to generate reports for the members such as how many and what documents were purchased.

my guess is a lookup table
purchased_docs

user_name(id) | purchased doc (id)

then once I have the doc (id) for the user I can then lookup the "documents" table to find the price for that document (id). Is this a good approach?

I expect to eventually have hundreds of thousands of documents and after this is built I will have someone go over the structure to make sure it is fully optimized.

btw I have been reading documentation and in my table the interger values and settings are all wrong.

I am good with php and have built dynamic websites with user editable content in flat files but now its time to jump on the mysql wagon.

Table Design Advice
I'm looking for some advice on suitable ways to construct a table or tables to handle the following situation.

I have a dynamic and structured content requirement, where the user can define a new type of item with a new/different set of fields. So I have

item types:
- name, fields

items themselves
- one or more field+content pairs depending on the information in item-types.

That in itself is pretty straight forward, I can store the item details in a single table of item-id, field-id, value triplets. ie. my db could follow the structure shown at the bottom of the post. However, I need to be able to filter for items based on the values of several fields.

In order to do that I can join the item table to itself for each field after the first, e.g. SELECT item-id FROM item-details i1, item-details i2, item-details-i3 WHERE i1.item-id = i2.item-id AND i1.item-id=i3.item-id AND i1.field-id=<field1> and i1.value=<expr1> AND i2.field-id=<field2> and i2.value=<expr1> AND i3.field-id=<field3> AND i3.value=<expr3>;

If the item needs to be selected on five fields then there would need to be four joins. Seven ... six, etc, etc.

Also, I would like to be able to search the field values without reference to the fields themselves. e.g. Search for all items with a value containing "red".

Is this the best way to handle the problem?
Is there another table design that would work better?
e.g. for each item-type, use the field information to create a table specific to that item-type with columns for each of the fields.

Possible structure
=============================

item-type
- type-id
- type-name

fields
- field-id
- field-name
- other field data (description, validation, etc)

item-fields
- type-id
- field-id

items
- item-id
- type-id

item-details
- item-id
- field-id
- value

Performance Advice Required
I'm after some performance advice if anyone has some. I've a table and query described below. The table holds about 13 million records. Query times for the query are about 135 seconds, which is way to long for live queries. Does anyone have any suggestions on how to reduce this. I realise live queries to a database this size might have to be tackled by spliting into other tables etc, but other sites manage this, and I just don't know how. I have already sumerised the data as much as possible to minimise the rows.

dns_stats CREATE TABLE `dns_stats` (
`hits` mediumint(11) unsigned default '0',
`domainname` varchar(100) NOT NULL default '',
`datetime` date NOT NULL default '0000-00-00',
`server` tinyint(4) unsigned NOT NULL default '1',
KEY `datetime` (`datetime`),
KEY `datetimedn` (`domainname`(20),`datetime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

select domainname, sum(hits) as thits,server, datetime from dns_stats where domainname like '.moc.tsettsrif%' and datetime >= DATE_SUB(NOW(),INTERVAL 30 DAY) group by datetime,server order by datetime

and explain shows

1,SIMPLE,dns_stats,range,datetime,datetimedn,datetimedn,23,N,7925,Using where; Using temporary; Using filesort

This table gets updated each night, so is only read from during the day. The queries are almost exclusivly reads. I tried tests without the summary and grouping but this only takes about 10 seconds off, so I'm guessing almost all the time is spent retrieving the initial records.

Table Structure Advice
I have two tables - Users and Groups. Pretty straightforward layout where the Users table has ID, last_name, first_name, etc... The Groups table is simply ID, name, description, etc... I want to include in my Users table a group_ids field. What makes this complex, is one user could be a member of just one or many groups, so I can't simply say User1 is in Group1. User1 may be in Group2, Group17,etc...

How does one (or how would you) apply this? I've thought about making the group_ids field a 'text' datatype and separating the group ids by comma or space., but for several reasons this doesn't seem to be the right way to do this.

Let me know if I haven't explained this well or if I should give an example of the data for clarification.

Complicated Join Advice
My tables look something like this:

FIGHTER
fighter_id
first_name
last_name
age
style

FIGHTS
fight_id
fighter1_id (key to FIGHTERS table)
fighter2_id (key to FIGHTERS table)
event_id (key to EVENT table)
winner (this field will contain either 1 or 2 indicating which fighter won)
win_round
win_time

codes...

I've been trying to figure out an appropriate query but can't seem to get it. The only solution I've come up with so far is to break it into multiple SELECT statements which seems to defeat the purpose of a relational database.

Advice Needed On Database Schema
I am trying to come up with a proper database schema for this situation that's at least 3 NF. I'm just having trouble with a few of the associations.

First, here's the situation.

I want to store information about all the cards in a Tarot deck.

In the deck, there are two types of cards; namely the major and minor arcana.

Both of these have some common information I want to store, and I want to be able to choose just a random card, but they also have some data unique to each, and I also would like to choose a random card of a specific type.

For example, both types have an associated element. I will have an elements table probably with simply id and name, and perhaps an element_id in the main cards table.

Both also have an associated planet and zodiac sign. Most of the major arcana cards have either a planet or zodiac sign, but not both. Also the association is a little different. With the minor cards, the planet is a specific decanate within the sign. I guess that might not matter too much though in the storage of the data, though again it might if I wanted to retrieve the planets associated with the decanates of a sign.

Further, the major arcana have a specific Hebrew letter associated with each card, while the minor cards have no such associated letters.

Finally, each of the major arcana cards are classified as elemental, planetary, or zodiacal. The minor cards have no such classification.

So, how should I store these data? They are both cards, and have some common data, but both have very different data as well.

Advice On Creating Categorie ID's
Hi (Sorry the title may be a bit confusing - only realised after submitting - and now cant change it!)

Sorry if this is a bit basic but i am a noob at MySQL and therefore may be going about this in the wrong way.

The situation I have is that i am trying to get users to record information against categories of work set by different departments, but also allow the user to create custom categories.

The numbers of categories created by the Departments may be fairly small (20 - 30 per Dept) and the same can be said for each user - except that the number of users may grow quite large - and therefore the number of user defined categories.

I think i need to therefore split this into two tables DeptCategories and UserCategories, but what i need is the user to only have one combined category list to work with when recording data... so therefore need to be able to to create a list of the users DeptCategories and their own UserCategories...

The alternative I gues is to have one table with a column(s) that identifies the dept and or the user that entered them... but this then raises the question of how do you list all the available categories for a particular user in the order of DeptCategories and then UserCategories (both in alphabeticcal order)

Any suggestion greatly recieved, as I would like to set the DB up correctly at the start so I dont have to alter tables etc.

Advice On Setting Up Address Database Please
(Before i start i feel obliged to point out my Noobie status so not to shock! )

As part of a larger poject i need to set up an name and address database.

Before i start i would very much appreciate some comments on the format of the primarry tables.

For example if i set first_name as VARCHAR(15), last_name VARCHAR(30) - this seems ok, but from experience what would people suggest as being realistic for the Address part..

Do i need several fields to allow for each line of the address:

MyHome
MyStreet
MyStreet2 (optional)
MyTown
MyCounty / State
MyPostCode

I have found several different stratergies for this such as VARCHAR with no max length.... is this a good idea?

Aslo UK post codes - i guess a VARCHAR of max 10 would suffice, but does any one know of an imput mask / regular expression to ensure valid imput?

Idea, Advice If Feasible (somewhat Off-topic)
Here is what I am facing. We need to be able to run some pretty intense queries on our existing MySQL database at work. The programmers say it will put too much stress on the servers and can't be done without a major overhaul, and its just a handful of users who need to do it.

What I want to do is build some client software that can do the queries. I want to take the data from the MySQL server, build it into a client, and let the end user run as many queries as they want. If they slow down their machine, fine, but they won't slow down the server. Plus most users are sitting on some very powerful hardware, so it should be able to handle it.

The issue is my coding skills are rather rusty. So I'm wondering, if I were to take on this project, what would be the best way to tackle it? What languages are best, is there something open source that is already available, etc. The end result has to go into Excel, because thats what my users can operate. Is my idea even feasible or will this be a giant pain?

I'm looking at creating a custom application that can sort/calculate/compare a database that is 1,000 to 5,000 rows in length. Its comparing hours/shift lengths for a large company over several departments. The user will not be adding addition data, just analizing what is already there. I can think of a lot of ways I might be able to do this, but I don't know what way would be easiest. Any advice would be helpful, this project is a long time from happening, if at all.

Need Advice On Switching From Sql2000 & Access To Mysql
Im a coldfusion programmer, so it all started for me learning on access db's. Then sql 2000. Along the way, I started installing the cms systems and learning how mysql | phpmyadmin worked and really started to prefer this.

The .htaccess control is whats making my decision for me. I just cant control urls enough using IIS & coldfusion.

I have found a host that supports coldfusion running on the lamp platform.

To convert the sites, I figure 2 major tasks:
1. Converting the databases over to mysql
2. Making sure all my queries function properly.

I figure converting wont be too bad, but Im wondering how much structural differences exist between T-sql and the syntax mysql uses.

At most, my queries to convert are 3 tables using LEFT OUTER JOIN or INNER JOINS.

Any feedback on the process will be most appreciated
(I cant wait to get off IIS)

Advice On Table Setup To Maximize Efficiency
My table set is is garbage .. I knew this when I created the table a couple of years ago. Now that I have tonnes of data in it the queries are taking forever. So it's about time I constructed a more efficient table. I am turning to this forum for some advice.

My table stores RSS feed content in it -- every 15 minutes a cron job INSERTs data from 300+ feeds and dumps it into the table.

The table structure consists of:

id
dateUnix
headline - headline of the article
permalink - URL to the article
body - TEXT type - body of the article
author - person who wrote the article
authorURL - URL to the person's website
favico
sitetype - I categorize the types of sites (0 = news media, 1 = blogs, 2= videos, 3= podcasts)
vote - users vote on stories
verified - 1 = verified, 0 = censored/banned story
newsCat - 20 different categories
newsSubcat - 50 different subcategories

I think the best way to make this table more efficient would be to break it up into 3-4 different tables using the id as the key.

ps: my cron job checks for duplicate entries and does not insert duplicates -- so duplicate stories are not entered

DB Moving To New Server, Requesting Upgrade Advice
I currently have an older dedicated server with about 10 MySQL 3.23 databases on it. All but one of the DBs are for Wordpress. One is for Invision Power Board. The server is in need of an upgrade because it isn't handling the traffic very well so I've aquired a new, and much more powerful one.

The new server has MySQL 4.1.20 on it so right away I'm in trouble because I can't seem to figure out how to get my tables converted. I'm trying to follow the instructions at http://dev.mysql.com/doc/refman/4.1/en/upgrading-to-arch.html, specifically:

"You can also store the dump in a file, transfer the file to the target machine, and then load the file into the database there. For example, you can dump a database to a compressed file on the source machine like this:
shell> mysqldump --quick db_name | gzip > db_name.gz"

But when I try to do this with any of my databases while logged in as ROOT I get the following:
"Got error: 1045: Access denied for user 'root@localhost' (using password: NO) when trying to connect."

I tried using the help file to figure out how to specify the password but either I couldn't get the syntax correct or it just wasn't working. So, my first request would be for a little guidance here if possible.

Assuming that problem is solvable, I have question. If I'm already going to have to import the data into the new 4.x format, would it make more sense to have the MySQL on the new server upgraded to 5.x now - while it's still brand new and before it gets all loaded up?

What would be the benefits or drawbacks to doing so? It seems to me that since I have no experience with this that it would make sense to do the upgrade to 5 now so I don't have to go through the pain of another DB upgrade down the road so soon.

Creating A List Of Repeated Search Words, Advice?
I store the search terms people have been using for the last year in a mysql database. (migrated to Mysql 5 recently).

Now I want to try and find out which terms have been used the most, in order that I can try and understand what users might be looking for.

I have no clue what word describes this kind of self indexing, or how to start doing this...

Can anyone give me a pointer on where to start looking?

More:

I also stored a datestamp, so would like to know month by month what were the search terms used.

Heres a snap of the meta:
Table : search_terms
search_ID int(11) auto_increment
daydate int(11)
term varchar(40)

Only has 135k rows.

Advice Needed Login/registration System With MySQL 5/PHP
I want to setup a login/registration system for my website with MySQL 5 / PHP. Most of the job should be the standard stuff: register form, login form, forgot password form, etc.

Can anyone advice me how to begin ?

Are there any good tutorials which i should use or are there freeware or cheap commercial tools which i should use ? Or maybe you recommend me to an awesome programmer you know who does this job at low cost ?

- a MySQL/PHP Newbie

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.

Some Advice For A Newbie On Big Scale Websites Like Ebay, Facebook, Youtube
I am literally newbie in database design. Just finished kevin yank's build your own database driven website using php and mysql. The book helped me to understand the imporatance of deisgn in a database driven site but I still finding it difficult to figure out the large scale websites like ebay, facebook or youtube. Does anyone know how the database might be designed for these sites. Facebook and youtube are tagbased sites where everything is connected by tags. but how are these tags organized in tables?

Optimization
I'm running MySQL 5.0.19 (windows), and running mostly well, but in
some cases seems too slow. I've been optimizing SQL as much as
possible, indexing important fields, using OPTIMIZE TABLE to clear out
deleted records, etc. I would also like to adjust the my.ini settings
to make sure things are set there to be as optimal as possible.

My
question is, when I look at the statistics in SHOW STATUS, is there any
guide for what to look for as far as what's running optimally or needs
improvement? I'm connecting to MySQL from ColdFusion.

Optimization
I have 2 tables that I join about (150,000 rows each)

in my where clause I have a statement that says ... t2.franchise!=3

now sometimes when the query is generated, there is a statements t1.feed=4

Now I know that when there is a feed 4, the franchise will never be 3

So, does taking out that extra franchise statement when I know it does not matter speed things up? I have about enough of these type of things that if would case me 3 or 4 conditionals in every query. I can just take care of them in php, but I will not waste my type if it will not matter.

Optimization
I wanted to ask information to you on like optimizing MySQL, in how much in the event that I illustrate I have found to you of 'the anomalous' performances.

In the first place I have intalled MySQL 5.0.11. I have a table 'Anag' with 31 fields, primary key Id AutoInc and one key univoque for Desc+Indirizzo+Localita.

If from the Query Browser I execute 'select cod, descr from anag' come extracted 150000 records in 4 second ones. If instead I execute 'select cod, descr from anag order by descr' the 150000 records they come extracted in 100 second ones.

I have tried to create a single index on the field descr, but the times do not change. The times are not change to you after to have made the 'Check Tables' and 'Optimize Tables'.

Not creed that is normal school that the clause Order by on an indexed field me must cost therefore a lot. What I could modify in order to improve the performances?

Optimization
I have a MySQl database on the backend of an Access front end where multiple users update client information as calls come into the company.  All of the data is now linked into Access from linked tables.

The problem is the response time when they are adding records.  It's not a heavy volume at all but there are about 10 users updating at the same time.  All users are set up under one username thread, as this was an easier way to set up the ODBC connection. (Is that the right way to go?)

My question is...what are the optimization parameters that I should be adjusting?  I am sure it has to do with the cache and the amount of memory being met for disk writes.  (Is this correct?)

Optimization
I need to get the last entry of a table, so I'm using the following query:

SELECT * FROM table WHERE (some conditions) ORDER BY date DESC LIMIT 0,1

This seems a little bit stupid... Does anybody know an another way to do that? Can I keep a ordered table and than avoi the use of "order by"? If yes, how can I do that?

Optimization
I read at MysQl.com about Explain SELECT, it says at then end of a =
paragraph to optimize your query, using filesort or temporary, but I =
went to MySQL search engine no luck on finding them, can someone explain =
me both type.

**What i'm doing, since I'm using mysql for a program***=20
**I execute a query=20
**when i get the query , I'm putting everything in a hash dbm
**Then i read the line i want to, I'm doing so because it is easy to =
overwritte info on the dbm which i really want to.

My.ini Optimization
I'm having some performance issues that seem to be MySQL related.

I am running W2KAS IIS, PHP 5.1.4 and MySQL 5.0.21. Memory or CPU speed do not appear to be the problem.

I'm running a phpBB forum and having difficulties editing posts, the install of phpBB seems to be perfectly okay, but when editing posts it either takes forever and times out or just comes up with a blank page after a couple of minutes.

I notice that mysqld-nt.exe will use up about 30-50% of cpu time when this is going on. Code:

Optimization
i have a little complex query that involves sub queries upto three levels. now thing is that , i think, mysql evaluates the sub queries every time that query is evaluated, whereas, i know that results for the third and 4th level queries are same for some number of queries. can we force mysql to store the result of the subqueries to be used later, instead of reexecuting the query.i studied mysql query optimization but, finally concluded is that things over there are just how mysql optimizes queries, not how can we optimize the query to be performed better. any resources for query optimization, i mean good resources ?

MySQL Optimization??? Help!
im recently been suspended daily by my hosts, saying that im causing a high load on my server with around 500,000 requests daily!!!

Now they are saying "usual" reasons for this are databases, and i run a big phpbb forum. Unfortunatly my knowledge of MySQL is limited, very limited in fact, so i cannot make optimization changes what-so-ever. Iv asked for help at phpbb official support......no answer.... however from browsing, i found "modded" forums can sometimes cause over 100 requests per post, but it can be optimized...... so im looking for help, tips, or anything you guys can give me to combat or fight this problem!

I have access to phpmyadmin, the forum db size is around 100mb, 5,000 members, not all active!

and i also run a topsite list!

if i cannot do this myself im willing to let a mysql expert have a look round, see if thye can fix this, and ill pay them to do so, if must be but my budget is limited, otherwise id just move to a new host