Index information in query results?


,

Basically I'm trying to get an idea of how effective an index on a table will be versus the memory it consumes. What I'm interested in getting is some kind of data back about how the index was used when I do certain queries.

For example, if I do a select for table A on column B, I'd like to see that the index was used to find all table A rows with a column B value of X. Preferably I'd like to be able to see what portion of a query's execution time is spent performing index look-ups, or the cost of altering the index when inserting/updating a row.

Is it possible to do this in MySQL Currently the only method I can think of is just creating a set of test-queries and measuring the time with or without a particular index myself, to see what kind of difference it makes. This isn't really preferred though as it's quite complicated for me to set such a thing up at the moment.

As a side-question for my particular case; if I have a primary key index defined for columns A, B, and C of a table, will that help to optimise queries that are matching only on column B, or is a separate index required to improve that case

Posted On: Monday 29th of October 2012 05:43:09 AM Total Views:  143
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Help with query optimize and indexes

Okay, i would like some help with setting up proper indexes. Here is my setup. Got 2 tables: mysql> describe archive; +--------------------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+------------------+------+-----+---------+-------+ | archid | int(11) | NO | PRI | | | | name | varchar(255) | NO | MUL | | | | added | int(11) | NO | MUL | 0 | | | section_id | int(11) | NO | MUL | 0 | | | nukk | varchar(255) | NO | | | | | flag | int(11) | NO | MUL | | | | arcreason | varchar(255) | NO | | | | +--------------------------+------------------+------+-----+---------+-------+ 27 rows in set (0.00 sec) And: mysql> describe sections; +------------------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------------------+------------------+------+-----+---------+----------------+ | section_id | int(11) | NO | PRI | NULL | auto_increment | | section_name | varchar(64) | NO | MUL | | | | section_stats | mediumint(2) | NO | | 0 | | | stats_synced | enum('YES','NO') | NO | | YES | | +------------------------------+------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) The archive table holds around 2 mil entries and the sections table holds around 40-50 entries. My current indexes on archive is: mysql> SHOW INDEX from archive; +---------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+ | archive | 0 | PRIMARY | 1 | archid | A | 2917498 | NULL | NULL | | BTREE | | | archive | 1 | added | 1 | release_added | A | 2917498 | NULL | NULL | | BTREE | | | archive | 1 | section_id | 1 | section_id | A | 31 | NULL | NULL | | BTREE | | | archive | 1 | archiveindex | 1 | name | A | 2917498 | NULL | NULL | | BTREE | | | archive | 1 | archiveindex | 2 | added | A | 2917498 | NULL | NULL | | BTREE | | +---------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+-------- my select statement is: SELECT r.name,r.flag,r.archreason,r.nukk,r.action,s.section_name FROM archive as r, sections as s WHERE r.name LIKE 'my%search%word%' AND r.section_id = s.section_id ORDER BY r.added DESC LIMIT 1 EXPLAIN prints: mysql> explain SELECT r.name,r.flag,r.archreason,r.nukk,r.action,s.section_name FROM archive as r, sections as s WHERE r.name LIKE 'my%search%word%' AND r.section_id = s.section_id ORDER BY r.added DESC LIMIT 1; +----+-------------+---------+-------+---------------+--------------+---------+------+--------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+--------------+---------+------+--------+------------------------------------------+ | 1 | SIMPLE | archive | range | archiveindex | archiveindex | 257 | NULL | 102869 | Using where; Using index; Using filesort | +----+-------------+---------+-------+---------------+--------------+---------+------+--------+------------------------------------------+ It shouldn't have to go through over 100 000 rows to find that searchstring And also heard that range as type isn't good. Anything more thats good to look out for here in explain Same with using filesort isn't good.. My main goal is to speed things up as much as posible. So what columns is best to have as indexes and which are not Anything i could do to my select to speed up things Can also add that name in archive should be uniqe also the added and id columns are uniqe Best regards Patrik
VIEWS ON THIS POST

114

Posted on:

Wednesday 24th October 2012
View Replies!

Question on building indexes

I'm building a search form, allowing users to search on any of a dozen or so search fields. I'm wondering if I should build an index for each field, or one big index containing all the fields. Here's a sample of my PHP code: PHP Code: if($search_by_name) \t$sql[]="`name`LIKE'$name%'"; if($search_by_city) \t$sql[]="`city`='$city'"; if($search_by_hobby) \t$sql[]="`hobby`LIKE'$hobby%'"; if(is_array($sql)){ \t$sql_query=implode("AND",$sql); \t$result=$db->query("SELECT*FROM`table`WHERE$sql_query"); } Assuming the user entered something for each search field, I would think one index with containing each of those colums would work. But what if the user only entered something in the city field Would the index still work if we're now only searching by one of the colums when the index contains 3 colums
VIEWS ON THIS POST

