Results In Multiple Pages, Takes Too Much Time
I have a table of a million records and wrote a CGI-PERL script to
display the results based on the user input. The results might be
anywhere from 100 to 1000 per query and presently I am displaying them
as 25 results per page.
Problem: Each query is taking about 20-30 seconds.
My solution: I have tried to optimize the table and also index the
table. I have actually converted a MS access database to SQL database,
so it wasn't previously indexed. Both optimization and indexing doesn't
give any good results. I always get a timeout. ie. it takes longer
after indexing and optimizing.
1. I was wondering if someone has a creative solution for this. ie.
reduce the time from 20-30 seconds to atleast 10 seconds.
2. I have links of pages of results beneath the first page result. When
each of these links are clicked it takes 20-30 seconds again. Is there
a way I can reduce the time taken for the subsequent pages are reduced?
I cannot use the LIMIT option in mysql, since I have a where clause
which has to search through the whole table. I tried using views and
using limits, but it takes as much time.
View Complete Forum Thread with Replies
Related Forum Messages:
Search/Results Pages
My Search page works, My results page doesn't want to display the results properly and I think the problem lies with my select statements. I am searching for a match between a selected Event Name in the db to a user Event Selected. All one table called Events. The result only displays one row of information when there are 2 matches in the db. The Event Name selected on the Search page is a drop down menu of choices with a Submit button and Event Names do have spaces and hyphens in them which the db stores verbatim.
View Replies !
Query Results By Pages
I would like to display query results in pages of say 20 results each. e.g. a table with two columns, serial# and descrition, with a few thousand entries. What would be the SQL query to find a specific serial number, and display all 20 results within the "page" that row happens to be in? e.g say serial#=3211 is in row 64, display rows 60->79.
View Replies !
Query Takes A Long Time
On my site I have a query that searches through 1,7 million. The php-file with the query takes a bit of time to load. Is it possible to show some sort of progress-bar during this time?
View Replies !
Display All Results Vs Spliting Up Over Number Of Pages
I have built a site for a local auction house. Each auction has around 2000 lots, which are held in the table auction_items with references to another 3 tables. I am trying to work out the best way of displaying all of the items for a given auction, whilst optimising performance. Each site visitor typically wants to see all of the items in the list and wants the option to "show all" items in a single view. From past hits we estimate approximately 700 hits a day, most of which will want to view the list of items. Are there performance benefits to be gained from displaying the results 20 to a page and having the users scroll through? Rather than loading the full list once per user? From a usability point of view they would be better off with one list that they can see "at a glance" and print.
View Replies !
JDBC Result Closing Takes Time
i am reading 1000 Elements from a database (500.000 all together). The problem is reading the data takes only a few miliseconds but closing the ResultSet takes another 22 seconds. My Hard Disk tells me that the ResultSet is probably running to the end of my table row by row. Is there a way to tell mysql not to do this ? Statement tempStmnt = aConnection.createStatement(); tempStmnt = aConnection.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); tempStmnt.setFetchSize(Integer.MIN_VALUE); Date tempStart = new Date(); ResultSet tempRs = tempStmnt.executeQuery("SELECT * FROM ARTIKELSTAMMDATEN"); int tempXx = 0; while (tempRs.next() && tempXx++ < aCount) { tempRs.getString("artikelBezeichnung"); }
View Replies !
Easy SELECT With OR Takes A Very Long Time
We've got a database with about 1000000 books. A query in the table BOOKS for the TITLE 'java' is very fast. We have a fulltext index on the column TITLE. However, if we want to do a exact same query and include the rule that the book with ISBN '0131016210' always should be included - then the query take several seconds to finish: SELECT * FROM C_BOOK WHERE MATCH(NAME) AGAINST ('java') AND (ISBN LIKE '0%' OR ISBN LIKE '1%') OR ISBN = '0131016210' ISBN is the PRIMARY KEY. Is there anything I can do about this?
View Replies !
JDBC Result Closing Takes Ists Time
i am reading 1000 Elements from a database (500.000 all together). The problem is reading the data takes only a few miliseconds but closing the ResultSet takes another 22 seconds. My Hard Disk tells me that the ResultSet is probably running to the end of my table row by row. Is there a way to tell mysql not to do this ? Statement tempStmnt = aConnection.createStatement(); tempStmnt = aConnection.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); tempStmnt.setFetchSize(Integer.MIN_VALUE); Date tempStart = new Date(); ResultSet tempRs = tempStmnt.executeQuery("SELECT * FROM ARTIKELSTAMMDATEN"); int tempXx = 0; while (tempRs.next() && tempXx++ < aCount) { tempRs.getString("artikelBezeichnung"); }
View Replies !
MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same). My server specifications: Red Hat 9.0 Pentium 4 3.0 GHz 2 GB Ram, Burst RAM 5 GB Running webserver, mail server as well. Is there a way I can view the minute process details, as what table record is being updated ?
View Replies !
Is Null Clause Takes A Lot Of Time But Is Not Null Statement Not
i have a query which takes 1 and half minute to fully execute. This query is following which return 2 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is null but if i remove the 'not' from where clause then it takes a fraction of seconds. query is following which takes fraction of second and it returns 3920 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is not null
View Replies !
Time Period Results
Can anyome tell me the php script to return data from a table which is less than 24 hours old. I have a timestamp on my table when data is added.
View Replies !
Time Value Calculation That Results In A Negative
here's my code: SELECT *, SEC_TO_TIME(TIME_TO_SEC(SEC_TO_TIME(SUM(TIME_to_SEC(phour)))-SEC_TO_TIME(SUM(TIME_to_SEC(mhour))))) AS Balance from toil.User join toil.toil on user.userid = toil.userid where name = 'X' FYI I'm working on a database to store details of Time Off In Leiu worked by staff (toil)
View Replies !
Multiple Results
I'm using PHP to display a list of statistic information about a site. Sometimes I need to retrieve mixed information from the database but I don't know which is the best method to do it. For example I need the top requested html pages and the most repeated value from a column. I dont know if I should make two different queries in the php file or it's more efficient to make only one query with an extra column like id | ip | date | page | max | --------+--------------+----------+----------+---------| 1 + 24.125.24.25 + 24/5/03 + index + 3 | 2 + 24.125.24.25 + 24/5/03 + top + 3 | 3 + 20.12.12.21 + 24/5/03 + index + 3 | 4 + 200.12.24.25 + 24/5/03 + left + 3 | 5 + 24.1.6.255 + 24/5/03 + left + 3 | 6 + 24.125.24.12 + 24/5/03 + index + 3 |
View Replies !
Multiple Results From $row[#]
i have a script for an image gallery, but in the spots where all the images (in the database) are supposed to be displayed, i have $row[6] on all of them, which produces like 12 of the same image... how do i get $row[6] to have different results from newest to oldest and automated?
View Replies !
Multiple Results In A Quey
I'm using PHP to display a list of statistic information about a site. Sometimes I need to retrieve mixed information from the database but I don't know which is the best method to do it. For example I need the top requested html pages and the most repeated value from a column. I dont know if I should make two different queries in the php file or it's more efficient to make only one query with an extra column like... id | ip | date | page | max | --------+--------------+----------+----------+---------| 1 + 24.125.24.25 + 24/5/03 + index + 3 | 2 + 24.125.24.25 + 24/5/03 + top + 3 | 3 + 20.12.12.21 + 24/5/03 + index + 3 | 4 + 200.12.24.25 + 24/5/03 + left + 3 | 5 + 24.1.6.255 + 24/5/03 + left + 3 | 6 + 24.125.24.12 + 24/5/03 + index + 3 |
View Replies !
Merging The Results Of Multiple Tables
I have two tables. The first table is "users", the second table is "songs". The idea is that each user (one record in the users table) can upload several songs (several records in the songs table). I have it so that each new Song added, has a foreign key that points to a user record. So dozens of Songs could all point to the same user. My question is, I want to run a query that returns information about a user, including all their songs. So, the return table would look like: username alias songname1 songname2 songname3 username alias songname1 songname2 username alias songname1 songname2 songname3 songname4 etc.. The songnames come from the SONGS table, and username/alias come from the USERS table, but the final result should be one table. What I've managed to get working is returning all the songs of a user, but as different rows in the table: username alias songname Bob bob Happy Song Bob bob Sad Song Bob bob Super Song But I really want that to be returned as: username alias songname1 songname2 songname3 Bob bob Happy Song Sad Song Super Song So I want to merge n number of sub records, into a single user record, and return the results as 1 per user. How do I perform this feat?
View Replies !
Querying From Two Tables - Multiple Results?
I have two tables, A and B. A has 10 rows and B has 5 rows. I make a query like: SELECT a.name, a.address FROM A a, B b WHERE a.zipcode!=b.zipcode The problem is that since there are 5 rows in B, I get back 5x the results. I can use LIMIT except the number of rows in A is not known, it may not be known. How can I fix this?
View Replies !
Search Results In Multiple Columns
Currently using WHERE email LIKE '%$keyword%' I would like for the keyword to look into multiple fields WHERE email,fname,lname LIKE '%$keyword%' Searching the entire table would be fine too. I tried something similar to the above and it didn't work. Sorry if this has been covered before. I couldn't find a thread...
View Replies !
Creating A Single Field From Multiple Row Results
I know how to combine multiple columns to get a single result field but I'm not sure how to combine rows from the same column into a single result field. Basically what I want to do is the following. If my table looks like: risk | reference -----|------ a | 1 a | 2 a | 3 I want a query that will give me: risk | references -----|------------ a | 1, 2, 3
View Replies !
Join Results From Select Multiple Statment
how I can join results from multiple queries in one result without geting a prodict of the tables ex: table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3... table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3... , , ...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3... , , ... , , ...table3_Col1, table3_Col2, table3_Col3... , , ... , , ...table3_Col1, table3_Col2, table3_Col3... using the 4.0 sql so no subqueries supported.
View Replies !
Sum Of Time In Multiple Rows
I have a table Code: TASKS{ started => time column, ended => time column } Now, for every task I do I make a new record (e.g.) saying I started at 11:00 and ended in 11:15 Now, I can find how long I worked PER row (e.g. in past example the following would return 15 min) Code: select subtime(ended, started) as time from tasks How do I sum all the rows, not just one?
View Replies !
Multiple Left Outer Join Results Duplicates
I have tables 1. Company Company-Id | Name 1 | c-1 2 | c-2 ... 2. Company_vs_Industry Company-Id | Industry 1 | sales 1 | service 2 | sales ..... 3. Company_vs_Group Company-Id | Group 1| group-1 1| group-2 2| group-2 I am trying to select the Company with its associations with LEFT OUTER JOIN like, Select * from Company as a left outer join Company_vs_Industry as b on b.Company-Id=a.Company-Id left outer join Company_vs_Group as c on c.Company-Id=a.Company-Id and I am getting the result as, Company-Id | Name| Industry | Group 1| c-1| sales | group-1 1| c-1| sales | group-2 1| c-1| service | group-1 1| c-1| service | group-2 ... Company-Id | Name| Industry | Group 1| c-1| sales | group-1 1| c-1| service | group-2 ...
View Replies !
SELECT DISTINCT Unexpected Results With Multiple Columns
SELECT DISTINCT (query) FROM `data` WHERE 1 LIMIT 0 , 30 and it works fine. When I start selecting more than one column, though, it returns all the rows weather the query is distinct or not. Here is the query that doesn't work. SELECT DISTINCT (query), data. * FROM `data` WHERE 1 LIMIT 0 , 30 It seems like DISTINCT() is looking for a completly distinct row. How can I get it to just look for a single distinct column?
View Replies !
UPDATING Multiple MySQL Tables In The Same Time
I have to perform a query and I don't know how to do it. It is a Joomla 1.5 guery and here is it: $_CB_database->setQuery(" UPDATE #__comprofiler SET avatar = " . $_CB_database->Quote( 'gallery/' . $newAvatar ) . ", avatarapproved=1, lastupdatedate = " . $_CB_database->Quote( date('Y-m-d H:i:s') ) . " WHERE id = " . (int) $row->id . "; UPDATE #__mycom SET avatar = " . $_CB_database->Quote( 'gallery/' . $newAvatar ) . " WHERE user_id = " . (int) $row->id .";"); if( ! $_CB_database->query() ) { $msg = The query hasn't been performed; }
View Replies !
ORDER BY Takes Forever
I'm having problems with ORDER BY. When I run a select targeting just one of my tables, it runs quickly and nicely. When doing the following: select * from rubbet, kommun where ortnamn like '%Johannes%' and rubbet.harad=kommun.harad and rubbet.socken=kommun.socken order by rubbet.ortnamn; it screws up bad. The query takes about 2 minutes to run before showing the result: "74 rows fetched in 0.0032s (112.5129s)" obviously the query takes no longer than 0.0032s but the ORDER BY clause makes it take forever to bring back the result. without the order by the whole thing runs very smoothly.
View Replies !
SELECT Takes 20 Seconds
I am having trouble speeding up a SELECT statement from a table with 1,764 records. I tried myisamchk --sort-index --sort-records=1 but this did not help (is a Primary key enough?). Things were fine at around 1200 records but went downhill after 1700. I am using MySQL 3.23.42 on HP/UX 11.11 and PHP 4.3.4. Any tips on how to speed things up?
View Replies !
Update Takes Too Long, I Need Help
here is my update SQL string: update prices, legend set prices.legend_id=legend.id where ( (prices.id between 1 and 10006) ) and prices.a_legend=legend.description running time: 63.97960 as you see prices has 10006 records and legend table arround 349 records please advice how to speed up this?
View Replies !
Subquery Takes Forever
I have two problems where I'm trying to retrieve data using a query. Part 1: I am doing a simple sub query which is on a two data sets with no more than 3000 rows in total. The query is taking 28 seconds to execute? Why I don't have a clue? Quote: 'Select Title FROM products WHERE Product_ID IN (SELECT Product_ID FROM order_items WHERE Order_ID="'.$id.'")' Part 2: When I have solved the above I'm looking to do a join, but don't know how to approach it ? Basically I'm trying to get a complete data set for a product/order like the following: SELECT * (which includes product_id) from items AND SELECT Title from products where product_id =(the product_id is retrieved from items); // using a join
View Replies !
Usergroups On Pages
I am trying to make some type of simple CMS, and I have a table for pages, and I want it so that when the user is making the page, they have an option for what usergroups they want to be able to see it. The admin will be making the usergroups themself, so I don't think I would be able to use SET. ANy idea on what I would put in my "usergroup" table or my "page" table? Or any other method of doing this.
View Replies !
Pages With Mysql
i'm looking to do pages with stuff out of mysql. lets just say i have a long list of names in the database and i want to display 10 at a time and have "<<Prev 1 | 2 | 3 next>>" display for each page. like this: name1 name2 name 3 name4 name5 name6 name7 name8 name9 name10 <<Prev 1 | 2 | 3 next>> i've tried doing something like this before but it's never worked right.
View Replies !
Popular Pages
Is it possible to use data from the url, record it into MySQL or text data file so as to display the popular pages from a site. Any PHP+MySQL ideas or links are needed? ----------------------------- I had the idea started with no luck... the url is basic structure like: domain.com/tmplate.php/1038/BMW list($first, $last) = explode('/', substr($PATH_INFO,1)); $entry_line = "$first/$last "; $fp = fopen("/public/count.txt", "a"); fputs($fp, $entry_line); fclose($fp); the explode grabs the 1038/BMW from the url and records into a text file.
View Replies !
Exit Pages
I have a database that contains all the pages requested on a certain site and the ip of the requester and a timestamp. Is it possible in an sql statement to get a count of the exit pages. ie. count the number of times each page was the last page requested by that IP within a given time frame?
View Replies !
Create Index Takes More Than 5 Hours
I'm running MYSQL on a windows xp laptop with an intel centrino 1.60 GHz Processor and 512MB RAM. I've created a table and imported 27 million rows of data. I wanted to create an index on one char(55) field, as this field is a key against which many selects will be made. The information in the field is not conducive to having an integer key as any queries would require an extra join to the look-up (reference) table. Creating the index ran for more than 5 hours and finally gave up when there was no more space on the drive (creating the index apparently consumed more space (11GB) than the actual table (8GB). I'm wondering if this is normal time and space consumption for 27 million rows. Finally, doing an un-indexed query on this table : SELECT * from historical where reference = 'abcd'; Takes more than 1/2 hour. Is this also a normal amount of time? I understand that people are using MYSQL for data warehousing and major transactional applications.
View Replies !
Navigate Pages By First Two Letters Of Name
I am creating a PHP/MySQL application (if you can call it that). The purpose of this part of the project is to display all of the records in the database ordered by first name. Right now I am selecting each record according to the first letter of their name: A, B, C, etc using a URL parameter. The flaw with this method is when there are only a few records for any given letter, and there are empty queries for letters like Z and X, but not all of the time, and as the list expands I wouldn't want to be changing the navigation. I believe a better method would be to create the query so that you navigate through the pages of the total records (ordered alphabetically ASC) by the first two letters of the first and last records on each page. I'm not advanced enough yet to know exactly how to do this and I was hoping I could get some help. I hope I am explaining this well enough. The other information that would help you help me: For each record, there is a "name_first" field which is what I need the first two letters from. There are three tables I am getting this information from: "men", "women" and "children". There are other variables in the query: "online" and "categoy". The query is currently: $query_men = sprintf("SELECT * FROM men WHERE name_first LIKE '%s%%' AND online LIKE '%%Y%%' AND category LIKE '%%Men%%' ORDER BY name_first ASC", $colname_men);
View Replies !
Pages Are Created On The Fly By Rows...
I have a MySQL database and I want to echo one field (Title) into a page so it can act as a link to that row. When someone clicks the title, I want that row's information to come up. For example Example Title Example Title 2 Example Title 3 User clicks Example Title 2, Example Title 2 | Example Author 2 Example Description. To be displayed. I know this requires "index.php?=". I just don't know what its called and I can't seem to find any tutorials about it. How is it done?:(
View Replies !
Connection :: Pages Slow
we have end site layout (really fast and made with css+xhtml) so we have insert the mysql connection The page load seems very very slow at middle of page. Page is load within middle, after there are pause of 1/2 second and after load continue .. we don't have meet this problem so, we have comment all last row code.. the row that make problem is mysql connection: $conn = mysql_connect($_CONFIG['host'], $_CONFIG['user'], $_CONFIG['pass']) or die('Impossibile stabilire una connessione'); mysql_select_db($_CONFIG['dbname']); Have you any idea?
View Replies !
Displaying Certain Dates For Certain Pages?
I am wanting to display certain dates on certain pages. For example: newsletters.php January 2007 Monday 15th January 2007 // link to another page to display the newsletter Tuesday 16th January 2007 // link to another page to display the newsletter February 2007 Saturday 10th February 2007 // link to another page to display the newsletter Sunday 11th February 2007 // link to another page to display the newsletter
View Replies !
MySQL Takes FOREVER To Start Back Up
Need some help here. I am running MySQL 4.1 on a windows 2003 server. We've had the server for about a year and right now the data file for the database is up to 345mb. Its growing week by week. Lately if I had to restart my server once windows comes back up it takes 20min for MySQL to start back up.
View Replies !
Backup With Mysqldump Takes Hours, Database Is Not That Big
Hello, I've been having some problems making database backups. I've searched all around and I notice that to most people it only takes a few minutes to backup huge databases (1+ gb). My database is around 350mb and it can take up to 4-5 hours to backup, which I guess it not normal at all. My server has a dual Xenon with 4gb ram, using mysql 5.0.27-1.fc6 and php 5.1.6-3.6.fc6. I am using Fedora Core6 with Plesk. The database is around 350mb, with around 1.1 million rows. To backup I use mysqldump -uroot -p database > backup.sql As I said, the above takes hours. I guess it should take minutes?
View Replies !
Delete All Records In A Table Takes Forever -- Anyone Know Why?
I have a table called table_a that has 1 record in it. I delete that record, which because of foreign keys (the tables are type InnoDB) will cause the records in 8 other tables to be deleted. The 8 other tables have a maximum of 200,000 records in them. 2 of them have that many while the remaining have < 5,000 records. Currently, deleting that 1 record has taken hours upon hours with no end in site. Does anyone know why in the world this would take so long? Is there any way to speed it up (maybe a config setting I am missing or something)?
View Replies !
PROBLEM WITH JSP PAGES, BLOB AND MYSQL DATABASE
I have a html form and I am using the <input type="file"> tag in order to store a blob image file into my MySQL datase. The problem is that I don't know how to get the file and store it. I write Java Scriptles in the JSP pages (this means pure java).
View Replies !
Creating A FTP Server/pages For File Transfer
I've never worked with MySQL before. I need to create a FTP logon page for clients, I'm not sure that MySQL is even the best solution f ro what I want to achieve, but I would appreciate it if someone could give me some advice. An example of what I want to do is at: http://www.rtfacts.co.uk/page2.htm From here click on "Contact us" on the next screen you will see "Click here to go to our FTP site". This is what I want to achieve. Can I achieve this using MySQL or is there an easier way? The hosting service I use provides MySQL services, so I can set up a database if I need to.
View Replies !
|
TRACKER
