Tagged Questions
4
votes
1answer
87 views
Does the order of fields in SELECT query matter when using composite indexing?
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I ...
2
votes
1answer
631 views
MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?
I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The ...
3
votes
1answer
1k views
how to fetch different data on compare rows of two tables
I have two tables in my database, one is user_app table in which user info are stored, table are as:
+--------------+--------------+------+-----+-------------------+-----------------------------+
| ...
0
votes
2answers
77 views
SQL and Multiple Indexes
Consider the following table:
i | a | b | c
--------------
0 | 1 | 2 | 3
1 | 4 | 5 | 6
2 | 7 | 8 | 9
3 | x | y | z
The table, in the end, will have several thousand rows, and even more columns then ...
2
votes
2answers
66 views
Are these extra Index fields needed?
I've designed a table as below for users who register a quiz:
CREATE TABLE `registered_quiz` (
`rq_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`user_id` smallint(5) unsigned NOT NULL,
...
2
votes
1answer
170 views
select for update gives error on indexed column
here is my code:
mysql_query("start transaction");
$q="select max(id) from test for update";
$r=mysql_query($q);
$row=mysql_fetch_array($r);
for ($k=0;$k<100000;$k++)
{
$q="insert into test values ...
2
votes
2answers
237 views
How do redundant indexes impact query performance?
The indexes are affecting the ADD/UPDATE operations however I was wondering whether the redundant indexes will affect the SELECT query performance or not?
How the redundant indexes are handled by ...
5
votes
3answers
1k views
What is the default order of records for a SELECT statement in MySQL?
Suppose you have the following table and data:
create table t(k int, v int,index k(k)) engine=memory;
insert into t values(10, 1), (10, 2), (10, 3);
When issuing select * from t where k = 10 with ...