87

Posted on:

Wednesday 24th October 2012
View Replies!

Will altering my indexes help with this query?

I'm doing a simply query but on a lot of data so it's taking quite a while. I have two indexes, but I'm wondering if I should change them into a multi column index to make a difference. Code: SELECT status , time FROM results WHERE id=$id AND ( time BETWEEN $on_after AND $before ); $on_after and $before are both unix timestamps. There are about 100 million records in the table right now. There are two indexes: one on "id", and one on "time". "id" is also a foreign key to another table. Is there really anything I can do to optimize this with this much data
VIEWS ON THIS POST

167

Posted on:

Wednesday 24th October 2012
View Replies!

PHPMyAdmin "Warning No index defined!"

This is a continuation of this thread . I think I have narrowed it down to the dB. I have a dB that is populated through uploading a csv file. I have it working on other sites but can't get it going on another. I feel the problem is in the dB and how I set it up. I created the dB and the only thing I see that could be wrong is that there is no index. The dB is for a calendar with the following fields: year, month, day1, day2...note1, note2, 35 fields in all. I am not sure what I need to do in PHPMyAdmin. "No index defined!" create an index on 1 columns GO. When I press "GO" it has a drop-down for type (Primary, Index, Unique, Fulltext) and below that it has field and size. I am unsure what I need to do here. Can anyone give me a clue
VIEWS ON THIS POST

54

Posted on:

Wednesday 24th October 2012
View Replies!

Difference between an index and a key

What is the distinction between an index and a key in terms of MySQL For example, MySQL limits the internal maximum key length to 1024 bytes, but I don't really know what that means. (Do they mean to say index where they say key) Thank you in advance.
VIEWS ON THIS POST

233

Posted on:

Wednesday 24th October 2012
View Replies!

MySQL: index for a very large table

This is the first time I try to use MySQL for very large tables. 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. Could anyone please advise whether MySQL really needs to store the entire index in memory Or is there some way to configure this
VIEWS ON THIS POST

86

Posted on:

Wednesday 24th October 2012
View Replies!

Getting the index to use it later

Good day, Let's I have a parent and a child tables. I load some data into the parent table, so a new record is generated with its primary key. As the private key is auto increment, I do not know the value of this index. Now, I need to load some data into the child table, where there is a foreing key linked to the parent table primary key. How can I get the value of the parent table primary key, in order to use it to load fata into the child table Parent table: "Customers" CustomerID
VIEWS ON THIS POST

134

Posted on:

Thursday 25th October 2012
View Replies!

Need help in table index

I have a table having around 2 million records i have tried to put proper index but still returning 7 rows when i explain query and an expert advice will be highly appreciated, here is the explain query result: id select_type table type possible_keys key key_len ref rows Extra: 1 SIMPLE table ref p_id,KF,_Fields _Fields 24 const,const,const,const,const,const 7 Using where; Using temporary; Using filesort explain SELECT * FROM table WHERE cat =16 AND s1 =112 AND s2 =0 AND s3 =0 AND s4 =0 AND p_id =102 GROUP BY field ORDER BY fc_od, f_od There is a composite index on cat, s1, s2, s3, s4, p_id named as _Fields
VIEWS ON THIS POST

102

Posted on:

Thursday 25th October 2012
View Replies!

Selecting one or more records by primary index

I'm trying to create a query that will extract one or more records from a database table. I've tried: Code: SELECT * FROM 'accommodations' WHERE 'accn_id' = 2007 OR 'accn_id' = 2024 which gives an empty result -- that's to say there's no error message, but no rows are returned, even though there ARE records with those accn_id values. I've also tried: Code: SELECT * FROM accommodations WHERE 'accn_id' = 2007 and that does give an error message, so presumably I've not got the syntax quite right I can't yet see what I've done wrong, but I hope I'm close.
VIEWS ON THIS POST

428

Posted on:

Thursday 25th October 2012
View Replies!

Slow performance query while using indexes

