Indexing Taking Long Time
I am currently importing about a million rows into temp tables - then the temp tables are indexed and finally the original tables are dropped and the temp tables are renamed - The indexing of the temp tables takes a considerable amount of time - My question is - Would there be a difference if i index the temp table before importing as opposed to after??
View Complete Forum Thread with Replies
Related Forum Messages:
Restore :: Taking Long Time
I've got a table with over 400,000 records. I did a mysqldump to ensure I could restore it in case of problems. Sure enough, problems arose and I needed to do a restore. The mysqldump created the usual create database, create table and one gigantic insert statement, where all 400,000+ records are inserted. I started my restore around 10pm or so yesterday and was surprised to wake up this morning and find it still running. Considering the dump itself took a couple of seconds, why is the restore taking so long? I'm building a website that's going to have tons of traffic. A database restore that's going to take multiple hours seems to be unacceptable. One way to speed up the process would have been to simply copy the actual database files and just stick them back in the data directory.
View Replies !
MySQL Taking Too Long
I'm trying to run a php script written by someone else, not me, and it's getting stuck in a particular step. Actually it isn't getting stuck per se, but the browser is, because it's taking forever to return the results back to the browser. Here's the line that's responsible for this: $users = $db->query_return_array("SELECT * FROM user"); It's getting stuck because in my database I have over 60,000 records. Now, I'm just wanting to get over this step (it's an upgrade script), not looking for fancy proper methods of php coding. What alternative ways are there for me to prevent the browser from timing out? I'm guessing some way of looping through the records, and updating the client with simple update characters to prevent it from timing out.
View Replies !
Joins Taking So Long?
Here is my SQL which I am running in MYSQL... select * from acct, custunq where custunq.custno=acct.custno; It should return about 250,000 records. The problem is, it takes about 2 minutes just to return 10,000 records so that would be what... an hour or so just to do this query. I have to be doing something wrong, it only takes like a minute to run within Access, and I thought MySQL was supposed to be faster?
View Replies !
Table Query Taking WAY TO LONG
I have a query that is inner joined with another table based on country codes Select distinct Name.* from Name inner join Location on Location.key = Name.key and Location.cc in ('<list of countries here>'); The problem is when I have more than 2 country codes, the query takes forever... When I show processlist, it says converting HEAP to MyISAM ( this takes over an hour )... I've tried bumping up max_heap_table_size to 128M and tmp_table_size to 128M, but that only seems to delay the converting HEAP to MyISAM message.... Name contains about 3 million records and Location contains about 1.5 million records. Code:
View Replies !
How To Prevent MySQL Queries From Taking Too Long
I have a very busy database with some tables having hundreds of thousands of records. I am running MySQL 5.0.16 on Windows. Some of the queries running on my database are not fined-tuned enough, and need improvement. However, the system is live in production and I am looking for a way to prevent MYSQL from committing Harakiri, i.e. from running very slow queries forever, taking the cpu to 50% and beyond for hours. My scripting language is PHP. I found that after a while the end user receives a timeout on the browser, but MySQL continues trying to run the query, sometimes for hours. My question is, is there a way to specify a timeout for MySQL so that it will abort any queries that it is running that have not returned a result by then. The end user is no longer around anyway, so I am looking for a way to protect the database resources (and in parallel I will start investigating how to optimize the slow queries).
View Replies !
Optimizing Table - Count Taking Too Long
Could anyone help me with some advise on optimizing a table? It currently has 600,000 records and 'select count(*) from products where 1' is taking several seconds to return a result. Mysql is running locally on my development machine (XP) as a service and is version 5.0.24a The table is defined as follows: CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, `description` text, `taxable` tinyint(1) default Ɔ', `sub_title` varchar(255) default NULL, `isbn` varchar(13) default NULL, `format` varchar(40) default NULL, `published_date` datetime default NULL, `pages` int(11) default NULL, `refundable` tinyint(1) default NULL, `refund_by_date` datetime default NULL, `publisher` varchar(255) default NULL, `bic_classification` varchar(10) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_products_on_isbn` (`isbn`), KEY `index_products_on_name` (`name`), KEY `index_products_on_bic_classification` (`bic_classification`), KEY `index_products_on_publisher` (`publisher`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
View Replies !
Take Long Time To Run A Query!
I have a webserver that I just use for a webapplication. PHP, Apache and MySQL is installed on the server. The size of MySQL database is 10 GB. End inn it is millions of rows. I wonder where I search what is the normail time the data to return? In my case it takes from 20 sec to 2 min to run a query!
View Replies !
Indexing Date / Time Fields.
If we have a date/time field and are doing frequent queries WHERE {date/time field} BETWEEN TimeA AND TimeB Does it make sense, query speed wise, to create an index on the date/time field? The reason I ask is that TimeA and TimeB are significant down to seconds. My *assumption* is that with a large amount of data scatter very few records will contain duplicate date/time points. And that under these circumstances, indexing will be of little value since every date/time point will need to be examined in detail. Date/time fields are actually floating point numbers after all. In other words, the assumption I am making is that if most of the data in a field is unique, then indexing will yield little or no query performance benefit. Is my "assumption" valid? Would an index on the date/time field speed up this type of query?
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 !
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 !
Script Taking Ages
if you can help me below is the script i run Code: select *,concat('[',first,'][',last,'][',postcode,']') from `new` where concat('[',first,'][',last,'][',postcode,']') not in (select concat('[',first,'][',last,'][',postcode,']') from `already`) basically thids is what happens it just takes too long and wanted to speed it up alot New = 70k records Already = 65k records i need to be left with the 5k difference
View Replies !
Stopping MySQL Taking Down A Server?
This may be a silly question but what can be done to stop load-intensive MySQL processes taking down a server? Things like adding fulltext indexes to very large tables, or selects on very large (multi-million-row) tables just completely kill the box until they complete. I don't mind how long these things take but the box shouldn't become unresponsive while they are running.
View Replies !
Null Field Not Taking Data
I have a mysql table with a password field. (varchar(20), Null YES). When i input data into the field from the form, I look at the table and it say null. It's not taking the data that I input into it.
View Replies !
HABTM Join Taking Ages
I have 3 tables: profiles (Contains User Profiles - 4 Million records) sections (Self Referencing Table containing Genres, Cinemas, Communication Preferences etc - 40 Million records) profiles_sections (HABTM between profiles and section - 200 Records) For example, I want to select all profiles in the database where the profile has select 3 sections. Currently, the only way I can get this data is with a VERY intense query which takes ages to return any records: ...
View Replies !
Putting In And Taking Out True/false Instead Of 1/0
In mysql 5.0, as far as I understand it, BOOLEAN is an alias for tinyint(1) How far does that aliasing go? When you INSERT, does it have to be a 1 or 0, or can it be a true/false? Same with SELECT, does it have to return 1/0, or can it be true/false? If it matters, I'm working with PHP.
View Replies !
Taking Colomns Of Data And Copying It To A Different Table
I've got two pieces of software running and I want to force users to only use the one log in so I though rather than trying to build a plug in I thought a cron job would no-doubt be easier. Now getting the data I want from the right table is easy enough for me but as the columns have different ID's I need to make it transpose it into the right columns... It runs but I suspect the second part of my code is failing. Code:
View Replies !
Float Field Not Taking Dots, Just Commas
I'm trying to add google maps coordinates to a float field, but I think due to my character coding it's not taking dots, and the coordinates come in a format like "-12.345678" it's a 10 character float with 6 decimals. What should I do to use a dot as a separator and not a comma?
View Replies !
Nested Select Statement Taking Hours To Execute
I am running a sub select statment so I can compare a couple of tables. If I run the statement from two test tables of 1000 records in each, it will take nine seconds to get the results that I need. However, if I take the same statement and point it to the two tables which contain all the data I need to run against (50560 and 161680 records respectively), it takes hours to run...how long I'm not sure. I fired the statement off yesterday afternoon @ 3pm and it is currently 11:30am the following day...the select statement is still executing. Does anyone have performance on something like this?
View Replies !
Performance Issue 2217728 Records :: Selects Taking Half An Hour
I am trying to execute following statement, which is not getting completed even after half an hour. SELECT FIND_IN_SET(t.seq, (SELECT GROUP_CONCAT(xx.seq) FROM indiv xx WHERE xx.unique_id = t.unique_id)) FROM indiv t indiv table has around 2217728 records. I have already index on unique_id. What should i do to speedup this statement?
View Replies !
GIS Indexing..
I have been testing mysql's GIS support for quite some time now. I have one question. Suppose I execute this simple query: select * from node where MRBWithin(node.shape_geo, LINESTRING(0 0, 200 200)); This will select all node records that are bounded in in the ractangle (0,0), (0,200), (200,200) and (200,0). Is there anyway to get mysql to return the results in the order of 'closest to the center point of MBR'? Ie the center point (100,100). The result set would be something like: (101, 105) (90, 80) (20, 35) (199,173)
View Replies !
What Is Key Indexing
I hope this is not a "too complex" questions, so please help me with these: 1. What is key indexing (in MySQL)? 2. Assume I already have a database (and all tables inside), how do I perform a "key indexing" in my database? 3. And can you give me one simple sample of implementation?
View Replies !
Indexing FKs
Should I index fks seperately in the linking table (in relation n:m) (if I already indexed fks usign pk). heres an example: [student]-1---n-[student_lessons]-n----1-[lesson] CREATE TABLE students_lessons ( student_id INT(11) NOT NULL, lesson_id INT(11) NOT NULL, PRIMARY KEY (student_id, lesson_id) KEY fk1(student_id), /*add this??*/ KEY fk2(lesson_id) /*add this??*/ )"
View Replies !
Use Indexing
I will make an online game with max 5000 accaunts and max 20000 character. Online users will be always 200-300. I want to make it with mysql becouse if I use array and it crashes all the data will be lost. I am new in Mysql. I want to know how to use indexes (How to get the id of a character and how to get character data of an ID). There was many tutorials on http://dev.mysql.com/doc/ for indexes but I cant understand it! :S If I use fixed row length would it be faster? Can I get memorypointers to speed up indexing?
View Replies !
Indexing?
I have a Database Table consisting of 16 rows. One is a Card number row which is Unique. The database size is over 3lac. I want to Optimize the database. I read about indexing. How to go about this?? Do i do single indexing or double?
View Replies !
PHP/MySQL Sorting By Date & Time (using Non Military Time)
This is probably a simple issue but I have searched online and can't find an answer. I am using PHP/MySQL and I guess the most intuitive would be to have three select boxes containing HOUR / MINUTE / AM,PM option. Then store the time into mysql using there functions so I can output the data sorting them all by date and time. All the examples I have seen are for military time only but I'm sure there has got to be a simple mysql function or php function that converts non military to military and a formatting function to display with the AM / PM. But I have not found it.
View Replies !
Compare Time Posted With Current Time Not Working
May I know how to get records with interval of 1 hour in database where the posting time is in this format '2008-05-15 00:10:40' I tried with this : $query="SELECT date,name FROM message WHERE (timediff(date,NOW())<=CRUDATE() CURTIME())"; But is not working. May I know what is the correct way of doing it?
View Replies !
Specified Key Was Too Long?
when I change the column datatype to varchar[1024], and set it as primary key, mysql 5.0.12 give me a error. Specified key was too long; max key length is 1024 bytes. but how can i increase it, because when i use sql sever 2000, it can work.
View Replies !
Specified Key Too Long ...
I'm migrating a client's cms to a different hosting provider. Their database for the site was originally built on MySQL 4 structure. Their new provider let's me choose between 4.1 and 5.0. I chose 4.1 but have trouble importing it. I'm using phpMyadmin ... and I got this error: #1071 - Specified key was too long; max key length is 1000 bytes
View Replies !
MySql DB Indexing
I'm a bit confused about indexing using MySQL.I have something like this: Table: PEOPLE Values: user_id first_name last_name email I put an index on user_id to speed up my search for: select first_name, last_name, email from PEOPLE where user_id=xxx; Once I set up the index, does it only hold true for the data that was currently in the table at the time the index was created? In other words, if I add 100 more users tomorrow,will I have to rebuild the index on user_id, or does MySQL automatically adjust for that?
View Replies !
Indexing Process
Is there a way to check the current status of the indexing process in MySQL [Ver 12.21 Distrib 4.0.15, for apple-darwin7.2.0 (powerpc)]? I am indexing a huge database and I would like to have an estimation of the end of the process. Is the indexing time linear or should I expect any special delay?
View Replies !
Fulltext And Indexing
I have a database with three text fields in it , each with a fulltext index. If I do a fulltext query against one of the fields, the explain says it will use the index. If I do a query on more than one of the fulltext fields, it doesn't use any of the indexes. What am I missing here?
View Replies !
Fulltext Indexing
If I have a query like: Quote: SELECT ided, merch.mid, mer, cost, ran, name, imgurl, brands FROM merch, products WHERE products.mid=merch.mid AND mcat='$cat' AND group=1 AND cost BETWEEN '$min' AND '$max' AND approved=1 AND MATCH (brands, cat) AGAINST ('$keyword' IN BOOLEAN MODE) How would I set up the index keys. I have done a fulltext index key on "brands, cat" Is this all I have to do or will I need to also do an index on "mcat, group, cost" so that it speeds up the query. I am doing this for optimization and want my queries to be as fast as possible. Take in mind that approved is from the merch table and all the columns from both of the indexes I mentioned above are from the products table.
View Replies !
Using B-tree Indexing
If I use a b-tree index on a memory table, then the index will work with column comparisons such as <= and >= and <>? I am just trying to make sure, because memory with hash indexing, only make use of the index when using = and <=>.
View Replies !
Indexing Two Columns
I have a table with two columns, x and Y. Could someone please elucidate the difference between CREATE INDEX joint_index ON mytable (x,y); and CREATE INDEX index1 ON mytable (x); CREATE INDEX index2 ON mytable (y); as far as a reflexive join of mytable - e.g., '..on a.x = b.x and a.y < b.y', would be concerned - or just point to a good online reference on indexes?
View Replies !
Frequent Indexing
My table has ~ 2 million records in it. Out of those records, only a relatively small set (~200) are queried and updated frequently. The rest are queried but almost never updated. Furthermore, I can reliably predict which of the records are going to be frequently updated and which ones are not. The issue is that even though only the same 200 records are being updated, the indexes have to be adjusted for all 2 million of them. To speed things up, I'm considering two options: (1) Split the table into two and have my web app switch between them as necessary. (2) Add a frequently_updated field to my table and index based on that. I'd like to use option 2 since it's a lot less likely to break something in the app, but I'm not sure how it compares performance-wise. Let's say for option 2, all indexes are of the form [frequently_updated, other_field(s)]. How then is the index structured? Specifically, if I add or update a record where frequently_updated = true, is only a small part of the index updated (e.g. only one branch on a tree) or is the entire index updated?
View Replies !
Scheduling Indexing?
I've been using mysql for a while, but to be honest I've never stored a load of data into it. Untill now. Now my project is taking off and I'm adding about 50,000 rows a month. i.e. 70 or so an hour.
View Replies !
Auto Indexing
Are primary and foreign keys auto-indexed for MyISAM and InnoDB? Mysql reference manual says “Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use R-trees, and that MEMORY tables also support hash indexes.†If I understand correctly, primary key is auto-indexed but what's about foreign key? Actually I need to add indexes to my tables; if mysql internally maintains for both key types, I will not insert additional/same indexes.
View Replies !
Database Indexing
I have set up a very large MySQL database, with over 180MB of data. This data is read by my PHP script and converted into an XML file. These XML files tend to be between 25 and 35KB once generated. My knowledge of databases is average. I've taken one relational databases course, but not the "advanced" one, so I need help indexing them for best performance. Hopefully someone can help. (If not, hey, it was worth a shot). Here is the structure for four of the tables (5 total tables, but the other one isnt currently in use). Code:
View Replies !
Indexing MySQL?
I keep on hearing alot about indexing mysql but can't seem to figure out how to do it, I'm running mysql on linux redhat 7.2 could some one explain to me how to do this as i'm not very familar with mysql administration tasks.
View Replies !
MySQL Indexing
Had much experience with indexing tables for optimality, but the product-search on a site I've been involved with is painfully slow, and I want to speed it up. It's based on OSCommerce, which is woefully inefficient, but there's nothing to be done about that, so will have to struggle with the database. The particulatly noticeable slowness is (ironically enough...) with the 'quick search' functionality. The query is essentially: SELECT p.image, pd.name, pd.short_description, p.price FROM products p JOIN products_description pd ON (p.id=pd.productid) WHERE pd.lang=1 AND pd.name LIKE '%keyword%' OR pd.description LIKE '%keyword%' OR p.model LIKE '%keyword%'; It's annoying that the system uses two tables when (in this case at least, with only one language) one would suffice. When in the context of the page, a search using the query as above takes about 15-20 seconds to load (this is running on a more than adequate server, on a local network), running just the query from the MySQL console registers about 7 seconds. What should I be indexing? If it were ANDs joining the WHERE-conditions then it'd be simple, but is there anything that can be done? The OSC paging-system does a SELECT COUNT(*) FROM... using the same query as above, and this is rather slow as well, so any pointers in that direction would be appreciated. I'm more familiar with postgres, and so don't have much of a clue about FULLTEXT indexing? Would this be of any benefit? The databases contains around 50k products and accompanying descriptions so while not tiny, is not exactly huge and shouldn't be this slow.
View Replies !
Indexing For Sorts
should you make a column that will always be sorted into an index? for example: a column that counts the number of views to a particular item. (in this case, the most common usage will be to fetch the item that is most viewed... my method SELECT * FROM counter SORT BY count DESC, LIMIT 1
View Replies !
|
TRACKER
