Tagged Questions
0
votes
1answer
14 views
Use timestamp(or datetime) as part of primary key (or part of clustered index)
I use following query frequently:
SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp
I would like to optimize ...
0
votes
1answer
22 views
optimizing a MySQL query
I have a MySQL database with 3 tables
Keywords
id, keyword, projects_id
Year
results_id*, jan, feb, mar, ..., nov, dec
Results
id, keywords_id*, country, user
star = foreign key
I need to find ...
0
votes
2answers
31 views
MySQL query to find items without matching record in JOIN is very slow
I've read a lot of questions about query optimization but none have helped me with this.
As setup, I have 3 tables that represent an "entry" that can have zero or more "categories".
> show ...
0
votes
1answer
31 views
MySQL: comparison of integer value and string field with index
Table a_table has index on string_column.
I have a query:
SELECT * FROM a_table WHERE string_column = 10;
I used EXPLAIN to find that no indexes are used.
Why? Could you help me with MySQL ...
0
votes
0answers
19 views
Tips for mysql tuning
My mysql server was running too slow. After doing preliminary investigation using top command I found that MySQL was using 200% of cpu which led me to following answer .
After running SHOW FULL ...
3
votes
0answers
33 views
How to design indexes for columns with NULL values in MySQL? [migrated]
I have a database with 40 million entries and want to run queries with the following WHERE clause
...
WHERE
`POP1` IS NOT NULL
&& `VT`='ABC'
&& (`SOURCE`='HOME')
&& ...
1
vote
1answer
21 views
Why cardinality value in mysql indexes don't equal distinct count for column values
Not a long ago I started to optimize queries for mysql db. I created some indexes and decided to see their params, using show index from syntax. And saw that cardinality doesn't equal distinct count ...
1
vote
1answer
38 views
Need Help to Optimize Query
Can someone please help me optimize following query? Its to search for a search term and result data which will be used in an auto complete form. Data will be sent as JSON (probably) or HTML (). But ...
1
vote
1answer
45 views
Does Database Indexing play any important role in Parent Child table?
Look at this 1st scenario, you got a table that has 2 columns- Parent (P) & Child (C).
P-C
1-3
2-8
3-6
6-4
8-7
When users search for all descendants of "1" then it will show:
P-C
1-3
3-6
6-4
...
0
votes
2answers
38 views
Optimizing mysql Join query with date sorting
Simplifying the database/table structure i have a situation with two tables where we store 'items' and item properties (the relation between the two is 1-N)
I'm trying to optimize the following ...
3
votes
5answers
76 views
best practice to count record in MySQL + PHP [duplicate]
I want to check weather number of record > 15 for a condition.
What is best practice to speed up the query?
1)
$query="SELECT `id` FROM `table` WHERE `name`='$name' AND `usage` > '$limit'";
...
1
vote
1answer
43 views
understanding perf of mysql query using explain extended
I am trying to understand performance of an SQL query using MySQL.
With only indexes on the PK, the query failed to complete in over 10mins.
I have added indexes on all the columns used in the where ...
0
votes
0answers
51 views
Optimizing MySQL ORDER BY field of JOINed table
I have the following structure:
CREATE TABLE `tbl1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tid` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`time` datetime NOT NULL,
`count` int(11) NOT ...
6
votes
4answers
150 views
Tagging query with group_concat
Using the database schema for tagging from this question's accepted answer is it possible to have a query using group_concat that works with a large amount of data? I need to get items with their tags ...
-1
votes
0answers
45 views
Optimize a mysql query on a table with 500 million rows
I have this specific mysql query:
select extract(month from cs.logindate - ra.creationdate), count(distinct ra.id)
from ra
inner join rp on ra.id = rp.raccountid
left join cs on rp.raccountid = ...