, i am having a problem with a aparently normal query, i am using indexes and it is still very slow, as the table grows biger and biger i am afraid the system cant hadle it... Here is the table data: CREATE TABLE `images` ( `id` int(11) unsigned NOT NULL auto_increment, `nome_imagem` varchar(36) NOT NULL default '', `last_seen` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `last_seen` (`last_seen`) ) ENGINE=InnoDB; Here is the query i need to execute: SELECT id, last_seen, nome_imagem FROM images WHERE ( last_seen =0 OR ( last_seen +5 ) < 1234610499
VIEWS ON THIS POST

201

Posted on:

Thursday 25th October 2012
View Replies!

Should I add an index to a TINYINT field with a small set of possible values?

I commonly use a field I call "active" on my database tables. It's a TINYINT(1) field that I store boolean values in. This field basically holds whether a row is ready to be used in production. When a row has outlived its usefulness, rather than deleting it it will be set to inactive, and active becomes 0. Consequentially, this field is used in almost every SELECT statement against the table. They will almost always check for active = 1 in the WHERE clause. Should this field be indexed Since it's used in the WHERE clause so often, it would make sense to index it, imo. But this active field is almost always set to 1 -- maybe 95 - 99% of the time. My understanding is having an index on a field that is almost always the same thing can actually slow the speed of the query down.
VIEWS ON THIS POST

119

Posted on:

Thursday 25th October 2012
View Replies!

Delete and re-index SQL

Out of interest -If I delete records from within the db are the records stored in a contiguous fashion or are there "gaps" in the table How do I go about deleting records from within a db and then RE-INDEX so that the records have new index id numbers Graham Durban
VIEWS ON THIS POST

85

Posted on:

Thursday 25th October 2012
View Replies!

indexing

Hi Everyone. Just wondering what is the normal amount of indexes on a table. I have a database with many tables although the tables fluctuate in the amount of fields they have within them. How many indexes would you suggest to apply to each table. At present I have added indexes to all tables which are frequently searched upon and or information is retrieved from as for other less used fields i never added indexes as i understand thats the point of indexes. Am I correct in how i have currently set these up as I am relatively new to the indexing feature.
VIEWS ON THIS POST

64

Posted on:

Thursday 25th October 2012
View Replies!

MySQL timing out? Really slow queries, already indexed.

For whatever reason, MySQL does not seem to be executing. So, I ran a few stat functions and here is what I got: Uptime: 220372 Threads: 39 Questions: 18748899 Slow queries: 808 Opens: 28723 Flush tables: 1 Open tables: 4096 Queries per second avg: 85.078 The "open tables" worries me for a few reasons. First, because it is 4kb (exactly, since it is divisible by 1024, evenly). This makes me think that maybe I am hitting a ceiling. Second, there are a few variables in MySQL that equal this, and maybe it's a cap on something. Here are the variables that equal 4096: query_cache_min_res_unit = 4096 table_cache = 4096 transaction_prealloc_size = 4096 Do any of those have anything to do with this Am I even close Are any of those values from mysql_stat a big deal
VIEWS ON THIS POST

106

Posted on:

Thursday 25th October 2012
View Replies!

best index for select with where/order/limit

This is an example of the table I am working on: Code: CREATE TABLE `db`.`messages` ( `unixtime` int( 10 ) NOT NULL default '0', `username` varchar( 20 ) NOT NULL default '', `sender` varchar( 20 ) NOT NULL default '', `content` mediumtext NOT NULL , KEY `username` ( `username` ) , KEY `unixtime` ( `unixtime` ) ) TYPE = MYISAM ; This is the kind of select that will be used a lot : Code: SELECT unixtime,poster,content FROM messages WHERE username='$name' ORDER BY unixtime DESC LIMIT $x,10 username has an index on it, because of the WHERE part. Unixtime has a time(); value in it wich I use to display the date, and because I also use it for ordering by date it also has an index. I dont think this current setup is wrong, but it could be improved, and I want to know if the following things are right : I could add an 'id' int (10) column, and set it to auto-increment and primary key and drop the key on the unixtime column. That way the messages will already be stored by date (new messages will always get a higher id value), and for mysql it will be easier to find the 10 posts that I want. I can not simply change 'unixtime' to a primary key, because on busy moments there is a real chance two users might send a message at the exact same second, resulting in a non unique value for that column. Code: SELECT unixtime,poster,content FROM messages WHERE username='$name' ORDER BY id DESC LIMIT $x,10 It will use the key of username to find all messages for this person, and it will then use the primary key to just select the 10 messages that are wanted. Those messages are already ordered by id (thus by time) when reading them so it will not need an extra sorting process unlike the ordering by unixtime wich probably requires all rows of that person to be read, and then all of them to be sorted to find the 10 that are wanted. So ordering by primary key would be faster and/or use less memory I could go even further and also drop the key on username, and instead have one primary key consisting of ('username','id') , with the id still an auto-incrementing INT. This way there is only one index that has to be loaded, wich can be used for both the WHERE and ORDER BY. The table would be like this then : Code: CREATE TABLE `db`.`messages2` ( `id` int( 10 ) NOT NULL AUTO_INCREMENT , `unixtime` int( 10 ) NOT NULL default '0', `username` varchar( 20 ) NOT NULL default '', `sender` varchar( 20 ) NOT NULL default '', `content` mediumtext NOT NULL , PRIMARY KEY ( `username` , `id` ) , ) TYPE = MYISAM ; I have done some testing , and used 'explain' too. With my first table it uses the username key and a filesort, on the second with the double primary key it reads a bit more rows, but its not using filesort anymore... so that should be an improvement I think it is, but would like some confirmation before I change it on dozen tables in the live database. And are there any other changes in index that would be even better
VIEWS ON THIS POST

137

Posted on:

Thursday 25th October 2012
View Replies!

how long would it take the production server to add new indexes?

Below is the table structure. How long would it take to add two new indexes to this table that has 200,000 records I know it partially depends on what else the server is doing and the type of machine but will it take hours or seconds is my basic question CREATE TABLE `purchase_list` ( `purchase_list_id` int(10) unsigned NOT NULL default '0', `purchase_id` int(10) default '0', `menu_id` int(10) unsigned NOT NULL default '0', `purchase_list_price` float NOT NULL default '0', `purchase_list_specific` varchar(128) default NULL, `purchase_list_name` varchar(255) NOT NULL default '', `user_id` int(10) default NULL, `old_purchase_id` int(10) default NULL, `purchase_mod_date` datetime default NULL, PRIMARY KEY (`purchase_list_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
VIEWS ON THIS POST

152

Posted on:

Thursday 25th October 2012
View Replies!

pagination w/1 query + how to use query with indexes

i'm asking 2 questions in 1 thread because i don't wanna take up too much room, hopefully no one will mind. i have mysql 4.1.10 1) i want to find all the rows that were edited this month. the query i currently have ( MONTH(CURDATE()) = MONTH(date) ) doesn't use indexes. how can i manipulate it so i can take advantage of indexes. 2) this is something i've always wondered, but usually just assumed was not possible. if i am listing some results, say 20 per page, how can i get both the total number of results as well as the 20 items required for that specific page. say there are 2 million total results, so grabbing them all and showing just 20 is not an option. if this is not possible what is the most efficient way of making both queries
VIEWS ON THIS POST

129

Posted on:

Thursday 25th October 2012
View Replies!

Using "WHERE LIKE '%Word'" that *will* use an index... Works but feels stupid!

FYI, I'm using INNODB. Let's say I have a table with a city column containing 30,000 records, and I want to provide a search form 'starts with' and 'ends with fields. So a user would supply the following: Starts with: Cha Ends with: tte And the search would find a record such as 'Charlotte' The 'starts with' search can use an index (eg, LIKE 'Cha%'), but ends with can't (eg, LIKE '%tte'). A solution seems to be to create a city_reverse column, add a unique index on that, then perform an 'ends with' search on the city names in reverse. For instance, the 'Charlotte' record would be stored in the city_reverse column as such: 'ettolrahC' ...and I'd perform an 'ends with' search using the unique index: LIKE 'ett%' The above works, but it feels stupid, and adds weight to the database. Is there an alternative
VIEWS ON THIS POST

97

Posted on:

Thursday 25th October 2012
View Replies!

Cardinality is NULL on Merge Table's indexes

I have one merge table comprised of two child tables. The index cardinality on the child tables is correct but the cardinality for most of the indexes on the merge table report NULL. I have run analyze table on the child tables (can't run analyze on the merge table, "The storage engine for the table doesn't support analyze") and dropped/recreated all the indexes on the merge and child tables, but the cardinality on the merge table stubbornly remains NULL. MySQL version 5.1.48. These are my tables: mattleech. com/bkwk.html Any help would be appreciated Merge table Table......Non_unique..Key_name..................Seq_in_index...Column_name....Collation....Cardinal ity....Index_type Merge_Table....0....idx_Delivery_Item_Plnt.............1.........Delivery..........A.............NUL L..........BTREE Merge_Table....0....idx_Delivery_Item_Plnt.............2.........Item..............A.............NUL L..........BTREE Merge_Table....0....idx_Delivery_Item_Plnt.............3.........Plnt..............A.............NUL L..........BTREE Merge_Table....1....idx_SalesDoc.......................1.........SalesDoc..........A.........2136736 8..........BTREE Merge_Table....1....idx_Createdon......................1.........Createdon.........A.............548 6..........BTREE Merge_Table....1....idx_Route_Createdon_DlvTy..........1.........Route.............A..............94 7..........BTREE Merge_Table....1....idx_Route_Createdon_DlvTy..........2.........Createdon.........A...........16187 4..........BTREE Merge_Table....1....idx_Route_Createdon_DlvTy..........3.........DlvTy.............A.............NUL L..........BTREE Merge_Table....1....idx_Soldtopt_Createdon_ShipToPt....1.........Soldtopt..........A.............NUL L..........BTREE Merge_Table....1....idx_Soldtopt_Createdon_ShipToPt....2.........Createdon.........A.............NUL L..........BTREE Merge_Table....1....idx_Soldtopt_Createdon_ShipToPt....3.........ShipToPt..........A.............NUL L..........BTREE Merge_Table....1....idx_Material.......................1.........Material..........A.............NUL L..........BTREE Child table 1 Table......Non_unique..Key_name..................Seq_in_index...Column_name....Collation....Cardinal ity....Index_type Child_Table_1..0....idx_Delivery_Item_Plnt.............1.........Delivery..........A..........943234 8..........BTREE Child_Table_1..0....idx_Delivery_Item_Plnt.............2.........Item..............A..........943234 8..........BTREE Child_Table_1..0....idx_Delivery_Item_Plnt.............3.........Plnt..............A..........943234 8..........BTREE Child_Table_1..1....idx_SalesDoc.......................1.........SalesDoc..........A..........471617 4..........BTREE Child_Table_1..1....idx_Createdon......................1.........Createdon.........A............5389 9..........BTREE Child_Table_1..1....idx_Route_Createdon_DlvTy..........1.........Route.............A............5389 9..........BTREE Child_Table_1..1....idx_Route_Createdon_DlvTy..........2.........Createdon.........A............6596 0..........BTREE Child_Table_1..1....idx_Route_Createdon_DlvTy..........3.........DlvTy.............A............6689 6..........BTREE Child_Table_1..1....idx_Soldtopt_Createdon_ShipToPt....1.........Soldtopt..........A.............664 2..........BTREE Child_Table_1..1....idx_Soldtopt_Createdon_ShipToPt....2.........Createdon.........A...........31441 1..........BTREE Child_Table_1..1....idx_Soldtopt_Createdon_ShipToPt....3.........ShipToPt..........A...........37729 3..........BTREE Child_Table_1..1....idx_Material.......................1.........Material..........A............6835 0..........BTREE Child table 2 Table......Non_unique..Key_name..................Seq_in_index...Column_name....Collation....Cardinal ity....Index_type Child_Table_2..0....idx_Delivery_Item_Plnt.............1.........Delivery..........A.............NUL L..........BTREE Child_Table_2..0....idx_Delivery_Item_Plnt.............2.........Item..............A.............NUL L..........BTREE Child_Table_2..0....idx_Delivery_Item_Plnt.............3.........Plnt..............A.............NUL L..........BTREE Child_Table_2..1....idx_SalesDoc.......................1.........SalesDoc..........A.........1665119 4..........BTREE Child_Table_2..1....idx_Createdon......................1.........Createdon.........A.............216 1..........BTREE Child_Table_2..1....idx_Route_Createdon_DlvTy..........1.........Route.............A..............37 0..........BTREE Child_Table_2..1....idx_Route_Createdon_DlvTy..........2.........Createdon.........A............8627 5..........BTREE Child_Table_2..1....idx_Route_Createdon_DlvTy..........3.........DlvTy.............A............9225 0..........BTREE Child_Table_2..1....idx_Soldtopt_Createdon_ShipToPt....1.........Soldtopt..........A............1598 0..........BTREE Child_Table_2..1....idx_Soldtopt_Createdon_ShipToPt....2.........Createdon.........A..........158582 8..........BTREE Child_Table_2..1....idx_Soldtopt_Createdon_ShipToPt....3.........ShipToPt..........A..........185013 2..........BTREE Child_Table_2..1....idx_Material.......................1.........Material..........A............4210 1..........BTREE
VIEWS ON THIS POST

147

Posted on:

Monday 29th October 2012
View Replies!

Speeding up MySQL query using index?

I noticed that my SELECT query becomes slower and slower the more records my table has (it takes as long as 10 seconds for just 40,000 records, slower if more records). This affects the performance. As I understand, a properly created index maybe able to help speed up my query (pls correct me if I'm wrong). Here's an example of the query i use: Code: (select * from log where (((to_days(now())) - (to_days(rectime))
VIEWS ON THIS POST

76

Posted on:

Monday 29th October 2012
View Replies!