MYSQL :: Tips / Advice For Searching A Database

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.

View Replies !

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.

View Replies !

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?

View Replies !

5.X Optimization Tips
I have a unix box that has 32G of RAM. I want to optimize MySQL because right now it's pretty slow using the default settings. Do you have any configuration settings that you recommend to maximize MySQL

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 512M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 512M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 512M
thread_cache_size = 8
query_cache_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 16
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

Edited 1 time(s). Last edit at 01/26/2009 03:46PM by John Doe.

View Replies !

Performance Tips
I'm working with 5.0.29 and have a model with 35 tables.

When I click a table for modification or move the element into de model my computer lost performance and WB take a while to respond (enought to desesperate in daily use).

Have a tips page to check posible reasons or tips for a better performance?.

My pc have a Intel dual CPU E210 2GHz and 2MB RAM, then I thinks that is enougth for run a normal program.

Please send me tips.

System info from MySQL WM

MySQL Workbench OSS for Windows version 5.0.29
Cairo Version: 1.5.12
Rendering Mode: GDI Rendering
OpenGL Driver Version: Not Detected
OS: Windows XP
CPU: 2x Intel(R) Pentium(R) Dual CPU E2180 @ 2.00GHz, 2.0 GB RAM
Video adapter info:
Adapter type: NVIDIA GeForce 7300 GT
Chip Type: GeForce 7300 GT
BIOS String: Version 5.73.22.62.72
Video Memory: 262144 KB

View Replies !

MySQL Tips And Tricks
If you have an interesting MySQL Tip or Trick, I would be interested in
posting it. The following link (GPU Free Documentation License) contains
advice on such standard features as "showing a process list" and "killing a
particular" process, how to do transactions, interface with Perl, Java and
C, remove duplicates entries in a table, rollback transactions, merge
multiple tables into one, update foreign keys in a multi-user environment,
create a UUID (same as GUID for MSSQL users), monitor connections with
"tcpdump", create a C or C++ API, encrypt and decrypt data (des_decrypt,
and des_encrypt), generate a virtual row count, monitor and show all
"select, insert, create etc. statements" with binlogs, and using Spatial
Extensions like SELECT X(GeomFromText('Point(5.02 7.9)')); with
explanations. Code:

View Replies !

Tips For Improving The Performance Of Mysql
I really hope I can get some general advice and suggestions about how I can improve the performance of our mysql server.

We have a dedicated webhost running a number of quite highly used websites. They all use mysql quite heavily at times. Recently we've had complete mysql gridlock and had to restart the server. What are the steps I can take to improve performance?

This is what i've done so far.

1. Optimise queries - i've been checking the queries we are running and trying to cache the results in a session rather than generating all the time. I've also installed something called mytop which is basically the same as the standard unix top command, except for mysql processlist and shows you the queries currently running and the time elapsed.

2. I have run explain on my queries and put in indexes all over the place to try and make lookups quicker.

Things i'm considering doing

1. Reducing the amount of data in the database and perhaps rotating the tables slightly, our biggest tables are approaching 2 million records and linking these together is i'm sure causing some of the problem, especially when we have to run a SUM query on them, even with good indexes.

2. Moving the database off the webserver and onto its own dedicated mysql server.

3. Load balancing the database and somehow clustering more than one database server, but this would cause serious headaches so not seriously considering unless it was thought the best solution.

4. Upgrading mysql... We are currently running version 4.0.27, should we upgrade to 5? Will this give us performance boost?

5. Re-compiling... After a google search I discovered that potentially we could recompile mysql with a better options set and perhaps improve performance.

6. Buying a book on mysql optimisation Any suggestions?

View Replies !

Any Tips For Improving This Query Time?
Is there anything I can do to improve execution time for this query?

mysql> SELECT webpageUrl, webpageName, COUNT(*) AS `count` FROM _1_log GROUP BY webpageUrl ORDER BY `count` DESC LIMIT 5;
+---------------------------------------------------------+---------------------------------------------------------------+-------+
| webpageUrl | webpageName | count |
+---------------------------------------------------------+---------------------------------------------------------------+-------+
| http://www.w3counter.com/ | W3Counter - Free Web Counter, Web Stats, Live Analytics | 2490 |
| http://www.w3counter.com/stats/visitors/4071/day/1000/0 | Visitors Overview - www.pitbullmortgageschool.com/ | 1496 |
| http://www.w3counter.com/stats/4071/v_daily | W3Counter - Visitors by Date - www.pitbullmortgageschool.com/ | 1130 |
| http://www.w3counter.com/stats/ | Your Websites | 1124 |
| http://www.w3counter.com/stats/websites | W3Counter - Websites | 800 |
+---------------------------------------------------------+---------------------------------------------------------------+-------+
5 rows in set (0.88 sec)

