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 Complete Forum Thread with Replies
Related Forum Messages:
Optimizing Slow Queries On Millions Of Records
I have a table that has millions of records in it. About 100 records are added every 5 minutes (one per OIDID) (the sample provided below has data for 2 OIDIDs (99 and 100) And I have a webpage that executes 9 queries one after the other, and then displays the results on the webpage. When the database was empty, this process was very quick. But, as the DB grew, it became slower. Now it takes about 38 seconds for all queries in the example below. MySQL 4.1 on Windows I have tried different indexes, and they do help and are used, but the queries still seem to take a long time. Code:
View Replies !
Slow Queries When Using Views Compare To Direct Table Queries
I’ve having problems with my server load for a while now. I have two tables with different content, but I need to display them in the same results, so I created a view with a union all (named: top_news_videos). The problem that I’m seeing is that when running a select to the view it takes a lot longer (and in that way, more server intensive) than running the query directly to one of the tables. For example, I created a page where I run 7 queries similar to this one: MySQL Code: ...
View Replies !
Searching In 100 Million Records..
I am working on PHP environment. Right now I am facing a big problem in Mysql. Actually I am having one table with more then 100 million records.. From that table I have to search for particular records.. What happen in my case is.. database been crashed.
View Replies !
Pagination For Million Of Records
I was wandering how the people with big sites do pagination. SELECT bla FROM bla WHERE bla LIMIT 10000000,25; Gets really slow, so there has to be some other way. Note that I'm not asking for pagination on a 1000 records table where limit works, but something with 100milion + records. Any input / ideas?
View Replies !
500 Million Records In A Single Table?
I really need a suggestion from you. I need to store more than 500 million records in a single table. These are basically historical data of different stock market for last 40 years. Initially I thought about partitioning for each company. But there are more than 1024 companies. At the same time if I partition the database based on year then to show the chart for a particular company I need to call almost all the partitions.
View Replies !
Search Over 1 Million Records (7 Columns)
What you should do in case of database with only one table containing over 1 Million records (7 columns) with over 1 GB of size and you want to run search query on it, making it searchable for public. want to search only 2 fields given below: SUBJECT varchar 255 FullText BODY text FullText What you would have done/ran the query to get the BEST possible results. MATCH AGAINST? LIKE? or both or someother option.
View Replies !
1.2 Billion Records And Growing At 2 Million Per Day!
I have 2 types of data: - an entity type data (average ~300,000 entries) - a state/change the entity goes/changes into (typically 4-6 entries per entity. Each with a date/time stamp, result of the change, etc.) This database grows at about 3 million new entries per week! Now, I normalized the data as much as I can. The end results is that I have N entities, and M number of possible states (M is an index to another table, which has indexes to date/time, location, etc. information). Currently, this data represents about 1.2+ Billion records (generated for the last 4 years). When I imported it as is to mySQL, it takes ~5 mins to find all states for a given entity when I do a search. This is because my initial design was: N1 : M1 N1 : M2 N1 : M7 N2 : M1 N2 : M3 N2 : M7 ... Nn : Mm I since redesigned it for better performance. I've decided to use TEXT types. So now, for the final "results" table, rather than multiple N's, there's only 1 N that lists multiple M's in the TEXT field (I know that this is a hack but performance is now much much faster!) i.e.: N1 : M1,M3,M17,..,M232453,..Mm N2 : M4,M76,M543,...,M5456,... etc.
View Replies !
ALTER TABLE :: Hangs For 4 Million Records
I have a somewhat large table (abt 4 million records) and when I do an ALTER TABLE to add a column the database hangs. I have plenty of free disk space and memory. The server version is 4.0.17 and the table type is InnoDB. Has anyone experienced this behavior?
View Replies !
Deleting More Then 1 Million Data Records Faster
can I delete a big amount of data records from my MySQL table faster? I need daily to clear, to make empty my table faster. delete from table_1 takes in some cases (e.g. for deleting more then 1 million data records) about 20 minutes. I mean maybe it would be faster to define a new table with the same structure. The old one (the big one) has to be deleted then.
View Replies !
Load Data Infile :: 11 Million Records And Runing For 3 Days
I am using load data infile to load 11 million records. It has been running for 3 days now and still going. Is there away to check the progress of it, for example how many records has it completed. I could try to query the table, but I would like to confirm if this will lock the table and cause my data load to fail. How is the commit beeing perform on load data infile, is it for every record or does it commit base on some setting?
View Replies !
Too Slow Queries
I am using version 5.0.4. I noticed that suddenly my queries were becoming too slow. I have data in three tables, with 40,000 rows, 50,000 rows and 70,000 rows respectively. I was able to run queries that joined the three tables together and get the results in less than 5 seconds. I tried updating the tables through a program that used a stored procedure to delete the tables but I had some other problems. Since the I restored the data in the tables from backup databases. Now I can't run any queries that join the three tables together, it waits for a long time and then times out. The data in the tables look good. Now I can't get the results from any of the other backup databases either. It look like the entire MySQL server has slowed down greatly. I tried rebooting the server to no avail.
View Replies !
Slow Queries!
I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form. My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot? Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?
View Replies !
What Causes Slow Queries
What causes periodic slow queries? I have checked my slow query logs and for some reason everyonce in awhile, a query thats never slow might be for example, one took 3 seconds to execute and every once in awhile a chat might take 10 seconds of cpu time while rest of the time 0.09...why is it it flexuates so much?
View Replies !
How To Log-slow-queries
I'm running MySQL 5.0. Now I would like to log-slow-queries. With MySQL 4 I used mysqld --log-slow-queries --log-long-format start but now I get following note: mysqld: Too many arguments (first extra is 'start'). Use --help to get a list of available options It looks like starting and stopping now only works with etc/init.d/mysql start
View Replies !
Slow Queries And Optimization
I am hosting a site with an increasing number of members. Letely the site has become slower because of too many queries are being used. Is there a way of asking the server which queries are used most often, as well as asking which queries take a long time to process so that I would know where to start my optimization process?
View Replies !
Slow View Queries
I have a fairly complex view which, when you execute a SELECT * shows all info very quickly (0.24 seconds to return nearly 3000 rows). However, when I start throwing slightly more complex queries at it, things really slow down - an average query can take 40 seconds to execute.
View Replies !
Slow Queries With LIMIT
Working with a large table (1 Million Records). Setting up pagination using LIMIT command. Queries near the end of the table are slow when non-indexed fields are selected (but not included in the 'where' clause). For example: With indexes on id (PK), and type: SELECT id, user_id, name, type, selected, creation_date FROM titans where type = 2 LIMIT 500010,10; +---------+---------+------------+------+----------+---------------+ | id | user_id | name | type | selected | creation_date | +---------+---------+------------+------+----------+---------------+ | 1000016 | 100001 | Uni Ekko | 2 | 0 | 1228149051 | | 1000017 | 100001 | Dynaneedle | 2 | 0 | 1228149074 | +---------+---------+------------+------+----------+---------------+ 2 rows in set (4.17 sec) If I only search on the ID field, the query is much faster. mysql> SELECT id FROM titans where type = 2 LIMIT 500010,10; +---------+ | id | +---------+ | 1000016 | | 1000017 | +---------+ 2 rows in set (0.36 sec) explain SELECT id, user_id, name, type, selected, creation_date FROM titans where type = 2 LIMIT 500010,10; +----+-------------+--------+------+-------------------+---------------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-------------------+---------------+---------+-------+--------+-------------+ | 1 | SIMPLE | titans | ref | avatars_indx2,tst | avatars_indx2 | 2 | const | 381988 | Using where | +----+-------------+--------+------+-------------------+---------------+---------+-------+--------+-------------+ 1 row in set (0.01 sec)
View Replies !
Do Unnecessary Parentheses Slow Queries Down?
I know a person who seriously writes queries like this: SELECT * FROM my_table WHERE ((((my_col = 30))) AND (((other_col = 40)))) I told him all those parenthesis were unnecessary and he responded that he does it so later if he has to add more criteria the parenthesis will be there. I about fell out of my chair that sounds so ridiculous. So my question is, other than the obvious ugliness and being hard to read, is there any other reasons why NOT to do this?
View Replies !
Fine Tune :: Timeout Slow Queries?
I have a very busy database with some tables having hundreds of thousands of records. I am running MySQL 5.0.16 on Windows. Some of the queries running on my database are not fined-tuned enough, and need improvement. However, the system is live in production and I am looking for a way to prevent MYSQL from committing Harakiri, i.e. from running very slow queries forever, taking the cpu to 50% and beyond for hours. My scripting language is PHP. I found that after a while the end user receives a timeout on the browser, but MySQL continues trying to run the query, sometimes for hours. My question is, is there a way to specify a timeout for MySQL so that it will abort any queries that it is running that have not returned a result by then. The end user is no longer around anyway, so I am looking for a way to protect the database resources (and in parallel I will start investigating how to optimize the slow queries)
View Replies !
MySQL Timing Out? Really Slow Queries, Already Indexed.
For whatever reason, MySQL does not seem to be executing. So, I ran a few stat functions and here is what I got: Uptime: 220372 Threads: 39 Questions: 18748899 Slow queries: 808 Opens: 28723 Flush tables: 1 Open tables: 4096 Queries per second avg: 85.078 The "open tables" worries me for a few reasons. First, because it is 4kb (exactly, since it is divisible by 1024, evenly). This makes me think that maybe I am hitting a ceiling. Second, there are a few variables in MySQL that equal this, and maybe it's a cap on something. Here are the variables that equal 4096: query_cache_min_res_unit = 4096 table_cache = 4096 transaction_prealloc_size = 4096 Do any of those have anything to do with this? Am I even close? Are any of those values from mysql_stat a big deal?
View Replies !
Table Gets Slow At Around 60k Records
I've a table that starts getting slow with count(val) and select ... order by when it has around 60k records. Table type is innodb Strange enough, at 50k records the response time is great, but looks like it grows exponentially after that. Its something like 50k records - 0.2 seconds for count(val) 60k records - 3 seconds 100k records - 20 seconds I realize order by and count forces mysql to go through the entire table...
View Replies !
815,000+ Records, DB Table Query Too Slow
I have a database table with about 815,000 records in it, each record containing the source of individual webpages (with all the tags and multiple spacing stripped out leaving just the words). At first my match boolean queries were fine but once I got to around 100,000+ records it became too slow to use for a web-based search tool. I was able to separate the data into 10 tables and then use AJAX to run the match query on all 10 tables at the same time and that has increased performance. My question is, is it possible for me to increase the performance for the 1 table some how instead of resorting to separating the data into multiple tables? Is MySQL the right DB for this kind of setup, would MSSQL or PostgreSQL perform better for larger sets of data? I'm not sure how MySQL 5 clustering works,
View Replies !
Slow Select Using Count(distinct) In A Table Bigger Than 100000 Records
Recently I started using MYSQL in my enterprise. I made a table which has around 100000 records. The problems is that it is really slow.. Im trying to do a query in which I get the number of distinct users per day. This is my query: select date(startedDate) as mydate, count(distinct(Users)) as users from Mytable group by mydate It is really simple and it does it correctly but it takes one minute.. One minute is not too much time but i need to insert around 10 000 000 records and thats what worries me.....
View Replies !
Float A Million
I have a problem making the price: 1.200.000 Until now I was using only prices under one million so it was no problem using: MySQL Table Syntax: `koopprijs` float(10,3) default '0.000',
View Replies !
A Million Rows
i'm setting up a squid log analyser. i wrote a little perl script which copies the log file entrys into a mysql db (with only one table). i get ~5 million entrys a day. so my table gets larger day by day. all entrys older than 30 days are automatically deleted. now i'm searching for a method to collect the data from the sql table to generate a report (perhaps a online report via php). but it's very slow to collect that data (atm ~30 million rows). i tried to collect the amount of connections the last 24h data with the following command: $ToDate = time() - (24 * 60 * 60); print " <tr> <td align="left" class="row1"><b>Total Connections</b></td> "; $result = SqlSelectQuery("SELECT DISTINCT id FROM logfile WHERE time > "" . $ToDate . """, __FILE__, __LINE__); $number = SqlNumRows($result); print " <td align="left" class="row1"> " . $number . "</td> "; well it works, but it takes a while and as result i get "1629258". so about 1 minute to collect that data? ~1.5 million rows?
View Replies !
Are One Million Tables Ok?
This is gonna be a simple question. Hit me with a one-line-answer. The Project: A Community with 1.000.000 users, each user has a mailbox with about 100.000 messages. Now what? Seperate Mailbox-Tables for each of the one million users? Or place all messages in one big hugemungous table? Which Engine to use? MyISAM or Innodb?
View Replies !
Sorting 1 Million+ Rows
I have two tables that each contain over 1 million records of membership data and I wish to output some of this information to create a directory of profiles. 1 table contains username and join date, the other contains the users country of origin and other information for their profile. I "LEFT JOIN" these two tables using their unique user ID, but when grouping/sorting by country of join date, the query can take over 20 seconds which, with a number of concurrent requests, results in my db server (2 x Clovertown 5335 4G RAM, 2x147GB SCSI) running extremely slow and unusable. Is it normal for two tables of this size to take such a long time and could somebody recommend a suitable way to cache such a large result set of which there will need to be several for grouping users by country and ordering by join date?
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 !
2-3 Million Record Table With MySQL
Does anyone have any experience working with very large tables? Say, 2-3 million records? I have the opportunity to work on a new project where at least one of the tables could grow to be that size, and I'm looking for any input on if MySQL is a good solution.
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 !
Delete Duplicate Entry - Over 8 Million Rows
In my table I have more than 8 million rows and has duplication. I have to just delete those duplicate rows. I was trying to find out the ids by SELECT id FROM `fact` GROUP BY keyword_id, time_id HAVING count( * ) >1; But its taking very long time to get the result, so long that I had to terminate the process itself.
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 !
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 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 !
|
TRACKER
