Mysql_fetch_array() And Large Query
I am running a SELECT query on a table of about 1.6 million records.
The number of rows I get back is about 1.5million. SQL runs fine, in command line and php, but when I try to move data from 'resource' to an array with mysql_fetch_array() my browser stalls.
So my question is, am I overflowing the buffer somewhere, and if anybody has a clue how to fix this.
View Complete Forum Thread with Replies
Related Forum Messages:
Can The Result From Mysql_fetch_array Be A Url?
$result = mysql_query("select * from work_request"); echo "<table border='1'> <tr> <th>Id</th> <th>Subject</th> <th>Date</th> <th>Submitted by</th> <th>Requested by</th> <th>Description</th> <th>Priority Level</th> <th>Status</th> </tr>"; while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['id'] . "</td>"; echo "<td>" . $row['subject'] . "</td>"; echo "<td>" . $row['date'] . "</td>"; echo "<td>" . $row['submitted_by'] . "</td>"; echo "<td>" . $row['requested_by'] . "</td>"; echo "<td>" . $row['description'] . "</td>"; echo "<td>" . $row['priority_level'] . "</td>"; echo "<td>" . $row['status'] . "</td>"; echo "</tr>"; } echo "</table>"
View Replies !
Mysql_num_rows & Mysql_fetch_array
I've been using a PHP coding package to get to grips with PHP & MySQL. These scripts work perfectly on my "home" machine, however when I try executing them on my web server and type of MySQL access causes an error. Two such errors are shown below. Could somebody explain why these problems are happening, please...... Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:Inetpubvhostsmilliondollarmotorpage.co.ukhttpdocsedit_page.php on line 93 Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:Inetpubvhostsmilliondollarmotorpage.co.ukhttpdocsedit_page.php on line 97
View Replies !
Mysql_fetch_array Will Not Return First Data Row
Can someone please explain to me why this will not return the first row of data? $result = mysql_query("SELECT * FROM users WHERE id <> 0"); $row = mysql_fetch_array($result); while ($row = mysql_fetch_array($result)) { echo $row['nick']; echo "<br>"; echo $row['id']; } When I run this, it will display the requested list, but it will not include the first row. Why is this?
View Replies !
Mysql_fetch_array With While Loops Infinite
i've made a database class and when i call the sql_array() function in the class it gives me the array but when i try to print the data in the array it prints an infinite loop and nothing else. i tested sql_numrows() tells me that there are 9 records in that table. Code:
View Replies !
Script Error - Warning: Mysql_fetch_array():
I am getting this error on my page: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/three12/public_html/index.php on line 191 In index.php lines: 184 - 192 <td height="25" align="left" colspan="5" class="tabhdblue" style="background-image:url(images/table_hd.gif); border-bottom:1px solid #C2C2C2;">Job Listing</td></tr> <? $l1=0; for($i=0;$i<20;$i++) { ?><tr> <? $sql="select * from free_cats limit $l1,5"; $result=mysql_query($sql); while($row=mysql_fetch_array($result)) { ?> The table does appear in my database and all connection details are correct. while($row=mysql_fetch_array($result)) <---line 191 .
View Replies !
Mysql_fetch_array Fails Bit Does Not Send An Error
while($n<$rows) { echo "<tr>"; $query="SELECT * FROM `survey` WHERE `id`=$id"; $data=mysql_query($query) or die ('<b>ERROR 5: unable to retrieve data from database: '.mysql_error().'. Please try again later.</b>'); $row=mysql_fetch_row($data) or die ('<b>ERROR 6: unable to fit data into an array: '.mysql_error().'. Please try again later.</b>'); //...... //code //...... $n=$n+1; } I get "ERROR 6..." thrown at me, but there is no error message from mysql to display. If it helps, I'm using mysql v4 not v5. If you need more surrounding code, just ask.
View Replies !
Large Join Query
I am at a loss. Right now I have a PHP script that needs to run a huge join query. They query joins two tables of approximately 200,000 records. I'm running mysql 3.x so I can't use a view... at this point indexes don't seem to help. I am thinking the only way to do this is with script processing
View Replies !
Help Me Refine This Large Join Query
$sql = "SELECT f.*, p.post_time, p.post_username, u.post_subject,u.post_text,v.username FROM (( " . phpbb_forums . " f LEFT JOIN " . phpbb_posts . " p ON p.post_id = f.forum_last_post_id ) LEFT JOIN " . phpbb_posts_text . " u, phpbb_users v ON u.post_id = p.post_id and ON v.user_id=p.poster_id) ORDER BY f.cat_id, f.forum_order"; Right now its select all the posts from every forum, I just want it to select the last post from every forum and the second join on phpbb_users don't seem to be working as the user_id doesn't seem to be matching on the poster_id, any suggestions? The query also doesn't work, I want phpbb_posts to left join to two tables, posts_text and posts_topics, how to I do that?
View Replies !
Noob: Select Large Query
If I had to select large number of records, say 1 million from the mysql server. What problems would I be facing? MySQL connection timeout, network latency? Anyone has done a large simple mysql select query and what problem did you face? I am not doing any joins.
View Replies !
Retreiving Large Query Results In Chunks
I'm running queries with MySql 4.0.17 that return thousands of records. Because I need to present them in GUI, I returieve the results in chunks using LIMIT, for example - get first 100, then the range 100-2000 and so on. The problem is as follows: in the first chunk, MySQL uses one strategy to fetch the results, and in the following chunks - a different strategy. This means that records from the subsequent queries might have records that already appeared in the first query or that some records will be left out. For performance issues it is a problem to add a unique secondary sorting criteria (like id) to the query. Is there a clean way to force MySQL to relate to the first (initial) query result set?
View Replies !
Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do: There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns. The most common query is SELECT data FROM table WHERE n1 = ? The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column. Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset: SELECT COUNT(1) AS total FROM table WHERE n1 = 3; index = random number from 1 to total SELECT data FROM table WHERE n1 = 3 LIMIT index,1; How can I speed this up? I need it to take less than half a second if possible. Thank you.
View Replies !
Batch Query Or Large List Of IN() Arguments?
I'm receiving a potentially large list of productID numbers from an external data source. Each productID has a number of partID's associated with it and those partID's are stored in my database. I would like to create a list of products sorted by the number of parts each has. If I only have one table called "part" which stores a partID and its associated productID, which of the following methods would be more efficient assuming the application and DB are on the same server. Option A) SELECT productID, Count(*) counter FROM part WHERE productID IN(large list of productID's received, maybe 1 or >10000) GROUP BY productID ORDER BY counter DESC LIMIT ?, 10 Option B) Execute a batch query of prepared statements (SELECT COUNT(*) FROM part WHERE productID=?) for each product in the received product list then sort the returned results in the Java app that is making the query. Option C) ???
View Replies !
Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions: 1-what can I do to avoid the system stop or time out message? 2- To use several tabels infomation should I use 'View' command or can I use other methods? 3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table? (I use postgresql).
View Replies !
Query On Large Table When Implementing Paging With LIMIT?
I'm working on a paging implementation but I am stuck on a couple of things: 1) I tested with a simple query which yielded this: EXPLAIN SELECT create_date FROM threads LIMIT 0,25 id=1 select_type= SIMPLE table=threads type=index possible_keys=NULL key=create_date key_len=4 ref=NULL rows=57852 extra=Using index I thought that the LIMIT clause would limit the number of rows scanned, why is it still showing 57k rows? 2) Assuming there is a simple fix to the above, now if I want to order by create_date (SELECT create_date FROM threads ORDER BY create_date LIMIT 0,25) I understand that mysql must pull the entire table to sort the data set before applying the limit. So the explain would probably look a lot like the above. In this instance, is there a more scalable solution to implement a sorted paged result set?
View Replies !
Large Db
i have some problem when trying make insert a lot of row in a large db. I have to pharse a 10 file of 1 million of record and insert it into a table ... when arrive at around 1 million of record, every 1000 / 2000 records, the query take 1 or 2 second to make 1 insert, then restart with a normal fast inserting ... in first i think was an hardware problem (like hd with bad sector or similar) but i changed pc and the problem persist the so is slackware 10.1 with recompiled mysql 5.1 the client program that made the insert is a vb application and the odbc is 3.51
View Replies !
Large SQL
Just wondering if there is a better way to view SQL code. Reason I ask this is because I have downloaded a backup of my database onto my local machine (desktop) and the only way I can view the .sql file is in Note Pad but it takes ages to load. Is there any software that allows me to view my msSQL backup?
View Replies !
Large Databases
We are in the process of building an application to front end a database of about 2 million records. I would like to know if MYSQL can handle this and if not, what would be a good database to go with. We would like to use opensource as much as possible, but if MYSQL cannot handle this kind of volume then we will have to go in for commercial software.
View Replies !
Precaution For A Large Db
I have to handle a very big (~50 gigabyte) database (quite all in a table), and I am going to use mysql. Can mysql handle all that ammount of data? What problems I am going to have? Should I take any precaution? Is some other database-engine more suitable for this work? Posgresql maybe?
View Replies !
Large Tables
So i've got a table with 15 million rows. id is the primary key SELECT COUNT(id) FROM listings WHERE id != 0; Takes about a minute to load... which is insanely slow.. anyone know what could be causing this?
View Replies !
A Large *.sql Import
I am trying to import a very large .sql file of about 165mb. When i try to import the file i use "source C:DB.sql" and it runs for a bit and then stops. When i try to query on this table it crashes. But when i do "show columns from DB;" is show me the information correctly. I did update my my.ini config file with: innodb_data_file_path = cseibdata1:10M:autoextend still not working.
View Replies !
Importing A Very Large DB
I have a rather large .sql dump that I was given to work with thats about 3 gig. I cannot get a smaller .sql dump unfortunately. I am using wamp server on my local machine and importing this database will take forever. I have two questions...... Can I optimize the amount of memory or processor dedicated to php or mysql to speed this up? Is there a way to add some sql parameters to the import to only import parts of the database I am working with? One of the columns is a unix timestamp which would also solve my problem.
View Replies !
Large Result Set
I have a question regarding working w/ an extremely large result set - or maybe better yet how I could possibly avoid it. I have a table w/ several million records and I need to loop thru and check each record against an entry in another table and if a match is found write most of the current row's fields out to a file. My problem is when I query for the first set the result set is too large and I blow my memory.
View Replies !
Very Large Tables
I am looking at using mysql to store some particle physics data. So the idea is (for example) I would have a table with a list of events, then a table with a list of particle types (one event can have many particles) and so on. The problem is that quite often I want to calculate something for every single event, but this table might run into 10s of millions of events and many GB. Obviously it would be mad to load the whole result set into memory then iterate over it. My question is: is there any way to have the db push results to the users one (or a few) at a time in an effiecent way? I was thinking of something like an iterator that pulls the data from the disk one (or few) at a time. An alternative (but much less useful) method might be to hand the function I want to calculate to mysql, for example: select myfunc(x,y,z,a,b,c) from events where energy>3; But as far as I know there is no way of defining your own functions in SQL
View Replies !
Very Large Bin Logs
We're getting several very large binary logs, reaching overall over 10GB which is a problem: - When does mysql decide to purge the logs if at all - Is there a way to control the number of bin logs created - If the bin log size is set to a small number with max_binlog_size, will we get lots of small files ?
View Replies !
Large Table
I have a website with over 36,000 registered users. I have a table with 5 million+ records, each of which correspond to a particular user. Obviously some users are tied to a few of these records, others are tied to thousands.I am trying to decide: -Keep everything in the one table, and allow it to grow infinitely large. -Create an individual table for each of the 36,000 users and keep only their records in their respective table.
View Replies !
My Table's Name Seems Too Large :S
If I execute a query to a mysql datasource and select any table that has this name: select * from 2006-catalogo-productos .... gives me an error: 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 ��-catalogo-productos' at line 1 but, i don't have that effect if I select another table of the same datasource, eg: select * from artistas The only difference seems to be that one table's name is larger than the another. I can't change the table's name... ¿any idea?
View Replies !
VERY Large Database
I have such question - how I should split large data for the better performance? Its seems to be old question, but can't find it in search Lets say if I have 10000 items, each have 10000 records - at the start and much more later. Main operations - INSERT/SELECT (search for the records on some item by key) DELETE/UPDATE almost will not be used (because of the implementation) I see few possible options 1. Create 100 databases, in each 100 tables, in each 10000 records (records for the 1 item). 2. Create 1 large table with internal index. As I understand, main difference between 1 and 2, that in case 2 main load will be drived to the mysql and index, in case 1 main load will be drived to the FAT (or whatever else used at the server). But before mysql will use index large file (or its index) will need to be loaded, so in case 2 FAT will be loaded too. IMHO option 1 is better from performance side.
View Replies !
Large DB Restore
I'm trying to restore a fairly large database in MySQL. Either my wireless connection drops or in the last instance, the session stayed logged in, and it gave me a message that i lost connection to the MySQL server(which was local). The restore file is on the server, and the command I'm using is just "mysql < localhost.sql", but after several atmysqlts,
View Replies !
MySQL With Large Table.
I am using MySQL for a table which will have 100M+ records, avg length of records being 130 bytes. When the number of records reach approx. 25M (and the file size close to 4GB), the rate of inserts falls drastically from 800 per second to 30-40 per second. Details: * MySQL 3.23.58 on Fedora Core 3 * Table has 4 indices. * I have got rid of 4GB file size problem with MAX_ROWS=1000000000. * File system : ext3 on single disk. ext3 could create 10G file without much trouble. So I am convinced that ext3 is not the bottleneck. * Tried using InnoDB engine but it also doesn't meet the requirements. Requirements of database: * A single table in the database with 100M+ rows, each of size 130 bytes (approx). * 500-600 inserts per second. * 200 selects and 200 updates per second. (These statements will affect only one row) * 3-5 select statements per minute which can return 10k to 500k records. * No foreign keys/ACID transaction requirements. * Fast recovery in case of crash. Questions: * Does MySQL performance drops when the table grows beyond 4G? * Which are most important server variables which need to fine tuned? Currently I am using only key_buffer = 512M.
View Replies !
Large Record Count
I have a client who wants me to take on a project wherein they have, on another table in another DB, about 1.2 million records. I am going to have to consider, in this project, a record length of about 200 bytes, and am wondering if MYSQL (5.x, on a first-rate Windows server) can handle such size. Does anyone have experience with something that large?
View Replies !
Migrating Large Db From One Server To Another
I am having huge problems migrating large db’s from one server to another. I use phpmyadmin to dump the data into a file, and then migrate it to my production server. Then I try to use this: mysql dbname < filename but if the tables are too large, it simply does not work. So I tried the following two work-arounds, which work but are tedious. Is there a nicer way to do it: Solution A: Create a dump file with subset of all the tables, to reduce size Solution B: Take a zip of the table structure directly, and then copy that. Problem here is that all the tables have to be individually "repaired" so that mysql is in synch with its new data.
View Replies !
Large Test Table
How do I create a very large table, for testing? Can I download one somewhere? I've tried to search google, but couldn't find anything. The contents dosn't need to mean anything, so a random function with a loop or so should do it, but if I could download a large test table somewhere with "meaning" that would be great.
View Replies !
Import Large Database
I saved my website databgase to my home computer. I am setting up a local version of the site, and need to import the database. I have mysql and everything set up. But when I try to run the sql script that I exported from my site's control panel, I get an out of memory error. What is the proper way to import this database? Do I need to increase some memory setting?
View Replies !
Large Table Or Many Tables?
I posted a similar question about 6~12 months ago with no definate answer, so I will give it another go, as the choice has popped up again in my programming life: If the number of entries is the same is it more efficient (better) to have a single table with many columns, or many tables with few columns? The obvious answer is a single table, because there is less closing and opening tables, but the reason why I ask is what about searching through a lot of columns being even more inefficient? The situation is I need to record data for every week of the year. Say ~50 columns per week --> 2600 columns. Or, do I split it so it's 52 tables with 50 columns each? In each of these options the user (or user ID) will be the primary key. The seemingly pointless other option is to insert new columns with every new user, and have the week-item as the primary key?
View Replies !
Reduce Large Sql Queries
I will be working on a project which i can see having quite large tables of data, particularly a transaction table which will store credit and debits from users accounts. Obviously to get a balance I would just need to sum all the transaction on the account and that would produce the current balance. My concern is that the table may become very large and displaying the account balance may end being a large query. Is there a better may to approach this? I know i could have a balance field but I don't like the idea of that in case it become erroneous.
View Replies !
Breaking Up A Large Table
I'm working with a web-based project management app that stores task information for multiple users in a common 'tasks' table. The table has over 500K rows at this point and is accessed by around 1000 users. The queries are optimized and the table is indexed properly (I think) but as the db grows performance is starting to lag. I'm thinking that the quick fix would be to break up the tasks table into smaller ones by user. So ... is that a good idea? All of a sudden I'm going to have 1000 more tables. Also the app code will be adding tables on the fly as new users come on board. (The existing tables are all of type MyISAM)
View Replies !
Large Number Of Queries
Ive currently got a loop on a php page which takes ~7000 rows from an sql table, parses information from it and does inserts/updates/deletes depending on the information. At the moment, it does a new mysql_query for each of the inserts/updates/deletes so it ends up doing ~10k queries each time its run. Im wondering if its better to use an array to store the queries and then put them all together (";" seperated) and run all the queries at once in the same mysql_query. One thing to bear in mind is some of the data involved in the queries relies on previous data from queries in the loop, would this cause any problems with the combined query
View Replies !
Large Number Of Tables
I would like to ask whether it is feasible (or whether it makes sense) to run a PHP script that creates a number of tables for each user. Basically, this script allows users to sign up for a free service, and for each user that signs up, the script creates 9 tables in the database to contain that user's details. Is this a sensible way of containing user data, and what are the inherent problems this may cause especially when say are 1000 users signup?
View Replies !
Large DataBase Columns
I am developing, what i consider to be, an extremely large database, using mysql and php. im concerned that one of my sql tables will be too large. tonight, im running some tests, but i was hoping the forums could toss me some good advise. The table will be around 1500 columns long, and about 500 rows on average. each column will not be any larger than 20 varchars. i've never created anything beyond 30 columns. is this beyond what's recommended with using mysql ??? i know the queries may take a little bit to load, but is this simply a bad idea anyway, or am i just getting nervous for no reason at all.
View Replies !
Import Large Amounts
My client has a large database in MS Access. I need to put a section of this (approximately 900 rows) into MySQL to be displayed on a website. The thing is, every month I will be resupplied with updated data, which will need to replace the data that is currently in the SQL database. I am operating on a Mac and they've given it to me as an EXCEL spreadsheet, which I am trying to import into MYSQL using phpMyADmin with no luck. I've tried exporting from EXCEL to various formats, such as CSV, but I can't seem to get it to import into the MySQL database Does anyone know what I can do? I need it to be relatively simple so that every month I can simply reimport the new data. Is there some trick to importing the data from EXCEL, or should I ask the client to supply the data from MS Access in a different format.
View Replies !
Large Database Table
I am using MYSQL in order to develope a database table that currently has about 20 million records. The table grows on a daily basis and is expected to grow to a size of about 300 million records.I wanted to get some suggestions in order to handle such large database. What kind of indexes are best? When should splitting the table be considered? What are ways of achieving good performance of queries?
View Replies !
Large MySQL Database
There are many threads on here about large db designs etc but I'm looking for a little information I have been unable to find anywhere. What sort of design to places like facebook/myspace use for their database(s). Is it best to have a table for user info (id, username, email, etc) then have a table for every feature of your site (blogs, pictures, friends, comments etc etc) or do they have a table for every user. I would assume the first as a table for every user would soon grow very very large and become inefficeint.
View Replies !
Large .sql File & Phpmyadmin
My ex-host recently gave me a backup of my ftp and database. The database consists of one massive .sql file. I've tried uploading the .sql as an SQL query via PhpMyAdmin, but the limit for files in PhpMyAdmin is set at 12mgb. The database is 32mb. Is there any way I can get the database up?
View Replies !
Normalizing Large Pieces
Let's say I have a user's table that is very frequently used: user ________________________________________ user_id(PK),user_username,user_password ------------------------------------------------------- And a user_info table that is not so frequently used: user_info _____________________________________________________________ user_id(PK),user_info_fname,user_info_lname,user_info_address, user_info_city,user_info_state,user_info_zip,user_info_email ------------------------------------------------------------------------------------- Now what I was thinking is that the user_info table is much to large to be considered a normalized table. If so, how can I break it up efficiently?
View Replies !
One Large Call Or Several Smaller Ones
Say have 250,000 rows to play with, and I want to show the results for the last week, the last month, and then the previous 6 months - do I may a query for each month or do I retrieve the results for all 6 month and let PHP do the work for me? Whats the best way? What are the arguments for and against? Anybody got any good resources I can have a read of?
View Replies !
|
TRACKER
