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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
Count Rows For Each Hour The Last 24 Hours, Including The Hours With Zero Rows
I have a table for logging log-in's with these columns: id, datetime, name. I want to see all the logins the last 24 hours for a specific name. I want mysql to return one row per hour (24 rows) and how many logins this name has done in these hours. Example result: 2009-02-13 00:00 2 2009-02-13 01:00 0 2009-02-13 02:00 0 2009-02-13 03:00 1 2009-02-13 04:00 0 2009-02-13 05:00 3 2009-02-13 06:00 5 2009-02-13 07:00 0 etc... 2009-02-13 23:00 7 I made this query: ......
View Replies !
UDF In Create Index
Is it possible to use function UDF in CREATE INDEX declaration ? For example: CREATE INDEX innertext ON webpages (MY_UDF(html)). It's avoid the creation of an intermediate column with the data transformed.
View Replies !
Create Index
On an Oracle database it is possible to create an index for a table on a different tablespace. (create index userindex on user (userid) tablespace indices;) I did not found a similar statement in the MySQL Reference. Is there another way to do that?
View Replies !
Create The Effective Index
I want to create the best index for that query: "select * from talkback where status=0 order by bject_type,vertical_id,event_id" Which index i need to create for best performance.
View Replies !
Can't Create Index ... 1044
My user wood@localhost can't create an index on the databse woodwork... I get the error ERROR 1044: Access denied for user: 'wood@localhost' to database 'woodwork' mysql.user reads... +----------------+------------+ | user | index_priv | +----------------+------------+ | root | Y | | root | Y | | | N | | | N | | wood@localhost | Y | +----------------+------------+ anyone know why?
View Replies !
Create An Index Without Using All Available Connections
I've got a very large table (149 fields -- I know, I didn't create it, and I'm working on changing it...) and I need to add an index to speed up a join query. However, when I run the "create index `index_name` on table(`column`)", MySQL uses every available connection to attempt the creation of the index, and does not allow the web site to access the database. If this was a short period of time (a couple of minutes), I could stand it. unfortunately, this is a live production server, and I'm guessing it will take an hour or two to create the index. Does anyone know how I can run the "create index" query while limiting the number of connections the query uses? The version is: mysql Ver 12.22 Distrib 4.0.16, and "max_connections" is currently set to 100.
View Replies !
Killing An Index Create
I am running a create index on a large table. The job is taking an extremely long time. If I kill the create index process what are the ramifications in MySQL? Will it simply roll back the index? And is there any way to see a percentage remaining on the job?
View Replies !
Create Index On Date Field
I have a table: +-------------+--------------------------+------+-----+---------+------- ---------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------------------+------+-----+---------+------- ---------+ | id | int(11) | | PRI | NULL | auto_increment | | NUMFATT | int(5) unsigned zerofill | | | 00000 | | | dataemiss | date | YES | | NULL | | +-------------+--------------------------+------+-----+---------+------- ---------+ (the table has more fields but I removed them to semplify the view of it). Where I want to create a UNIQUE index with two tables 'dataemiss' and 'NUMFATT' 'dataemiss' is a date format and in the index I want to consider just the year part of the date. I cannot change the field type setting it as a string (ex. char, var, etc.). any hint on how to do it? The goal of this is index is to avoid to insert the same 'NUMFATT' twice or more in the same year. I'm using MySQL 4.0.13 on Win 2K server.
View Replies !
Create Index For A Mysqlorary Table
I have to do SQL query over two mysqlorary tables, I want to create indexes on fields of the mysqlorary tables. Is it possible to create index on a mysqlorary table. I am using MySQl 5.0.26 Furthermore, Is it possible to merge the statements for creating mysqlorary table and the statement for creating index on the mysqlorary table into one singel statement....
View Replies !
Create Index And Bookmark Script
I am using a bookmark script and have created a few sites with it.Sites run great when DB is small but the large site (190 MB) DB is hogging CPU I have been kicked out of two host for this last host suggested I create index on DB this is what the tables look like bookmarks table 189,134 rows bId uId bIp bStatus bDatetime bModified bTitle bAddress bDescription bHash Bid Bookmark Id is PRIMARY ties to tags table Tags this is probably where indexing would help the most as the google bots try and index the whole site all at once and kill the host Tags table 1,451,202 rows id bId tag id Primary key bId bookmark Id
View Replies !
How To Create/choose A Suitable Index
I am having trouble figuring out a suitable index(es) in the following case: select * from circuit where (col1a=val1 and col2a=val2) or (col1b=val1 and col2b=val2); (it happens that I use val1 and val2 as above). Every choice I make for index doesn't, according to EXPLAIN, get used by the SELECT. The table has a few thousand entries and I expect the SELECT to return one or two at most. Well, it does, but quite slowly (in fact the SELECT is a bit more complex) and I would really like to improve this particular combination as its at the inner-most loop of my PHP code :-) INDEXes seem to work for ANDs but not when there's an OR in the month (sorry). So an index of columns A,B gets used if you have A AND B in the WHERE but not A OR B.
View Replies !
CREATE INDEX Crashes Server (Lost Connection...)
I'm having trouble with a table I created quite a while ago and now want to add an index to. Here's what I get if I try to add an index on the host field of my table: mysql> create index Index_host on uselog(host); ERROR 2013 (HY000): Lost connection to MySQL server during query Connection was lost because the server crashed. The Windows event log has an event that says "Faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0, fault address 0x0017dc06." I've also tried the allegedly identical "ALTER TABLE uselog ADD INDEX Index_host(host)", as well as trying to index a different column, all with the same outcome (server crash). Thing is, I had no trouble adding an index to the same table a week or so ago. The table was smaller then (and had only one index), but even now, it's not very big by DB standards, so I can't imagine that's the problem. (My table had ~900K rows earlier today, which I pruned down to 140K rows after my first failed attempt to add the index. Puny! Only 55MB data length, according to MySQL administrator.)
View Replies !
Unable To Create Full Text Search Index On A Column
I have installed MySQL 5.0 on my WinXP machine. I have created a test schema in which I have created a test table as follows: Test_Table { Id => Integer and Primary Key Name => Char(255) Address => Char(255) Tags => TEXT } Now I want to create a full text seach index on column Tags, but when ever I try to execute following SQL statement: ALTER TABLE `TEST_TABLE` ADD FULLTEXT `Index_FTS`(`Tags`); I get following error: Error while executing query: ALTER TABLE `TEST_TABLE` ADD FULLTEXT `Index_FTS`(`Tags`); MySQL Error Number 1283 Column 'Tags' cannot be part of FULLTEXT index. My table is MyISAM based table.
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 !
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 !
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 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 !
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 !
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 !
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 !
Now() - 3 Hours
I was wondering if I can do this easily in mysql to change the time from server time to my time (server is in EST, im PST). PHP Code: $query = "INSERT into table(current_date) VALUES(NOW())"; PHP Code: $query = "INSERT into table(current_date) VALUES(NOW() - 3 hours)"; or should i just get the date and format it in php?
View Replies !
Group By Last 24 Hours
I am looking for a query to produce a result set of the number of hits in the last 24 hours. I have a table called hits with the following fields (ip [bigint], created[timestamp]). What I want is a result set like this for the last 24 hours from now (assuming the current time is 23:15), hour hits ----- ------ 23:00 5 22:00 18 21:00 8 20:00 0 19:00 0 18:00 0 17:00 22 etc..... I tried the following query but it only gives me results where hits > 0. Ideally I want to keep the hours (for sequence) where the hits are 0. SELECT hour(created),count(ip) FROM hits GROUP BY 1
View Replies !
NOW() Minus Few Hours
I'm doing a query along the lines of the following: SELECT * FROM `featured_item` WHERE now() >= date_start AND now() <= date_end; Gets featured items, that each have a start and end date specifying how long they are valid and should displayed for. However, I need now() to actually = now() minus 2 hrs.
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 !
Lost Connection To MySQL Server During Query After Script Takes 60 Secs
I get the error "Lost connection to MySQL server during query" at the mysql_select_db when reaching this part of the code after 60 seconds of script execuation has already passed. Works fine if the script execution time when reaching this is under 60. Tested with Sleep() and I'm 100% sure that the 60 second barrier is causing it. I just don't know what the barrier is.////
View Replies !
Now() Or Current_timestamp() And The Hours Value In Time
When I run select now(); or select current_timestamp(), I see the output like "2005-04-19 02:50:14". By reading the documentation I thought the the hour value should be in military format namely 14, but instead I see 02. How can I fix this problem? I have MySQL 4.1.10a database running on RH 9.0 server.
View Replies !
Comparing Hours In One Single Day
I have to write a program using PHP5 and mysql 5.0.51b thats found on the WAMP 2.2 The dilemma is the following: I have 5 meeting rooms each one named c1,c2,c3 etc How do make a table where I can reserve a room at a certain date and certain time? This is the table i have so far: ....
View Replies !
Find Record 24 Hours Ago
I have a mysql time field type and a mysql date field type in my table I am inserted these values from a script running via a cron every 30 seconds How would I find a record exactly 24 hours ago? I dont care about the exact second only minute....
View Replies !
Replication Master Crashed After 6 Hours
I recently setup replication, the master and slaves appears to work fine initially. After about 6 hours, the master mysqld process will start to consume 99% CPU and the load (avg. load per minute) will go to 3, 4, 6, 15, 24, 56, 312... in a matter of seconds! The master then stops responding and needs a reboot. This problem is reproducible, I have tested a few times. This is a screenshot of "top" command just before the master died: http://choonkeng.hopto.org/temp/replication-hang.gif There are plenty of memory and no disk swapping when the master goes 99%. Error and slow logs don't show any useful information.
View Replies !
Delete Rows After 24 Hours In A Table?
I'm gonna keep track of all uniqe visitor on a web page and therefore I need to store all visitors IP-number. Each IP-number is only counted as a visit if its on diffrent days. So the IPs will be saved for 24 hours. Is there a way to automaticly delete rows after 24 hours in a table?
View Replies !
SELECT Based On All Day Names And Their Hours
I have a table which has a DATETIME column which stores datatime for each record. I wanted to calculate the total/average of the records for each day and then for each hour of that day ALL TIMES like not only the current week or current month or year but for all available records. a sample output can be Monday 00 (count value) 01 (count value) 02..... (count value) 23 (count value) Tuesday 00 (count value) 01 (count value) 02..... (count value) 23 (count value) ... Sunday 00 (count value) 01 (count value) 02..... (count value) 23 (count value) I tried something like following SELECT count(id) as cnt,HOUR(hitdate) as hr, hitdate FROM hits GROUP BY DAYOFWEEK(hitdate),HOUR(hitdate) And I am getting the following results, (long result) cnt hr hitdate 1274 0 2008-07-27 00:00:50 1235 1 2008-07-27 01:00:07 921 2 2008-07-27 02:02:13 830 3 2008-07-27 03:00:03 674 4 2008-07-27 04:02:49 617 5 2008-07-27 05:00:37 606 6 2008-07-27 06:01:41 545 7 2008-07-27 07:01:52 534 8 2008-07-27 08:05:58 630 9 2008-07-27 09:04:01 680 10 2008-07-27 10:04:41 688 11 2008-07-27 11:01:26 881 12 2008-07-27 12:00:22 745 13 2008-07-27 13:00:09....
View Replies !
Drop Tables Older Than 2 Hours
Is there any way to drop tables in the database which are older than 2 hours. The WHERE clause does't work with SHOW TABLE STATUS. The created table is deleted as soon as the user selectet or deleted records and submitted the form. If the user just closes the browser, i am stuck with some useless tables. The tables are easy to identfy, they all start with tmp_.
View Replies !
Selecting Rows From The Past 24 Hours
I have some data rows, and they contain date entries in a datetime formatted field - e.g.: 2006-07-05 01:30:03 2006-07-05 01:46:36 2006-07-05 01:20:41 I would like to select rows from the table for the past 24 hours It would be something like: SELECT fldID, fldName, fldDate FROM my_table WHERE .... fldDate is within the last 24 hours But I'm not sure how to do it. I've used the date_format function before, but that might not help. Then I found the date_add function - so if I did this: SELECT DATE_ADD(��-01-02 23:59:59', INTERVAL -24 HOUR); It would return: 1998-01-01 23:59:59 That looks good - but I'm not sure how I can build it into my statement to only return rows where the data_format field contains data less entered in the last 24 hours.
View Replies !
|
TRACKER
