Big Select Vs Small Ones
I have an array of 10 IDs.
I need to query the db for values associated with these 10 IDs.
In a lot of places, I have seen people using a php foreach like this
foreach (ids in array)
{
query = SELECT values FROM tables WHERE id = [id 1 in array]
do query
store values
}
we could build one big select statement like this:
query = SELECT values FROM tables WHERE id = [id 1 in array] OR [id 2 in array] OR [id 3 in array].... until we have ids.
View Complete Forum Thread with Replies
Related Forum Messages:
Handler_read_rnd_next = 13k On SMALL Db
I have two very small DBs. One for a little number game, which only 6 people hit 1-3 times a day. The other is for a prototype of a game I'm working on, and there are no more than 3 users on this. Using phpmyadmin, I check the page with runtime info and I see Handler_read_rnd_next 13 k If I reload this page a couple times, it goes up to 14k. Most of the queries I use look like Code: SELECT c.color, b.percent FROM tbl_creatures a, tbl_creatures_colors b, tbl_colors c WHERE a.creature_id =1 AND a.creature_id = b.creature_id AND b.color_id = c.color_id And the EXPLAIN SQL for these comes up with Code: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE b ref PRIMARY,color_idPRIMARY 4 const 2 1 SIMPLE c eq_ref PRIMARY PRIMARY 4 hq_proto_creatures.b.color_id 1 To me, this all looks fine. I can't tell if all the Handler_read_rnd_next is due to the phpmyadmin status checks or not.
View Replies !
Small Databases
I am wondering if having a database for the client back office, a database for the static website and a database for website stats would be a good idea. The client database would hold credit card info, user info, reg info, project info, client messages and more. The website database would carry all the content for the pages of the public website like about us, contact, home page, news and such. The website stats database would keep track of user agents, new hits, returning hits, keyword searches made on the client websites and all that jazz. Should I make this all ONE giant database or three smaller database?
View Replies !
One Big Table Or Many Small Ones?
I am designing an interactive website consisting of a few pages and I want to track individual user progress. I was wondering if it would be better to create one big table for all values on all pages (Which may have a lot of columns, easily hundred or more), or created a separate table for each page (so I could have some 10 tables with 10 columns or more). Or does it not matter?
View Replies !
One Big Database Or A Lot Of Small Ones?
My boss is implementing a system where a lot of different sites will be using the same exact database structure. He has it in his head that things would be simpler with one database shared by hundreds, possibly thousands of sites. I feel like each site deserves it's own database, even if it involves more maintainence.
View Replies !
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.
View Replies !
Backup A Small Database.
I'm looking to backup a small mysql database. The problem is mysqldump does not exist on the server I'm trying to export the database from. Does a php script exist which I could use to do essentially the same thing as mysqldump?
View Replies !
Small Favor Gone Wrong
I have a VPS account and I am trying to move my sister's professional organizations site onto my server more or less as a favor (getting paid a little - but not really worth my time). It is being hosted by a professional hosting company that supplied me with ftp access to download the site for the move. I am running into a problem because I don't know how to set-up/use mySQL through UNIX. Previously I have used the Plesk interface provided with my VPS account. Plesk (or at least my hosting services configuration of it) won't allow me to create the user I need in order to import the database because the user ID is contained within the user PW. (this is the database coming from the vendor). The original host has been unhelpful at best so I thought before I go to them and ask for assistance (that I am pretty sure I won't get anyway) that I would post here to find out if there is any easy way through this roadblock or if any the unix commands are simple enough, how I would proceed that way.
View Replies !
One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables. I need to gather information from various devices (about 500). Each device has its own Id and some data. Should I use only one table with an indexed column for the ID and another column for the data, or should I use 500 tables each with only one column for the data?
View Replies !
MySQL Upload Small Files
I'm uploading small files (>10K) and mysql is deleting it. The one field is a blob field and everytime i test upload a file thru phpMyAdmin within 2 seconds mysql deletes the row. Theres nothing in mysqld.log, there another log i should be checking out?
View Replies !
One Big Query Vs Lots Of Small Queries
I am in the process of migrating a MySQL database from one schema to another and am writing a script to extract the info from one table to be inserted into multiple tables on new new db. My question is this, is it better for me to make one giant query (about 1 million records returned) on the source table, manipulate the data, then enter in the data by cycling through the results, or would it be better to split up the query on the source table into lots of smaller queries with a short pause in between?
View Replies !
Structure Of Small Social Network
I need to setup a social network type mysql database, but not as intense as friendster, thefacebook, myspace, etc. i have a structure set up but it think it's weak and extremely limited: Code: +---------+-----------+ | user_id | friend_id | +---------+-----------+ | 5 | 4 | | 1 | 7 | +---------+-----------+ i fear it's going to be a lot of redundant IDs in a column, which i think could be a problem when retreiving friends: Code: +---------+-----------+ | user_id | friend_id | +---------+-----------+ | 5 | 4 | | 1 | 7 | | 1 | 9 | | 7 | 9 | +---------+-----------+ i want to do a "bob is friends with john" and a "jackie knows sally through richard" (aka friend of a friend). that's it and nothing more. how do i structure mySQL database to be able to this?
View Replies !
One Huge Table As Appose To Many Small Ones
i have a very large XML DB (60gb+ and growing on a daily basis) which holds complete life cycles of stock option , i used to hold it all in folders with each folder named like the option paperId so it seemed logical to me to hold it now in diffrent tables for each paper even tough the are all the same exact table , now i'm having some problems with hibernate (one of the biggest reasons i left XML) and so many tables , is it more effiecent to keeeping data like that ? ofcours i will have to change the PK from datetime to paperId+DateTime if i decide to change to one big table ... how can i calculate diffrence in retrieving data ?
View Replies !
A Huge Database Or A Lot Of Small Databases
I have a question regarding database performance. Which of these two designs would be better: -Having one huge database with 50 tables or so (each table having millions of rows) or -Having a lot of databases with the same amount of tables, but each table would have only thousands of rows *The mysqlserver is running on a P4 3.0 ghz with 1 gb in ram, but I may upgrade my server soon. *Privileges to create databases is not a problem since I own the server
View Replies !
Several Small Calls Vs One Large Call
I am working on a php interface to a mySQL db which will pull lots of rows from the database. Assuming I am selecting a hundred rows by their auto_incremented ID's, is it better to do this with a loop of 100 "SELECT ... WHERE id=" calls, or to do it with one single "SELECT ... WHERE id IN(...)" call, with IN() holding a list of the hundred items.
View Replies !
One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this. My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group. If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?
View Replies !
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.
View Replies !
Small Database, High Load
I have a very heavy mysql database used website. Data is constantly being updated and at peak there is well over 1,000 people online, after it hits the 1,000 mark, the website starts to lag pretty bad. My database is only small at around 90mb. We have a web server for the files, and a database server for the database. The load on the web server is fine. But the database server is pretty high. Would replication reduce lag and load? if i was too add a second database server?
View Replies !
Inserting Small Image Directly Into Database
How do you insert an image into a table directly using something like data studio? I have tried googling it and it seems all the examples are using a language to do it for you. The images are between 2-4k and the table is just an int column followed by a blob column. I just thought the statement would go like INSERT INTO sometable VALUES (1,'image.jpg'); The web suggests uploading and downloading in PHP (which im using) but im building the data first using data studio then just displaying the table information using PHP. Before i go into displaying the information on the page i just wanted to make sure the insert query is correct.
View Replies !
Small SQL & Php Webpage Field Database Task
In a webpage, imagine a little section where the user fills in his/her contact details to register. Among this section is a field called "Country". The task is to make this field automatically generate the persons current country of residence. I am told, that the IP ranges and the country to which they belong is in a CSV .xls file. I am also told that the best way to do this is to: 1) Insert the CSV into database table (MySQL). 2) Use php to get that country name onto the website field.
View Replies !
Large .sql File (break Down To Small Parts)
i have an oscommerce installation that generates a 200mb sql backup file. i want to try and rebuild the installation on my local pc (localhost) to emulate and play with, unfortunately the 200mb file is difficult to handle. phpymadmin seems to crash. i have console access on the host however. is there a way i can break down the sql file into smaller increments for easier uploading?
View Replies !
How To Reset Yahoo Small Business MySQL Database
I am a new user of "Yahoo Small Business" web hosting service and I changed a lot of settings in my "MySQL Database" using "phpMyAdmin" utility during my learning period. Now, I want to reset my Yahoo Small Business MySQL Database to default settings or to reinstall it (I want it to be the same as it was newly activated). Note: I tried to repair my database with Yahoo's "Database Setup" page and the message "Database scheduled for repair" appeared for a few days but nothing happened. And, I can not login to my database using "phpMyAdmin" utility. If you can help me to reset my "Yahoo Small Business - MySQL Database" I will be very pleased.
View Replies !
Small Tables Or A Few Big Tables
My Web site is going to use a MySQL database extensively throughout the Web site. It is a content management system type Web site with lots of users and written articles and other things. With my database I'm trying to figure out if I should create big tables that have a lot of rows in them or if I should create a lot of tables that are very specific to its data and use a lot of table joins in my select statements. I'm mostly concerned about performance. I'm thinking that if I make a lot of tables, then select statements I do will be really fast because my tables won't contain very many rows. But I don't know if this is true or not or if it matters for performance reasons or not. In my Web site I have a lot of different kinds of data that could put in many different tables. But, without data redundancy and without using extra storage space, I can still make a few big tables, and make that work. I just don't know which way is best for performance -- speed, I want speed. Anybody know what I should do or know a book I should read or a Web site to go to?
View Replies !
INSERT INTO SELECT Taking Forever But SELECT Part Is Fast?
I have the following mysql code to insert about a million rows. This takes quite a long time (over 50 min). However, when I just run the query alone without the INSERT command, the query itself only takes under 5 minutes. Is there a way I could speed up the inserting?? insert into tblc select tbla.* from tblb inner join tbla on tbla.issuer_id = tblb.issuer_id;
View Replies !
SELECT INNER JOIN Performance VS Single Table SELECT
I have designed my database using a somewhat oriented approach. Rows are objects, and different type of objects are in different tables, but since my objects share a common set of fields like ExpireDate, Archived, Draft, CreateDate, CreatorID, etc; I have an 'objects' table with these columns, and I have set up a foreign key in other tables where there is a need for a row-to-row integrity. Now my question is, since I have to SELECT using INNER JOIN with object and the corresponding table, merely to filter out archived rows, I am wondering if I would be better off actually putting the common fields into each respective table and get rid of the 'objects' table altogether. I mean, is it more job for the server to actually JOIN the tables for each SELECT versus having a clean design with object oriented approach ? How bad is my design, and what are the recommendations of experts who obviously were tempted to create an object oriented database design?
View Replies !
Select Statement Question (nested Select?)
I have a DB containing 3 fields fullname, inext, and outext. I need to see all the records that have a duplicate entry in inext. I know I can do a distinct query on the inext column but that only gives me the unique ones I need all the different duplicate records.
View Replies !
Speed Diff Between Select * And Select Column
I have this question that I cannot find a right answer among my friends. Will like to know: Assuming I have a table with 8 columns. When I am querying these records with specific WHERE conditions, will SELECT * be faster, or SELECT (3-5 x columns) be faster?
View Replies !
Combining SELECT Statements Into One SELECT Statement.
I want to take the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN authuser ON name.empnumber = authuser.uname AND authuser.team = 'PHQ' ORDER BY name.lastname, name.firstname; and the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN crew_attendance_6QJ ON name.empnumber = crew_attendance_6QJ.empno ORDER BY name.lastname, name.firstname; And combine them into one query that outputs all of the results both queries would output. Then order those results. So far I have come up with:
View Replies !
Select Within Select Needed To Get This Query Going?
I'm not sure how to go about this, but I am thinking I need some sort of a select query within a select query to get this properly displayed... My table structure is as follows (simplified, of course): payment_id | member_id | completed | amount | trans_added and I am trying to get the following data out of the table - a daily sum of all the transactions that were 1) completed, 2) by a member who already has an existing completed transaction (completed = 1) on a different date (or members with a count of all completed transactions > 1 will be included only). so far I've got this query, but I don't know where/how to plug in the condition for the members/completed/etc: .....
View Replies !
SELECT Doesnt Select Anything WHERE Doule
I am running the next script SELECT * FROM flcc_Tickets WHERE 'Match No'=1; but dont get any results although there are lots of resords with 'Match No'=1 in a table. 'Match No' is DOUBLE . if run it with 'Match No'=0 i get all the table.
View Replies !
Nested SELECT :: Select All Years Except Last
I'm new to MySQL and want to do a nested SELECT statement. i want to get all years stored in the database except the last year: (content is the table and null values exists) SELECT DISTINCT YEAR(content.date) FROM content WHERE(((YEAR(content.date)) IS NOT null)) AND YEAR(content.date) < (SELECT YEAR(MAX (content.date)) FROM content)) ORDER BY YEAR(content.date) DESC What is wrong
View Replies !
What's The Best SELECT In This Case / How Can I Make This SELECT ?
well, step 1, I need to get a few ids like: PHP SELECT id FROM $table_one WHERE this_field=$that_value // this is OK I will get multiple rows from this command Then, I need my main SELECT command (that i need to use) like: PHP SELECT * FROM $table_two AS t2 LEFT JOIN $table_three AS t3 ON t2.pid = t3.pid WHERE t2.pid = (all the ids that I have got from my first SELECT)
View Replies !
Select In Select Statement
Could any give me an example of how this works please as for the life of me cant get any results.Basically I have 3 tables, I record home team and away team results in the RESULTS table and up and coming fixtures in the FIXTURES table. Im wanting to get all my home teams previous results where they are playing at home in an upcoming fixture but also want to return for the same fixture the away teams previous results, I dont even need TEAM table to be honest but thought may be easier to create a join between the two tables.
View Replies !
Select With Select In Where Clause
This is a very simplified SQL statement intended to reproduce an error, not do anything interesting: select * from value where id in (select id from value where id=1000000); It fails with: ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select id from value where id=1000000)' at line 1 However, the following works select * from value where id in (1000000);
View Replies !
Select Constraint (select ... Where X) If 'where X' Isn't Specified
I'm working on a site where records (say articles) can exist in one of three states: active, archive, trash. I'm doing this so that I can implement soft delete and non-destructive action. For some brief background, we used to simply have an 'archive' column with enum('n','y'). Which meant that every query had to be appended with "where archive='n'". Later we modified the architecture of the application be more robust so that you could publish records (basically duplicate the records into a table called articles_instance; so you can publish as many versions of a master copy as you want). This worked well enough that when I decided to implement soft-delete, I decided to move the deleted record into the articles_instance table. I created a new column ('state' again) with 'publish', 'archive', and 'trash'. So whenever I query the instance table, I opt in "where state='publish'" instead of having to opt-out, like before ("where archive='n' and trash='n'". This was better, but I realized that I had to save the old record id in a column 'old_id' because I wanted to be able to send the row back into the regular 'articles' table if the user chose to undo the action or unarchive an article. I decided to keep the old id, rather then send it back with a newly generated id because there were certain relations I needed to keep, like associated audio files with the article (temporary broken links aren't as much of a problem because you can only see the audio when you look at the article; when the article is permanently deleted, then I can remove the relations). This approach works, but I feel it's messy, and with all the code I'm writing to shuttle the rows back and forth, I feel as if there's only more chances for error. I like the simplicity of simply setting a state in the master 'articles' table. But then I have the problem of having to specify "where state='active'" in all of my queries. If I don't do that, I risk pulling all possible articles, regardless of state, causing strange problems. So here's the latest breakdown: The articles table has a 'state' column with enum('active', 'archive', 'trash'). The articles_instance is now called 'articles_publish' and no longer has a state column. So it is used exclusively for published articles. It boils down to this: Is there a way, in MySQL 4 (I don't know the exact version, but I can find it if necessary) to constrain/modify all of my select statements to have an implicit "where state='active'" if a state isn't explicitly specified? If I could do this, I feel I could have the best of both worlds: have two tables with specific needs, where one table has data that can exist in different states, and I wouldn't need to move rows back and forth, possible causing problems. And if I implement the constraint, I don't have to worry about going through and changing a lot of existing code and making sure the problem doesn't crop up in the future. I am providing all this background in the hope that someone might just suggest a better and more efficient way of doing this and tell me that a select constraint is entirely unnecessary . I might also be convinced that using MySQL as a crutch in this case might be a poor idea and could potentially confuse programmers down the line (although there's only two of us). If it's not already clear, I'm new to all of this, so I am not really familiar with best or standard practices when it comes to these sort of things. If anyone wants to refer me to any resources (books or articles) about versioning/publishing records or database design in general, I'd love to hear them. Thanks!
View Replies !
How To Use Previous Select Query Results In New Select Query?
I have tested everything and it works OK in its current form. However, what I need help with is the part of the query highlighted in red. As it currently stands, the PHP while statement loops through the rows and echos out the html. The issue I have is that it echos out the same speaker name and subject type for each iteration. Now I know this is happening because I have set subject.event_id = 1, so what I need is to use the event_id of the current iteration as the clause and that is what I need help with....
View Replies !
Row Count Mismatch In Select Count(*) And Explain Select Count(*) From Table
mysql> explain select * from parameter; +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | parameter | ALL | NULL | NULL | NULL | NULL | 3354 | | +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> select count(*) from parameter; +----------+ | count(*) | +----------+ | 97 | +----------+ 1 row in set (0.00 sec)
View Replies !
Count(*) In A Select Returns "1". It Should Behave Like Select Count(*)
i'm trying to make a query work properly but I got lost: SELECT *,count(*) FROM cancons c, musics m, discos d, r_discos_cancons rdc WHERE c.c_id_music = m.m_id AND rdc.rdc_id_disc = d.d_id AND d.d_id_music = m.m_id AND m.m_id = 24 GROUP BY c_id note: cancons (ca) = songs (en) discos (ca) = cd's (en) music (ca) = musician (en) don't worry for the WHERE part. i need it because of the foreign keys. this query returns a table with the title of the song and some more data. on the right side, I get another column called "count(c_id)" with the number "1" in it for each row. That's supposed to be due to the "group by" clause, I think. I'd like to get the table with the songs, as usual, and, with the same query, I'd like to get the total number of rows selected.
View Replies !
Don't Select Rows That Match Both Columns But DO Select Rows That Don't Match Both
I suppose it is because it is 2:30 AM but I'm having a bit of trouble figuring out the SQL I need to write. I am usually pretty good at this. Here's what I'm having trouble with. My app includes a Poll system built from scratch. Each question will run for one month. the month that the poll will run is kept in 2 columns in my table... runMonth and runYear. What i'm having trouble doing is selecting the rows that don't match both the current runMonth and current runYear. My Table... id.............Question...........runMonth...........runYear 1..............QA...................1......................2007 2..............QB...................2......................2007 3..............QC...................3......................2007 4..............QD...................4......................2007 (current month and year) 5..............QE...................5......................2007 6..............QF...................6......................2007 I can simply get the question for the current month... SELECT * FROM table WHERE runMonth = 4 AND runYear = 2007; but if I try to get all others using SELECT * FROM table WHERE runMonth != 4 AND runYear != 2007 I get an empty result set. As I should because there is nothing there that the runYear != 2007 doesn't knock out. The result set I am looking for is id.............Question...........runMonth...........runYear 1..............QA...................1......................2007 2..............QB...................2......................2007 3..............QC...................3......................2007 *********************(remove runMonth4 and runYear 2007) 5..............QE...................5......................2007 6..............QF...................6......................2007 What am I missing?
View Replies !
SELECT With MAX
This select question has been bothering me for a long time now. SELECT t1.artnr, t2.photo, t1.access FROM lundhags_product AS t1 LEFT JOIN lundhags_photos AS t2 ON t1.uid=t2.product_uid WHERE t1.access <= 1 GROUP BY t1.uid ORDER BY t1.artname Problem is that I have many photos connected to one product and I want to fetch the photo with the newest creation_date. With this question I get (for some reason) the photo that has the oldest creation_date. What I want is to put in something like this in the question: WHERE t2.creation_date = MAX(t2.creation_date) but that doesnt seem to work.
View Replies !
Sql Select
I use the following line to create a recordset. The s4, in the LIKE statement, is text of a filepath and some of the files have an ( ' ) apostrophe in the file name. The code will not add those files to the recordset. Is there a way to get around this without changing all the filenames? rs2 = App.eBookDB.SQLSelect("SELECT * FROM eBooks WHERE FilePath LIKE '%" + s4 + "%'")
View Replies !
Which SELECT Is Better?
I need to do a SELECT query that joins four tables. The largest of the tables could eventually have over 1 million records. I need to do a SELECT that returns a maximum of 1000 records which will display on multiple pages. Which way would be more efficient.... 1. Do the SELECT and LIMIT it to 1000 records, then relying on MySQL's caching, do a data seek to jump to the record that will begin each page. As I understand it, MySQL will do the actual query only once and the use the cache to return the results each time the exact same query is sent to it. or 2, Do the SELECT and LIMIT it to only the beginning and end records for that particular page (about 20 records). It is my understanding that MySQL will only cache the results for that particular page and each time a new page of records is accessed, MySQL will have to run the entire query again.
View Replies !
Sub Select
I am using version 3.23.58 and need to do the following: select * from a where id not in (select tbl_id from b where tbl="a"); Given that my version does not support sub selects, how can I re-write the statement to get the desired results?
View Replies !
|
TRACKER
