How To Set An Index On Table To Improve The Performance
i've got a table with about 500 mio records. there are 3 col. decimal,decimal,int. my aim is to find data which fits best to the two decimal col.
in which way should i set the index for the best performance? at the moment my query needs sth about 5min and it shouldn't need longer than 10 sec. is it possible to improve the performance only by using the correct index.
View Complete Forum Thread with Replies
Related Forum Messages:
Best Way To Improve Performance On Order By?
I hope someone can help me with the following problem. (Note: I will simplify my table structure to the essential) I have two tables, one containing objects and one containing the objects a user has, so basically I have * table_userObjects with PRIMARY KEY idUserObject INDEX ON idObject (not unique) * table_objects with PRIMARY KEY idObject INDEX ON name What I want to optimize is the query which gets the objects for one user and sorts them by name. For example: SELECT * FROM table_userObjects,table_objects WHERE table_userObjects.idObject = table_objects.idObject AND table_userObjects.idUser = 3 ORDER BY table_objects.name LIMIT x,30 The db has around 40000 different objects and the top users have 200000 different items. In this cases it takes around 6 seconds to run the query. If I leave out the orderby no sorting has to be done and the query runs fast as expected. Is there anyway to create an index on table_userObjects, based on the name of the objects from table_objects? Or some other way to speed up this query?
View Replies !
Indexes To Improve Performance
I am looking to add indexes to my database to try and improve performance. Now I heard someone say once that you should add an index to any column that you are planning to filter using in the where part of your statements. So my question is this, in below is an example i have a table that I use to store reports that are generated by the system (i haven't chosen this especially but it has the core element that i user everywhere else). These core elements and columns that i regally in a where clause have '*' next to them. As such if i was following the advice of were to add indexes, i would probably add one to each of these columns. Then there are the other three that have '+', which occasionally i conduct a '%%' (wildcard) search on to help me find a cretin report. given this, plus the index on the primary key almost every table would have an index on it. To me this seems a bit much. So how many is too many?
View Replies !
Limit Records To Improve Performance
I'm facing a performance issue, I'm using JDBC to read rows on a huge mySQL database 1.5 Million rows. The programme basically read rows by sample of 1000 rows. select * from <tbl_name> LIMIT i, i+1000 and print result in a text file. This takes 5 minutes for the first 500000 rows, 10 min for the following 500000 and 18 for the rest. Which seems to me very slow just for reading rows? I wonder 2 things: 1- is this normal for mySQL to take this time? 2- if not, is using LIMIT this way in the SQL would have an effect on mySQL performance? 2- how can I improve this performance. If you think of anything that can improve the performance on a select * from , please let me know.
View Replies !
Index To Improve Queries With AVG()
Is there any way to create some sort of index that will improve the performance of queries which use the AVG() function. An EXPLAIN statement on the query indicates that no index is being used. There are indexes on the ratings table for ID and ratings but they don't seem to be getting used EXPLAIN SELECT u.username AS username, ur.users_ID AS user_ID, IFNULL((AVG(r.rating))*(COUNT(ur.ratings_ID)),0) AS overall_score FROM ratings AS r, user_ratings AS ur, users AS u WHERE r.ID=ur.ratings_ID AND ur.active='yes' AND u.ID=ur.users_ID AND u.active='yes' GROUP BY ur.users_ID ORDER BY overall_score DESC LIMIT 0,40 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE r ALL PRIMARY 5 Using temporary; Using filesort 1 SIMPLE ur ref rating,users_ID_2,active rating 4 v2.r.ID 239 Using where 1 SIMPLE u eq_ref PRIMARY,active PRIMARY 4 v2.ur.users_ID 1 Using where
View Replies !
Performance Of UNIQUE Index Compared To "normal" INDEX
I have a table with several columns. one column "myColumn" of this table has a UNIQUE index on it. i want to use this column in many many search queries. does it make sense - in terms of performance - to add an additional INDEX to "myColumn"? or does an UNIQUE index already "include" a "normal" INDEX?
View Replies !
Large Table Performance Problem With MYSQL Performance
I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram mysql 4.1 with odbc 3.51 MYISAM windows 2003 server std edition date, account and invoice number are indexed Database size 18 million rows I am querying (selecting) columns of a date and an account our tester program that opens a socket to the Mysql database and does a select for the above n times each time the date and the account is randomized to minimize hits on records closeby. This program will perform over 1000 queries per second. At the end, the Mysql socket will be closed Even when the socket is closed each time, I still get 400 queries per second. When I enter a similar query manually a web interface, I get about 3-9 second response time. This program opens/closes a socket for each query when using EMS I get similar 9 second results. Does anyone have any suggestions Also in production, this table will be accesse for both read and write will I have problems. My testing showed that Innodb is much slower.
View Replies !
FullText Index Performance Issue
I'm running a fulltext query on a ~50000 record mysql database and while performance is usually satisfying - about 0.02 secs per query, i get a critical performance deterioration when looking for popular keywords (words that appear in more than 50% of the data) - about 0.25 secs/query. I know that the standard fulltext engine does not return any results on such words, and this is good, but this does not explain the performance issue. Any ideas?
View Replies !
Query Performance Question (using Index Slows Down?)
I have the following query processed in my app and was wondering if there is any way to make it faster. SELECT * from city where last_sync > '1900-01-01 00:00:00' AND last_sync < CURRENT_TIMESTAMP limit [FILLED_IN_BY_APP],[FILLED_IN_BY_APP]; limit increments in steps of 500: limit 0, 500 limit 500,500 limit 1000,500 etc. explain returns: +----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+ | 1 | SIMPLE | city | range | sync_dates | sync_dates | 8 | NULL | 138456 | Using where | +----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+ I've observed that the higher the limit goes (starting from limit 100000, 500) the querytime substantially increases. Note that my table has roughly 300K records. Because they have been inserted all at once, the result at the first time running this query will be everything (300k). However I've checked within my app how long it takes to execute the query and when -NOT- using an index the query times go to maximally 500ms while when I am using an index the max lies at 1 second. My questions: 1) Why is limit(0,500) (30ms) executed faster than "limit (200000, 500)" (600ms) 2) Why is not using an index actually faster than using an index in this case?
View Replies !
SHOW INDEX Strangely Improves Performance
I'm running MySQL 4.1.20 on Linux (FC4). When I execute "SHOW INDEX from xxx" statements in the client (to figure out performance problems) the performance improves markedly for the application working with the same MySQL server, but drops down again slowly but surely. This makes no sense to me, I assume there's some mechanism the statement invokes that I'd like to automate to keep the performance up without human intervention.
View Replies !
Temporary Table Performance
Since I work with MySQL 4.0... I can't use sub queries. So I have to create a temporary table to perform a complex request. The request has to be called periodically (let's say every second). The data in the temporary table must be refreshed each time I perform the request. My question is : What solution has le lower cost in terms of performance : droping, recreating the table and inserting the new data OR deleting all the records and inserting the new ones? The table contains less than 500 records.
View Replies !
Update Performance On Large Table
I have a table that is approaching 4 gigs. I have optimized as much as I can with indexes so that select statements are ok, but updating entries seems to be taking a bit of time. I have 2 questions: 1) I know about the EXPLAIN command for select queries, but is there something like that for updates? I get an error when I try to do "EXPLAIN UPDATE mytable..." Do updates use indexes the same way as selects? 2) Would breaking the table up into seperate tables and then using a merge table increase the performace? I've heard that key reads are slower on merge tables - but since I could make 4 tables of a gig each, wouldn't it be faster opening up 1 smaller file to make an update instead of a huge one?
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 !
Dramatic Performance Drop When Table > 100MB
"Simple" table: time | datetime n1 | smallint(5) unsigned n2 | smallint(5) unsigned n3 | smallint(5) unsigned msgid | int(10) unsigned msgtext | blob url | varchar(255) (tables seem to all be of Innodb type) No problem at all - as long as the table size stays below 100MB. As soon as the size reaches that threshold, performance slows to less than 1/10th of normal! At that speed, it is just not feasible. I doubt that the 100MB number is a coincidence. This is with MySQL 4.1 on XP. Anybody has a clue what's going on?
View Replies !
Table With No Primary Keys Affects Performance?
Just a quick question: does it affect performance (efficiency, etc.) at all when using a table that does not have any of fields set as the "primary key"? I'm wondering mostly because I have some database tables that don't really need a primary key at all and space-wise I would probably benefit from leaving them off, but I have no idea if somewhere that can have a negative impact.
View Replies !
Improve Speed
I need to improve the speed of my ff sql statement but I do not know HOW! I am using mysql in running this. If the records are below a thousand, I have no problem getting the info quickly. But I tried it on 700,000 records, the feedback took 22 secs.In this sql statement, I am currenly accessing the same table but I have to get the rows of certain conditions multiplied to different numbers. SELECT COUNT(IF(code='play',1,NULL))*5+count(IF(code like 'ok%',1,NULL))*2.50 + COUNT(IF(code='prev' OR code='prev_here',1,NULL))*15 + COUNT(IF(code='hello' OR code='hi',1,NULL))*15 + COUNT(IF(code='new',1,NULL))* 2.50 as sum from table;
View Replies !
Improve Query
I have this query that I use for stepping through records in a table, it selects for me the lowest, previous, next and highest record id refs for the table in question given the current record number. However it returns its query on two rows with repeated (and obsolete) data due to the case statement, I pick out the data I require using mysql_result command in PHP, but it would be much nicer if there was a way to only return a one row result with only the data I require. user_id is the autoinc field in table_t $id is the current record number being viewed PHP SELECTmin(user_id) AS First,max(user_id) AS Last, CASE when sign(user_id - '$id') > 0 then min(user_id) else max(user_id) end AS PrevNextFROM table_tWHERE user_id <> '$id'GROUP BY sign(user_id - '$id')ORDER BY sign(user_id - '$id') for completeness my PHP code for extracting the values i need is PHP $first = mysql_result($result,0,0);$prev = mysql_result($result,0,2);$next = mysql_result($result,1,2);$last = mysql_result($result,1,1);
View Replies !
Improve SELECT Command
I perform a SELECT on my database, but it takes over a minute for every run. I have to run it over 10000 times (with different values in the WHERE), so it takes way too long. A was therefore wondering if I could improve the query speed. Below you find the query. It is based on the ratio between a pixel (pix) vs. the average of its neighbourhood (from geo) in the same table (vgt) based on additional requirements (mgba,mgsc,eco). Code:
View Replies !
Improve The Execution Time
i am new to db2 want to ask questions about the performance of my sql commands for a view based another 3 views the sql commnads are as following: create view b_central_subgroup as select communicator as central_member, project_id as project_id from b_normalized_communicator intersect select initiator as central_member, project_id as project_id from b_normalized_initiator intersect select monospeaker as central_member, project_id as project_id from b_normalized_monospeaker it takes 4.4 seconds to execute this sql. it likes that the time is the summ of the other three views. Can the execution time be reduced through some other methods? If it is possible , then how ?
View Replies !
How To Improve On A Nested Select
I'm working on a simple data import tool, and I need to insert email addresses from table two into table one, if they don't already exist in table one. I figured this was a pretty easy nested select statement, but what I'm doing is getting my site taken off line for exceeding the CPU limit. Here's the SQL I'm using to get the new email addresses: SELECT distinct value, id FROM table_two WHERE name = 'email_address' AND value NOT IN (SELECT DISTINCT subscriber_email FROM table_one) There are about 4600 rows in table one, and 145,000 rows in table two. Does this seem like it would be a burdensome query? I'm not a SQL expert my any means, so is there a better way to go about this? It seemed like a simple one to me. Maybe my web host is just stingy with the CPU time.
View Replies !
How Do You Improve The Order By In Queries?
I read somewhere about mysql having to scan the table twice or something with "order by something" this was on mysql.com (as far as I remember) it said something about having it only have to scan it once but it didn't explain how to do it I really want to know, because a 500k row table of mine with a few indexes that cut it down to about 90k per topic is lagging more than I want (okay, so 1.2 seconds average page load on a 8mb connection isn't too bad, but I want to fix this order by stuff and maybe make it 0.9 or 1.0 :P)
View Replies !
How To Improve The Speed Of Mysql Query Using Count(*)
I'm using this kind of queries in mysql in InnoDB engine Select count(*) from marking1 where persondate between '2007-04-23 00:00:00.000' and '2007-04-23 23:59:59.999' and PersonName='aaa' While executing these queries from front end VB, It takes above 5 secs with 50 thousand records. How can I improve speed for this kind of queries. Is there any alternation for this command.
View Replies !
Index Spanning Table
What does "the best index spanned more than 30% of the table" mean, from the following paragraph from the docs? "Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size. "
View Replies !
Index Table Problems...
I am currently trying to query a database for two distinct keys. I know I should be using numbers to ref rather then words, but it goes really quick for one and not the other. The explains can be seen below. mysql> explain select * from event where node ='accel'; +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | event | ALL | typeDx | NULL | NULL | NULL | 656307 | Using where | +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from event where node ='gps'; +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | 1 | SIMPLE | event | ref | typeDx | typeDx | 28 | const | 164 | Using where | +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ 1 row in set (0.00 sec) As you can see the 'gps' query is quick as where 'accel' takes much longer. If I delete my index file, its the other way around.
View Replies !
Index Every Id Field In Each Table?
Right now I have 8 tables, and I was told it is best practice to put an index on anything you will be querying a lot for. I will be querying the table a lot based upon the id field, is it a good idea to put an index on each id field to speed up the process?
View Replies !
Is There A Case For No Index On A Table?
I've inherited a project that has left me scratching my head a bit, none of the tables have an index nor is there a field that can be treated as unique. It is more than a bit frustrating in trying to tabulate the info, but beyond that, it got me to wondering if there are cases when you would want to create tables without an index at all.
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 !
Speed, Table Size, And Index
I am trying to understand if table size impacts query speed on an query thats looking up an index value. Here is an example query: Select * From Holdings Where Holdings.user_id=3 Assuming there are 50 records in this table for user_id 3, will there be a difference in query speed if the whole table is 10,000 rows versus 10 million, or will the only difference be in storage size of the user_id index? Also, if there is a difference, does it only have to do with whether or not the whole table can be cached in memory, or is there more of a fundamental difference in the way the query would operate?
View Replies !
Index For A Large Myisam Table (MYD ~=7GB, MYI ~= 7GB)
I have a very large table with 66,836,760 rows (in my world), MYI and MYD files each about 7GB in sizes and even though I've created indexes for all the possible columns, some queries are still slow. I think I read somewhere that for a very large table, there is a way to configure Mysql to store the index file separately just for that table to facilitate the query. How do I do that?
View Replies !
Index And Data Table Growing
My problem: I'm using a database as a 'shared' and real time storage: a soft is writing while another soft is deleting some rows periodicaly while a third soft is reading some lines. The database is never containing a lot a rows, but the rate of writing/deleting/reading is very high (300/sec) After a moment, even if my database is empty, the index and data stay very large and the query time becomes very long. For example, a SELECT COUNT(*) display 0 after .... 40 secs !!! I found a solution to clean index and data entries and then improve performance: DELETE FROM myTable... but it is not a good solution because my database is keeping data in a real time traffic and will never be empty for a long time.
View Replies !
Change Index Using Alter Table
ALTER TABLE tmp_fund_load ADD CONSTRAINT tmp_fund_load_pkey PRIMARY KEY ( t_unit_price_date, t_fund_ref, t_term ) DISABLE; The above statement adds primary index on three fields. But the index is disabled. What is the effect of keyword disable here? ALTER TABLE tmp_fund_load MODIFY PRIMARY KEY USING INDEX tmp_fund_load_pkey ENABLE; After that the index is enabled using above statement. What would be the effect of enable keyword here.
View Replies !
Myisamchk Single Index On Table
I have a large myisam table (670 million rows) that has 6 indexes on it. I was had replaced all the data in it, and was running myisamchk when my server ran out of space on the 6th index (after 48 hours of processing). Is there a way I can run the index fix just for the 6th index, not the other 5?
View Replies !
Index Large Table And Low Memory
Experienced users warned me against possible problems with *index* definition for large tables: they claim MySQL indices are stored completely in memory, which is problematic to me : I have a huge table and limited memory size (RAM). I did the math, and the entire index just won't fit into the RAM. Unfortunatelly it will be a while before management approves hardware improvements.
View Replies !
Creating Index On InnoDB Table
I am trying to create an index on InnoDB table and it always fail. Table is quite big. Can I improve index creation process as we can in MyISAM by increasing the value of myisam_sort_buffer_size?
View Replies !
Time To Index Table - Non Responsive Server
I'm indexing a large InnoDB table of about 100 million rows. I realise this takes a long time (I'm passing the 24 hour mark about now). However, shortly after beginning this process the computer that the DB is running on has become unresponsive. It's a modern dedicated machine running windows server 2008. Firstly, does this seem like an excessive amount of time to create an index for a table of this size? Secondly, while creating an index, should the operating system still be responsive? (I'm inclined to think yes) If the answer to the previous question was "yes, the OS should still be working", do I have any option other than 'pulling the plug out' (holding in the off button).
View Replies !
CREAT TABLE And INDEX At Same Time MySQL
I am trying to add indexes to this table at ssssid, sprice, nights - is this the correct way to do it? CREATE TABLE `data_PLG` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(50) default NULL, `ksvensson` int(10) unsigned default NULL, `lineid` int(10) unsigned default NULL, `ssssid` int(10) unsigned default NULL, `nights` int(3) default NULL, `rating` varchar(10) default NULL, `currency` char(3) default NULL, `sprice` decimal(10,2) default NULL, `piccodescription` varchar (125), `brandid` int(10) unsigned default NULL, PRIMARY KEY (`id`), INDEX (ssssid, sprice, nights) ) ENGINE=HEAP DEFAULT CHARSET=latin1 MAX_ROWS=30000000;
View Replies !
SELECT INNER JOIN Performance VS Single Table SELECT
I have designed my database using a somewhat oriented approach. Rows are objects, and different type of objects are in different tables, but since my objects share a common set of fields like ExpireDate, Archived, Draft, CreateDate, CreatorID, etc; I have an 'objects' table with these columns, and I have set up a foreign key in other tables where there is a need for a row-to-row integrity. Now my question is, since I have to SELECT using INNER JOIN with object and the corresponding table, merely to filter out archived rows, I am wondering if I would be better off actually putting the common fields into each respective table and get rid of the 'objects' table altogether. I mean, is it more job for the server to actually JOIN the tables for each SELECT versus having a clean design with object oriented approach ? How bad is my design, and what are the recommendations of experts who obviously were tempted to create an object oriented database design?
View Replies !
Changing The Info In A Table With Multiple Index Columns
I have a MySQL question about changing the info in a table: I am using MySQL Server version: 3.23.58 I have a table called sign_region2 and it contains thousands of references to signs and the regions they are in. mysql> desc sign_region2; +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | id_sign | int(11) | | PRI | 0 | | | id_region | int(11) | | PRI | 0 | | +-----------+---------+------+-----+---------+-------+ This table keeps track of which signs are in which regions. Note that the Primary Key is a multiple-column index (id_sign and id_region). mysql> select * from sign_region2 limit 20; +---------+-----------+ | id_sign | id_region | +---------+-----------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 1 | 4 | | 1 | 5 | | 2 | 1 | | 2 | 2 | | 2 | 3 | | 2 | 4 | | 3 | 5 | | 4 | 5 | | 5 | 5 | | 6 | 5 | | 7 | 5 | | 8 | 2 | | 10 | 2 | | 11 | 1 | | 11 | 2 | | 11 | 3 | | 11 | 4 | +---------+-----------+ 20 rows in set (0.00 sec) I need to take all "id_region = 4" and make them "id_region = 2" except where "id_region = 2" already exists. In that case I'd just need to delete where "id_region = 4". Remember that there are thousands of references in this table. Some signs are in region 2, some are in region 4, some both, some neither. What query would I use to accomplish this? The problem is that the DB won't allow identical instances of the id_sign and id_region combinations (because they must be unique because they are primary keys). An example: Let's say I've got a table that looks like this... +---------+-----------+ | id_sign | id_region | +---------+-----------+ | 12 | 1 | | 12 | 3 | | 12 | 4 | | 12 | 5 | +---------+-----------+ But I need it to look like this... +---------+-----------+ | id_sign | id_region | +---------+-----------+ | 12 | 1 | | 12 | 2 | | 12 | 3 | | 12 | 5 | +---------+-----------+ But for thousands of records not just the ones with "id_sign = 12".
View Replies !
Using An Index Vs. Full Table Scan, Character Vs. Integers.
I've got what are surprising (to me!) results from a very simple query. I'm hoping that some of the experts around here could confirm that my understanding of things is correct. Given the table: mysql> desc my_table; +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | year | char(4) | | PRI | | | | id | char(5) | | PRI | | | +-----------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) which has a unique index on both fields: create unique index my_index_1 on my_table (year, id); and about 38,000 rows in it. and given these two queries against the table: Code:
View Replies !
|
TRACKER