mysql> explain SELECT webpageUrl, webpageName, COUNT(*) AS `count` FROM _1_log GROUP BY webpageUrl ORDER BY `count` DESC LIMIT 5;
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
| 1 | SIMPLE | _1_log | ALL | NULL | NULL | NULL | NULL | 29422 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
1 row in set (0.00 sec)
It averages 0.5-3 seconds throughout the day (it's run often enough that it shows up in my slow query log multiple times an hour for exceeding 1 second).

Adding a partial index on webpageUrl doesn't affect anything, and URLs can be quite long. Is there anything I can do to speed up that query? The table is updated too often to maintain separate url/count tables -- can't spare the resources.

View Replies !

Searching A Database?
Does anyone know of any tutorials or documents which would help someone like myself work out how to search a database and calculate relevancy.etc. I'm creating an auction site and so I want people to be able to search both the title and description of what is being sold. I want the results ordered by relevancy. But I have no idea how I'd work out what's more relevant?

View Replies !

Searching Database With Languages
i have the following situation: table with about 20 columns and two of them are tolanguages and fromlanguages. User uses search form which is basically two fields(to from) and a button.

What i am trying to do is to make a search by this two columns. I need that keywords from to field be searched in column tolanguages and from field in fromlanguages.

I tried full text search but i don't think it should be used in this situation because it doesn't search specific keywords in specific column. I also have an idea of using regexp but don't know yet how;

Example searches would be:
1. To: English; From: Chinese
2. To: Chinese, English; From: Russian

PS my problem lies in multiple keywords like in ex. 2

View Replies !

Searching A String Field In Database
I would like to have someone be able to search a my database. Here is how i would like it to work, but i am having a hard time figuring out how to code it. any help would be great.

So for instance say i have a field labeled "activities" and i will have the user check off boxes to fill this field.

Activities
"baseball AND soccer AND volleyball"

now i want someone to be able to search for "soccer" what would be the best way to search each person in the database for this attribute? Am i looking at this in the wrong way? Is there a better way to do this?

View Replies !

Searching Database With Non Exact Phrase
I am helping someone with a website that was setup, they have a problem searching the database, if you don't put the phrase in exactly as it is in the database, it won't return anything. They would like to search for xxx, and have it return anything that has xxx in the title of the product.

View Replies !

Display Only Last 5 Records Without Searching The Whole Database.
I am currently using this query statement:

select id from tblmain where date = '2006-06-16' order by date, time desc limit 5

This displays the 5 records that I am looking for. However, I just want the last 5 newely inserted records in this database table .. What the problem is that it searches a database with over 9 million records. It only takes about 3-4 seconds.. but I want this query to display faster because I want to insert into a heavy traffic page. I want to take the last 5 inserted records which would be my newest records. Anyone have a better query statement for the same procedure without having to search through all the records?

View Replies !

Searching Database InnoDB & Sphinx
I have a MySQL database (InnoDB) that I need to search. I have been trying to output all of the data, they are short news briefs, to a single page and then having Sphider search that page looking for relative links. I am not quite getting the results I need, plus I am worried about scaling problems with this approach.

I have looked at Sphinx but this application runs on IIS6 and it seems that Sphinx will crash when multiple searches are being run on IIS6.

What do you do when faced with doing full text searches on MySQL databases? Is it a problem to convert to MyISAM?

View Replies !

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?

View Replies !

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.

View Replies !

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.

View Replies !

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?

View Replies !

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.

View Replies !

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.

View Replies !

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?

View Replies !

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.

View Replies !

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.

View Replies !

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

View Replies !

Query Optimization Advice?
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.

Any suggestion to optimize it? Or should I work on a completely different approach?

View Replies !

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

View Replies !

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?

View Replies !

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)

View Replies !

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.

View Replies !

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.

View Replies !

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.

View Replies !

Advice On Converting SQL Server 2005 Express To My SQL
So we have a brand new database which is not being used yet. There might be a few testcases of data, but since it's not live yet, anything there is not important. Here is the setup:

The SQL Server express database is broken into 3 distinct functional schemas:
1. Main and Worksheet Archive
2. Validation Method Archive
3. Raw Data Backup

Each database has a unique table structure. As a result, they can be located in the same owner/schema within the same database. I believe we use a single database. However, because each database can grow at different rates, we do have the option of storing them in separate databases.

The tables for each database/schema can be created using scripts provided by the company that created this Lims system. These scripts are for either SQL Server or Oracle database types.

Connecting the Lims application to the database uses 'Microsoft OLE DB Provider for SQL Server'. We can also connect to an Oracle Provider, but we are not using Oracle, just SQL Server Express.

We are running the SQL Server express database on Windows 2003 R2. The Server itself is a Quad Core Intel Xeon X3210 2.13 with 4 Gigs of Ram. The Lims application we've bought will only run on Windows operating systems.

So, after a lengthy introduction to our setup...onto my question.

1. Should we convert the database, or try creating a MySQL database from scratch?

2. I've seen sticky's and posts on converting SQL Server to MySQL...since I know nothing about these databases (but I am willing to learn), do you think I will be able to convert this database?

3. If I can convert it, what is the best method? There are many commercial products I've found...will they work? Or should I use another method like a script to convert the database?

View Replies !

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

View Replies !

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)

View Replies !

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.

View Replies !

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.

View Replies !

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

View Replies !

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?

View Replies !

Searching A Row
i need to be able to do a search using the "LIKE" instance through all of them, but id was hoping to avoid having to do something like this:

first_name LIKE "%$form{'FirstName'}%",
last_name LIKE "%$form{'LastName'}%",
company LIKE "%$form{'CompanyName'}%",
street1 LIKE "%$form{'Street1'}%",
street2 LIKE "%$form{'Street2'}%",
etc....
i would have to do this like 40 times to have it search everything.

View Replies !

Searching On Ã½
I need to find all the instances of Ã½ in my database, but not y. So, for example:

SELECT * FROM listings WHERE descritpion LIKE '%Ã½%'

produces anything with a y in it. How can I get only those records with a Ã½?

View Replies !

Help With Searching WHERE/LIKE/OR
I tried to search this on the forum, but 'where', 'like', and 'or' aren't the greatest of search terms...

Given a keyword, I need to search several fields for matches. I can use %$keyword% format.

This is what I have been trial and erroring:

$query = "SELECT * FROM tablename
WHERE field1 LIKE '%$keyword%'
OR field2 LIKE '%$keyword%'
OR field3 LIKE '%$keyword%'";

Can you see the error here?

View Replies !

Searching For Records
eg search for records having same email address

select txtemail,count(txtemail) from `tbl_user` GROUP BY txtemail
having COUNT(txtemail)>1;

and it works, is that the best way?

View Replies !

Searching Mysql.com
A comment was made something to the effect
that such a line in /etc/mysql/my.cnf could be
used to restrict network connections to mysql
to a specified IP address. I'm not quoting exactly,
but the idea is that if it is desired to connect to
mysql tables via say an ODBC connection, a line
like bind-address = 192.168.1.175 would allow
connections from a client pc with that ip addr.

What if I wanted to allow 2 PC's on the LAN to
connect to the mysql server on the linux box?
I couldn't find help on mysql.com for this objective.

View Replies !

Searching By Tags
I have three tables: `photos`, `tags` and `tags_photos`.

The `photos` table contains a unique ID and a bunch of other stuff, the
`tags` table contains a unique ID and a tag name, and the `tags_photos`
contains the photo ID and the tag ID for an association between a tag
and a photo.

In other words, any photo may have many tags and any tag may have many
photos.

This is great for filtering photos based on tag, but I'm now trying to
allow users to search photos on multiple tags. I want users to be able
to enter a list of tags and either search for photos with "all these
tags" or "any of these tags".

I tried the query "SELECT a.* FROM photos AS a, tags_photos AS b WHERE
b.photo=a.id AND b.tag IN ($tags)" where $tags is a comma-separated
list of the tag IDs from the tag names the user entered, but of course
it returns photos multiple times if they have more than one of the
entered tags.

Any ideas?

View Replies !

Searching Algorithm
I'm developing a database for a research firm which has data they want
to search for specific criteria against varying base line data - some
of the questions that have been asked to get the raw data (which I
will import into MySQL) are like this:

how often do you use mouthwash?
etc.
etc.

The responses for each question was structured with only 4 answers
from a large group of people:
no answer
low
medium
high

Using various baselines of people, I want to build a query that will
accurately find matches and to also allow me to find 'close' matches
- for example:

if base line #1 is for a consumer who uses mouthwash a 'medium' amount
of times per week, I want to find in the database how many 'hits'
there are for that question - but I also want to capture 'close' hits
like in this example, for this specific question, if the search finds
'low' instead of 'medium' I'd like to count that as a 'close' hit vs a
direct hit (match) - then when the query is over, I'd have say, 80%
matches with (for example) 60% of those direct hits and 20% close
hits - I'm using PHP so I can use that to keep track of direct hits
and 'close' hits.

View Replies !

Searching Archives
I just joined this list and would like to search the archives before I ask a =
question. I there a way to search.

View Replies !

Searching On Two Keys With OR?
Which version is targetted for optimization of OR searching on two keys,
that is, "select * from sometable where f1 = 123 or f2 = 123",
as described in http://www.mysql.com/doc/en/Searching_on_two_keys.html
?

View Replies !

Searching On Two Keys With OR
Which version is targetted for optimization of OR searching on two keys,
that is, "select * from sometable where f1 = 123 or f2 = 123",

View Replies !

Fulltext Searching
How fulltext is working!!?

I have the following table with just one record! And when I do a select statement, mysql returns no hits!!!

Would you explain to me what is wrong?? Code:

View Replies !

Searching Blob
is it possible to search through blob data similar to full text searches?

View Replies !

Text Searching
I am using normalization on my database tables so that I can related each table to each other.The only problem is that I am using a full text search on 4 fields. These include: descrip, title, brand, category.
Now would I have to have these 4 fields in one table or could I normalize them and make split it up into 3 different tables as such.

table 1) descrip, title, brand_id, cat_id

table 2) brand_id, brand

table 3) cat_id, category

Once I have done that then would I still be able to perform a full text search on the 4 fields that I had mentioned above or would I have to keep them as one table.The main reason why I want to split them up is because in the brand and category fields I have alot of duplicate entries. Infact about 50 or so percentage of it is duplicate.I just want to try and make my dataset as small as possible as I will be having well over 2 million rows and I need the search performance to be fast.

View Replies !