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 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 !
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 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 !
Question About Indexes And Performance Impact They Have
I read that inserts can be an issue (the speed of them) if one uses indexes. With millions of records I want to put an index on a date column and a url column, but I receive a ton of inserts (10,000+ per day). Should I avoid indexes on those fields because of that? Without indexes,
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 !
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 !
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 !
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 !
What Are Indexes?
What exactly are indexes? are they like Foriegn Keys? How do you make Foriegn Keys? How would you go about specifying the primary key link to the foriegn key? whats a good situation when indexes are most needed?
View Replies !
Two Indexes
I have a DB made up o three tables. I created a multi-column index called idx on (table1.id, table2.id). I also would like to create an index idtot on (table1.id,table2.id, table3.id) and an index idp on (table1.id, table2.id). First of all I would like to know if this is possible. If so, when I make a query like "select table1.id from table1,table3 where table1.id=table3.id", MySQL automatically chooses the right index idp to make the search, or do I have to specify it? (and if so, how?).
View Replies !
Many-to-many Indexes
You have a many-to-many between two tables. One method (which I use, and which apparently is seen as a good method) is to use a linking / joining table. Just a table with two columns, having IDfromTable1 and IDfromTable2. Now, what is the best way to index this 'link' table? Table1 and Table2 will have a primary key index on their unique IDs, and those IDs will have several matching values each on the 'link' table. Is it better to create an index on each column in the 'link' table? Or create an index which uses both columns? I get that using both columns would give me the ability to have a unique key, but performance-wise, etc. which is the better option? Tables are MyISAM.
View Replies !
Indexes
I have a few books on mysql and have succesfully now transferred all my records over from ms access. everything does work ok but it searches are a little slow (even slower than ms access which is why i moved in the first place). I have 17 tables in the database and it is all in third normal form. I have correct primary keys on all. I think indexes is the prolem here. I have added a few but i am unsure about how many to add and also about updating them. Can you please give your advice in terms of how many indexes should be used normally and how easy it is to update them?
View Replies !
Best Use Of Indexes
I have a situation where I am doing the following: Example keys: key1 key2 key3 Now "key1" is used a lot in WHERE queries with "key2" & "key3"; however they are not used all together at once.. "key1" is only used with 2 & 3 seperately. So I was wondering is it best to create 2 seperate indexes such as: key1 key2 key1 key3 ..or create one such as.. key1 key2 key3 ? I created the two seperate ones, but phpmyadmin is complaining about me having two indexes created for "key1".
View Replies !
Use Indexes?
My table has some indexes declared. How I can use them in the select queries in order to achieve better performance?
View Replies !
Preserving Indexes
I'm using phpMyAdmin for the most part. I notice that the index cardinality shows up as None in phpMyAdmin for each index I have. If I drop the index and re-create it, all of the indexes show their correct cardinality. I don't think phpMyAdmin is at fault because searching on the table before I fix it is sluggish until afterwards. Is the a command I could run that would re-index everything after I dump the data into the table? Or what is going on?
View Replies !
Two FULLTEXT Indexes
I'm building a new database and I will need a search engine for it. I wonder if it would be ok if the database contains two fulltext indexes.It's because my database will contain English document and French document.I would put two FULLTEXT indexes in my table so that way, you could do your research only on the French document or only on the English ones. So my question is, would it be better to put the two fulltext index in one table or do a table for the French articles and a table for the English articles.
View Replies !
More Than 32 Indexes With MyISAM?
I've searched Google every way I know how, and came up with two answers that refer to MAX_KEY and MI_MAX_KEY, along with a typedef for key_map. I have the 4.1.1 alpha source (4.0 is no good, because it doesn't support subqueries). I changed the two defines in sql/mysql_priv.h and include/myisam.h, respectively. The typedef for key_map is no longer ulong or ulonglong, but instead Bitmap(64); I left that alone. After compiling, an attempt to create a table with more than 32 indexes (which I absolutely must have) still shows an error referring to 32 max keys. What's going on here? I could not find anywhere in the code that generates that error which doesn't check either or both of the above defines. So why isn't it working? Is it hard coded somewhere?
View Replies !
MySQL Indexes
if the indexes of a table are dynamically updated... in other words, if I have a table updated every minute, do the related indexes are updated as well?
View Replies !
Enum Or Indexes
My question is, would there be a significant impact on optimization by removing the enum in favor of other tables.For example, in the table there is a column of type enum to represent the 50 states. Is there any advantage performance-wise to move these 50 states into a table?
View Replies !
Table Indexes
1. is this true that all the fields on which you will be doing search one should make them index? 2. is there a limit to how many index fiedls you can have? 3. i once was geting this error while creating a table "specified key can be max 500" when i got rid of index it got created sucessfully I don't know why?
View Replies !
Indexes, Primary Key
I have set up a temperature logging system with ds18s20 one-wire sensors. In the MySQL database I have a table with four columns - time as timestamp, outside, inside and server temperatures as floats. A small C program runs the Digitemp ( http://www.digitemp.com ) program every half hour from cron, parses the output and inserts now() and temperatures. The data is used by a web page, and I mostly select according to timestamp (for example, the last week).Would there be any performance to gain from creating an index on the timestamp? It is already ordered, ascending in the table. I have not set a primary key on the table either, would that be any advantage?
View Replies !
Define Indexes?
I'm wading through the waters of "MySQL/PHP Database Applications," and I still cannot get this INDEX malarcky into my head. What exactly is an index? What's a working example that would put it into clear understanding
View Replies !
Proper Indexes
I recently had an administrator on a system I built manually enter in grades twice for the same exam. I had made check to stop this when importing from a csv but did not think to check when manually entering grades. My bad. If I add an index for both Session_ID and SOMS_KEY would this fix the problem? I can't have the same SOMS_KEY which identifies a student written into the table for the same Session_ID. The Session_ID has all the exam grades for that session. Example: Session_ID, SOMS_KEY 61, 602 61, 602
View Replies !
FULLTEXT Indexes
I know that FULLTEXT indexes actually return meaningful results but I also suspect they add a lot of overhead to the database server.I can't change it for our existing application but for the next major revision I was planning to create a search table that contains all words that get entered into the database (apart from noise words) with an article ID.I think that might get a bit complicated though as it would have to return results and then scan through the text returned to highlight that word? Actually not that hard but is it worth it for performance benefits?
View Replies !
Reorganizing Indexes
I have some tables that have an index (auto increment int) and I need to reorganize them so they are in order again. Is there a sql statement for this? Or a tool? I need it because Im moving some data and I want it to be in order in the new table.
View Replies !
Duplicate Indexes
I have this table: CREATE TABLE `foo` ( `logId` int(10) unsigned NOT NULL auto_increment, `uid` varchar(64) default NULL, `activityDate` datetime default NULL, `activityType` varchar(255) default NULL, PRIMARY KEY (`logId`), UNIQUE KEY `noDups` (`uid`,`activityDate`,`activityType`), KEY `uid` (`uid`), KEY `activityType` (`activityType`), KEY `activityDate` (`activityDate`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 is the index 'uid' a duplicate index, since it is also the first index in 'noDups' ?
View Replies !
Optimizination Using Indexes
SELECT `salfldggol`.`ACCNT_CODE`, `ssrfacc`.`ACCNT_CODE`, `salfldggol`.`PERIOD`, `salfldggol`.`AMOUNT`, `ssrfanv`.`CODE`, `salfldggol`.`ANAL_T1`, `ssrfanv`.`CATEGORY`, `ssrfanv`.`NAME`, `ssrfacc`.`ACCNT_NAME`, `ssrfacc`.`SUN_DB` FROM `salfldggol` INNER JOIN `ssrfacc` ON `salfldggol`.`ACCNT_CODE` = `ssrfacc`.`ACCNT_CODE` INNER JOIN `ssrfanv` ON `salfldggol`.`ANAL_T1` = `ssrfanv`.`CODE`
View Replies !
Indexes Documentation
I've been over the documentation and am still a bit confused on the most beneficial way to set up indexes in MySQL for certain tables. Imagine I have a table that is used purely for cross-reference, containing 4 columns. A query into this table may involve joining or filtering on anywhere from one to all of these columns. Is it best to: A. set up one index for each possible combination/arrangement of the columns, B. set up four indexes, with one column in each, or C. don't index this table because it's not appropriate.
View Replies !
Creating Indexes
I have a doubt on indexes. I need to create some indexes on a series of relations I created with mysql. I have to create a index on the primary key and a index on the foreign key for each table. The problem I found is with tables of this type: I have two columns, both belong to the primary key, and only one is a foreign key. Is it different if I create the indexes this way: create index primarykey on table(column1, column2); create index foreignkey on table(column2); or this way: create index foreignkey on table(column1); create index i2 on table(column2); I mean, is it different to create a index on two columns or creating two indexes each on one column?
View Replies !
About Multiples Indexes
is it a good idea to make a multiple primary key with an autoincrement index and another key? or it's better to make a single autoicrement primary key and then another index with this autoincrement index and the another index?
View Replies !
Linking Indexes
I have two tables... tbl_one id two_id info_a info_b tbl_two id info_c And what I want to do is link `two_id`, an index to the `id` field in `tbl_two`. Then I basically want to be able to display data (specifically `info_c`) by referring to the `two_id` field. What would be the easiest way to achieve this?
View Replies !
Reset Indexes
How do I reset the indexes on a table? Because... I assume that with index skips, eg... 4-5-7-8-12... Where stuff got deleted. This code here might become inefficient? $gotConf = false; while ($gotConf == false){ $confData = mysql_query("SELECT * FROM confession WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM confession) AND active=1 ORDER BY id LIMIT 1;", $conn) or die ('Confession select failed'); $confInfo = mysql_fetch_array($confData); if (isset($confInfo[text]{2})){ $gotConf= true; } }
View Replies !
Indexes In MYSQL
Is there any way to know if/when you should set up and use indexes on a MySQL table? A chatroom I created has a post table with the following fields: id = int(11) (Primary Key) name = varchar(40) post = text timestamp = varchar(20) trash = smallint(1) invisible = smallint(1) ip_address = varchar(20) The trash and invisible fields are used in WHERE clauses to determine what a user can and cannot see. The timestamp and id fields are used for ordering. What fields should I create indexes on, if any? I'm looking for a general rule of thumb, so that I can make the necessary indexes on all my SQL tables.
View Replies !
Searching And Indexes
I have a fair sized table now (1,955,041 rows) and it currently has two indexes: PRIMARY is the ID number and Keywords is a FULLTEXT index of the Keywords column (Text). In my first query type I also check two other fields: Disabled and Toplevel SELECT * FROM files WHERE Disabled='0' AND TopLevel='clipart' AND MATCH (Keywords) AGAINST ('+tree' IN BOOLEAN MODE) ORDER by ID; In my first query type I do not have keywords but am rather checking to see if an image belongs to a particular category like so: SELECT * FROM files WHERE Disabled='0' AND TopLevel='clipart' AND (Category='Food' OR Category2='Food' OR Category3='Food' OR Category4='Food') ORDER by ID My question is, are there any other indexes (or anything else) that I can do that will speed up the results? +----------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+------------------+------+-----+---------+----------------+ | ID | int(20) unsigned | | PRI | NULL | auto_increment | | Disabled | char(2) | | | 0 | | | TopLevel | varchar(50) | | | | | | Keywords | text | | | | | | Category | varchar(100) | | | | | | Category2 | varchar(20) | | | | | | Category3 | varchar(20) | | | | | | Category4 | varchar(20) | | | | |
View Replies !
Difference In Indexes
What is the difference between these two types of indexes? ALTER TABLE mytable ADD INDEX ( myfield1 ) ALTER TABLE mytable ADD INDEX ( myfield2 ) and ALTER TABLE mytable ADD INDEX ( myfield1, myfield2 ) or is there no difference?
View Replies !
Hint MySQL Which Indexes Use
EXPLAIN SELECT A_id, B_id FROM A, B WHERE B_id = '\pathsubpathmorepath01-0466.html' AND A_id = 4 AND B_code=A_code; Things get complicated when more than one tables are involved. Let's say I have indexes for A_id, A_code on table A, and an index for B_id on table B. How can I hint MySQL that which indexes it should use? In the example above, I expect one index on table A and one index on table B are actually used. When I looked at it with the EXPLAIN command, I found out that only the index for B_id is used.
View Replies !
|
TRACKER
