Table Gets Slow At Around 60k Records
I've a table that starts getting slow with count(val) and select ... order by when it has around 60k records. Table type is innodb
Strange enough, at 50k records the response time is great, but looks like it grows exponentially after that.
Its something like
50k records - 0.2 seconds for count(val)
60k records - 3 seconds
100k records - 20 seconds
I realize order by and count forces mysql to go through the entire table...
View Complete Forum Thread with Replies
Related Forum Messages:
815,000+ Records, DB Table Query Too Slow
I have a database table with about 815,000 records in it, each record containing the source of individual webpages (with all the tags and multiple spacing stripped out leaving just the words). At first my match boolean queries were fine but once I got to around 100,000+ records it became too slow to use for a web-based search tool. I was able to separate the data into 10 tables and then use AJAX to run the match query on all 10 tables at the same time and that has increased performance. My question is, is it possible for me to increase the performance for the 1 table some how instead of resorting to separating the data into multiple tables? Is MySQL the right DB for this kind of setup, would MSSQL or PostgreSQL perform better for larger sets of data? I'm not sure how MySQL 5 clustering works,
View Replies !
Slow Select Using Count(distinct) In A Table Bigger Than 100000 Records
Recently I started using MYSQL in my enterprise. I made a table which has around 100000 records. The problems is that it is really slow.. Im trying to do a query in which I get the number of distinct users per day. This is my query: select date(startedDate) as mydate, count(distinct(Users)) as users from Mytable group by mydate It is really simple and it does it correctly but it takes one minute.. One minute is not too much time but i need to insert around 10 000 000 records and thats what worries me.....
View Replies !
Optimizing Slow Queries On Millions Of Records
I have a table that has millions of records in it. About 100 records are added every 5 minutes (one per OIDID) (the sample provided below has data for 2 OIDIDs (99 and 100) And I have a webpage that executes 9 queries one after the other, and then displays the results on the webpage. When the database was empty, this process was very quick. But, as the DB grew, it became slower. Now it takes about 38 seconds for all queries in the example below. MySQL 4.1 on Windows I have tried different indexes, and they do help and are used, but the queries still seem to take a long time. Code:
View Replies !
Slow Queries, 4 Million Records, Need Educated Advice!
I have created an app a few years back to store some records in a DB. According our calculations we were never to exceed 500,000 records in the DB. Seems we were off by a decimal point or so. I set up a FreeBSD box with MySQL three years back and it has been filling up. One table has over 4,000,000 records. Yes, four million. As a web developer and not a DBA, I have struggled to upkeep the server the best I can. As of the last one million records the server has been struggling to keep up with multiple requests and as you can imagine the user base is growing too. Hardware: Dual Xeon 3.06 Ghz 4 GB ECC RAM 800GB RAID5 SATA array Software: FreeBSD 5.3 Apache 2.0 PHP5 MySQL 5.0.2 Basically I have to perform a search on one of two columns in this huge table (10 columns, 4 million rows). The table is MyISAM with a single primary key that is used largely for updating row data. Most records are ten digit numbers for one column and a ten digit varchar for the other, but sometimes either column can be a series of characters up to 100 chars long, so each column is set for varchar(100). The action performed is SELECT count(*) FROM tableName WHERE col LIKE "%123%" then... SELECT * FROM tableName WHERE col LIKE "%123%" LIMIT 0,25 with "%123%" being any random string typed into a search window.
View Replies !
All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I can usually figure out a select statement on my own. I have one today though that really has me stumped. I am working in MySQlL 5. In My first select statement I get all my records from Table B SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` In my second statement I get my records that match in this case phase 401 in Table B and all my Table A records for phase 401. SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` Where table_A.PHASE In ('401' ) Now I need to combine the Data of both recordsets. I need EVERYTHING in Table B, but I also need All Table A records that match the phase selection.... Can I write this one query or do I need to use a Temp table?
View Replies !
Selecting Records Belonging To Multiple Records Through Join Table
I have is 3 tables, 'categories', 'levels' and 'categories_levels', the 'categories_levels' table is a join table, with the `id`, `category_id` and `level_id` fields. Categories can belong to many different levels, so records in the join table might look like this (assuming that the category has an ID of 5) id, category_id, level_id 1, 5, 1 2, 5, 2 3, 5, 3 So you can see that category 5 belongs to 3 different levels (1, 2, and 3). On a web page, I've got a multiple select box, which allows a user to filter the categories depending on which levels they belong to. What I need a query to do is use that join table to find all categories which belong to the selected levels. Now for the tricky bit. Only the categories which belong to every single selected level should be displayed. So in the example above, if I select level 1, then category 5 will be displayed, but if I select levels 1 and 4, then category 5 should not be displayed, because although it does belong to level 1, it doesn't belong to level 4 as well.
View Replies !
Very Slow Table Update
I have two tables. One is really a subset of the other. However, they came in different data files and I would like to pull data from one and put it into the other. However, it is VERY slow! Once the tables are setup I will only read from them and perform operations. I will never update or insert. However, I can't get things setup to that point. Code:
View Replies !
Slow Table Access On Local Machine
I'm running MySQL 4.1.7-nt on my laptop, and I'm accessing it through ODBC (MyODBC-3.51.10-x86-win-32bit). I'm developing a .NET web application, but I don't think that's relevant. I already had the application running OK on my desktop, and when I created the same environment on the laptop, retrieving trivial amounts of data (5 rows) from a table is taking 6 seconds. Updates the same. IN otherwords any table access takes 5 or six seconds. (Table only has about twenty rows in it). I can connect to the database on the laptop from the desktop (i.e - application runs on desktop, retrieves data from laptop - works fine, not noticeably slower than local on desktop. If I connect the other way - application on laptop, database on desktop, it runs just as slowly as locally. The effect is the same whether I connect to 127.0.0.1 or via the machine name. Code:
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 !
Slow SELECT Query INNODB Table
I have a couple hundred connections doing "SELECT [Char36 Field], [LongLong Field], [Long Field] FROM [Connection Specific Table] WHERE [NonIndexed VarChar36 Field]=[Value]". Notice that the table the select statement is being called on is unique to each connection. These tables have less than 10000 records, but this statement can sometimes take over an hour to execute. It is an INNODB table.
View Replies !
Slow FullText Search When Table Grows Constantly
I have a small issue with a web-search engine I'm working on. The main table is constantly growing (1 insert per second, currently 150 000 records) and it has full-text indexes on 2 fields that contain over 20 000 characters on each row. The thing is, as I could observe, that when multiple different full-text searches are made in appropiate period of time, the query doesn't take so much to respond (though i couldn't quite say it's fast), but when no search is made, let's say, for over 2-3 hours, the same query takes up to 30-40 seconds to respond. The query is simple: .....
View Replies !
Multiple Table Calls And Slow Loading Times... Joins?
just recently started using MySql and I think I've got most of the basics down - everything WORKS just not well. Essentially, I'm making an image gallery and the search/landing pages have thumbnails, pretty straightforward. The thumbnails are rollover slideshows of the images in the gallery so for each thumbnail preview there are a varying amount of actual thumbnail images that are loaded. The table structure is to this effect: gallery table: gallery_id, gallery_views, gallery_rating etc etc thumbnails table: gallery_id, thumbnail_link So i may have an entry as such: gallery_id = 21, gallery_views = 300, gallery_rating = 60 With several thumbnail entries: gallery_id = 21, thumbnail_link = url/image1.png gallery_id = 21, thumbnail_link = url/image2.png gallery_id = 21, thumbnail_link = url/image3.png What I have been doing thus far, say for the index page I grab the top 30 by views, is something like "SELECT * FROM galleries ORDER BY views DESC LIMIT 30" and then after bringing that into PHP in my while statement on each iteration I make a separate call that is like "SELECT thumbnail_link FROM thumbnails WHERE gallery_id = '$gallery_id'" and then output all the thumbnails for the rollovers. So let's say then for example that I want to show the top 30 by views, as well as the top 30 by user rating. At that point I'm making two calls to the galleries table, which arbitrarily we'll say contains 50,000 entries, but I'm also making 60 individual calls to the thumbnails table, which contains in some cases 20 or 30 thumbnail links per gallery and contains upwards of a million rows. Obviously my loading times are much higher than I would like them to be and I can't imagine this is the optimal way of making these calls. It seems like a fairly elementary concept but I can't seem to find something that works.
View Replies !
Why LAN Clients Frozes While A Slow Networked Client Downloads A Big Table
With MySQL 4.1 I think from a day to another something changed in the working of our server. When a remote agent with very slow Internet connection downloads 60.000 rows query lets say downloads the whole "products" table, all of the other fast LAN clients stop responding until the big download finishes. It is possible, that they would like to change the "products" table while the remote client downloads it... But! There is no lock to the table, just its a simple "SELECT a,b,c,d FROM table ORDER BY a" query.. Can it happen because of that the table becomes bigger and bigger, and now MySQL can't copy it to in the RAM, and let others live, and now it stops other clients instead?
View Replies !
Inserting Data Into Existing Records Of A Table From Other Table
how to insert data from one table to an existing records of another table.... In specific how can i insert data into a table....in such a way that this data should not be inserted as a new record,rather it should be inserted into the existing records(these records at first contains only two fields of data..the remaining fields contain default values) my question is that how to insert the data into these remaining fields from another table.(more specifically what is the insert statement used).
View Replies !
Finding All Records In One Table That Don't Have A Corresponding Record In Another Table
I have photo information stored in a table called pictures. One column is keyword. Visitors to my site can search for photos by keyword. I record search terms in another table called searches. Each search term is recorded, meaning there are duplicate search rows. This allows me to see easily the popularity of a search term. Step 1. What query can I run to find all search terms that return no matching picture in the picture table. I tried the following, but no luck:Quote:select term from searches left join pictures on term like keyword where picture_id is NULL
View Replies !
Slow Queries When Using Views Compare To Direct Table Queries
I’ve having problems with my server load for a while now. I have two tables with different content, but I need to display them in the same results, so I created a view with a union all (named: top_news_videos). The problem that I’m seeing is that when running a select to the view it takes a lot longer (and in that way, more server intensive) than running the query directly to one of the tables. For example, I created a page where I run 7 queries similar to this one: MySQL Code: ...
View Replies !
Select All Records From One Table That Does Not Appear In Another Table
I need to select all records from tableA that does not appear in tableB. I am using the following query that does work but is very very slow. Is there anything i can do to speed up the query? select tableA.ID, tableA.Name, tableA.Surname from tableA where tableA.ID != ALL (select tableB.ID FROM tableB where tableB.status = 'inserted' or tableB.status= 'edited' or tableB.status = 'deleted' ); I have as example 6000 records in tableA and 2000 records in tableB. tableB is used to track which records have been inserted, edited or deleted from another system so the query should return all records that do not exist in the other system yet. This query runs for about 3mins and just gets slower the more data there is. Can anyone suggest anything that could make this a sub second response?
View Replies !
How To Copy Records In One Table?
does anybody know how to copy a record within one table? I am looking for some simple method similair to INSERT INTO orders SELECT * FROM orders WHERE Order_ID=256; Actually, this method does not work and I can not believe that MySQL does not offer some simple method how to do it. Keep in mind that I have a 60-field table. So, naming all fields is a pretty tedius task.
View Replies !
Update Records In Table
i have installed a mysql server, and i have a problem with import record in database. the origine is a file txt or csv, the destination database contain also other records, and i must update the records into database
View Replies !
Joining Table Records
Migrated an MS Access database (which has linked/joined table records). Reviewing the records within MySQL Query Browser, I noticed there's no obvious indication of the table records still being joined/linked; nor did I find a way to recreate them. I need to know that a link exists before I begin coding PHP. (Which is also new to me.) Don't want to run into a problem with having the correct coding but the wrong answer based upon the lack of links.
View Replies !
Limiting Table Records
Is there a way to limit the number of record in a table, for example i want 20 in my table, if a new one comes in then the oldest one just gets dropped.
View Replies !
Different Languages In Table Records
I wanted to add different language support for my application and for that i needed to add text in languages like Arabic and Hindi into the records in a table in my Mysql Database. I tried simple copy paste in MySQL administrator but it did not recognize the characters.. how do i enable support for the language's character sets?
View Replies !
Counting Sub-table Records
I need to write a SPROC that will be pulling information from several tables, altering formats, and inserting into new tables. The query is several hundred lines long. Most of the tables have a related index field. What I need to do is use a few LEFT JOINs during the SELECT for one of the tables. I need to know, for this table, how many records from some of the other tables have matching Id numbers. I am having trouble figuring out the COUNT statement. Code:
View Replies !
Posting Records To Table
Im building a PHP page where Im allowing visitors to input information using a online form. With MySql The table is got the first field primary (id) 1-8000 records there are another 22 empty fields in this table. year, color etc... each (id) field is unique to a product model Im asking how is it possible to post multiple entries to the unique (id) field? I would then be able to query the table and display entry after entry for the unique id field.
View Replies !
Remove Records From Table
If I have a database called 'DB' with a table called 'table1' - how would I remove the records from table? I still want to use the database and table again. this goes in a cron job and I need the table to be empty before the next run of a script.
View Replies !
Pull The Last 10 Records From A Transactions From A Table
I'm trying to pull the last 10 records from a transactions from a table using this query: SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10 But I want to display the rows in ascending order by timestamp. I can't get the subquery below to work and not sure why: SELECT * FROM (SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10) ORDER BY timestamp ASC;
View Replies !
Cleaning Up MySQL Records In A Table
I transfered a mysql database to a new mysql server using a little software... Thing is, it put some garbage characters at the end of every record and it messes up when I try to echo the result in PhP.I'm looking for a way to remove the unwanted characters (or non-alphanumeric characters) in each record.Is there an easy way to do this ? I'm also using phpmyadmin, if it can be usefull.
View Replies !
500 Million Records In A Single Table?
I really need a suggestion from you. I need to store more than 500 million records in a single table. These are basically historical data of different stock market for last 40 years. Initially I thought about partitioning for each company. But there are more than 1024 companies. At the same time if I partition the database based on year then to show the chart for a particular company I need to call almost all the partitions.
View Replies !
Combine Records Of A Column Of The Same Table
i have all my portal contents on the SQL Database in the following manner: Node_id Contents 12 this the first page 12 welcome to the portal 12 this is some sample data 13 this the second page, 13 sample data when a page is being loaded, depending on the node_id contents are retrieved and displayed. Now i need a query which would combine all records of the same Node_id (page) and display as one record. Eg: Node_id Contents 12 this the first page welcome to the portal this is... 13 this is the second page, sample data
View Replies !
Counting Records In Related Table
I'm trying to construct a single query that returns all records from one table plus a count of all corresponding records in another table. I have a table of members (members) and a separate table (traffic) that tracks what each member has downloaded from the website I've created. Here is the current SELECT statement I'm using: SELECT * , COUNT(*) AS traffic_count FROM `members` , `traffic` WHERE members.id = traffic.member_id GROUP BY members.id ORDER BY traffic_count This almost works for me. The problem is that it only returns results for the members who have corresponding records in the 'traffic' table. This is a great start but I'm trying to return all records from the 'members' table including a corresponding 'traffic_count' variable for each member. This means that for each member that has no entries in the 'traffic' table I'd like the 'traffic_count' variable to be 0.
View Replies !
Only Showing Records That Do Not Exist In Another Table
How do I show a listing of all records within one table that do not exist within a second table? For example: Table A ---------- apple orange pineapple banana Table B ---------- orange cherry lime banana I want all records in A that do not exist within B to show. thus I should have the results of: apple pineapple What would my sql statement be if my column name was "fruit"?
View Replies !
Best Way To Select Records WITHOUT Relationship In Another Table?
I have a stock table and a stock_category_r table which is a relationship table between stock items (products) and stock categories (M:M relationship) I need to select stock that is uncategorised. I.e Items that have not been related to ANY categories. I was using this query with a subquery: SELECT s.ID, s.code, s.name FROM `stock` s WHERE s.ID NOT IN (SELECT stockID FROM stock_category_r) Just wondering if this was the most effecient way to find stock records that have no corresponding relationship in stock_catgegory_r ? Do sub queries effect performance much? My tables for this app have very few rows, but I'm interested in the best-practice theory The table structure: CREATE TABLE `stock` ( `ID` smallint(5) unsigned NOT NULL auto_increment, `code` varchar(16) NOT NULL default '', `name` varchar(40) NOT NULL default '', `description` mediumtext NOT NULL, `image` varchar(35) NOT NULL default '', PRIMARY KEY (`ID`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `stock_category_r` ( `stockID` smallint(5) unsigned NOT NULL default Ɔ', `categoryID` tinyint(3) unsigned NOT NULL default Ɔ', PRIMARY KEY (`stockID`,`categoryID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
View Replies !
Help With Performance Of Table With 200k Records
My client is experiencing severe performance issues going through a sales process. the order/cart table has around 200,000 records and is modified as you move through the process. Performance for multiple item orders takes a minutes to refresh. Their server version is 4.0 and the table has a primary index. (I am moving it to at least version 4.1) It is running PHP as the scripting language and I am going through logs to see what's up there as well but the performance issues have been ongoing and getting worse so it leads me to believe it's a db issue.
View Replies !
Join Records Form Same Table
I have results from a mySQL database that I am exporting to a CSV. These records are sales form an eCommerce store (ZenCart). What happens is that attributes for each part of an order make a new row for each attribute. Like this: .....
View Replies !
How Many Records Can A Single MySql Table Hold
I have a mysql database running on Mysql version 3.23.53 on a Red Hat Linux 7.2. In the database there is a single table with over 150,000 records. Unfortunately, the performance of that table is very poor, comparing to other tables in the same database.
View Replies !
|
TRACKER
