Questions tagged [database-optimization]
186 questions
4
votes
2 answers
MySQL Indexes creation
I have a table with 10 columns and I need to support combined range filters for most of them.
Let's say for example:
WHERE column_a >= a_min AND column_a <= a_max
AND column_b >= b_min AND column_b <= b_max
...
But that is not all, I need also to…
sebasuy
- 484
- 2
- 17
4
votes
1 answer
ORDER BY NULL slower than ORDER BY column
according to MySQL documentation, adding ORDER BY NULL after GROUP BY should "avoid the overhead of sorting the result". If so, why is following query aprox 5 times slower
SELECT COUNT(*) advert_count, category.name
FROM advert
LEFT JOIN category ON…
Petr Peller
- 8,039
- 9
- 47
- 64
4
votes
2 answers
How to manage Huge operations on MySql
I have a MySql DataBase. I have a lot of records (about 4,000,000,000 rows) and I want to process them in order to reduce them(reduce to about 1,000,000,000 Rows).
Assume I have following tables:
table RawData: I have more than 5000 rows per sec…
Behrouz.M
- 2,987
- 5
- 33
- 61
4
votes
1 answer
What does SQL Server Execution Times represent?
I have a query that I'm running on two equivalent databases, but hosted on separate MS SQL 2005 servers. I want to measure the time of a query on both servers, and thus tried the following:
SET STATISTICS TIME ON
GO
SELECT TOP 10000 *
FROM…
Tomas Vinter
- 2,610
- 7
- 34
- 45
4
votes
1 answer
Faster LIKE '%XXX'
In MySQL conducting a search like:
SELECT * FROM people WHERE surname LIKE '%ton';
Are slow, even when the column subject to the LIKE is indexed. Are there any techniques for speeding this up?
The best idea I can think of is:
Create a new column,…
Kohjah Breese
- 3,619
- 5
- 29
- 43
4
votes
4 answers
Regular performance tuning and maintenance
How often do you conduct regular maintenance such as stress test your application and/or tune your database indexes for your applications?
E.G., Do you tune (defrag, reorganise or rebuild) your database indexes once a week, every six months or only…
Kane
- 15,657
- 11
- 56
- 82
4
votes
1 answer
Performance issue full index scan in mysql database
I have a database with a table called QuizMatches. The table has the following structure:
CREATE TABLE `QuizMatches` (
`QuizMatchesGuid` binary(16) NOT NULL,
`DateStarted` datetime NOT NULL,
`LatestChanged` datetime NOT NULL,
…
www.jensolsson.se
- 2,861
- 2
- 30
- 58
4
votes
1 answer
Getting multiple tables with single db call in entity framework
I am new to ASP.NET MVC and Entity framework. Previously I used to work with ADO.NET Datatables and DataSets. So in scenarios like getting multiple tables from database in single call, I used to use DataSet. e.g.
ALTER PROCEDURE…
Usman Khalid
- 2,872
- 8
- 33
- 63
3
votes
3 answers
Advantages of having user authentication details stored in a separate table
I have a user table in mysql containing all user data (firstname, surname, address, etc)
But should I store the authentication details in another table (username, password) and link the two tables via a user ID?
Is there much point in this?
Is it…
AlexMorley-Finch
- 6,144
- 15
- 62
- 100
3
votes
5 answers
Best way to count rows from mysql database
After facing a slow loading time issue with a mysql query, I'm now looking the best way to count rows numbers. I have stupidly used mysql_num_rows() function to do this and now realized its a worst way to do this.
I was actually making a Pagination…
Aajahid
- 1,539
- 2
- 12
- 33
3
votes
2 answers
Advice needed: SQL Server DB Architecture for Large Database
HI all!
My client currently has a SQL Server database that performs 3-4 Million Inserts, about as many updates and even more reads a day, every day. Current DB is laid out weirdly IMHO: The incoming data goes to "Current" table, then nightly records…
Dimitri
- 6,595
- 4
- 31
- 49
3
votes
4 answers
Which of the following SQL queries would be faster? A join on two tables or successive queries?
I have two tables here:
ITEMS
ID| DETAILS| .....| OWNER
USERS:
ID| NAME|....
Where ITEMS.OWNER = USERS.ID
I'm listing the items out with their respective owners names. For this I could use a join on both tables or I could select all the ITEMS and…
Ali
- 6,940
- 20
- 97
- 155
3
votes
2 answers
Does a database column that has numbers need to be indexed?
Suppose Bob has earned 107 points, Mary 105 points and John also 105 points. These numbers could change and are not unique.
Now I want to query and check the order, who is on top and who is on the bottom. Does that column that holds points need to…
user1861388
- 85
- 6
3
votes
1 answer
Matching descriptor to database of descriptors
I'm trying to find a fast way to match descriptors from a database. My program works the following way:
1) Populates a database with descriptors of images (using proper feature detection algorithms)
2) Load an image
3) Extracts descriptor for that…
3
votes
2 answers
Optimising MySQL queries with heavy joins
I currently run a site which tracks up-to-the-minute scores and ratings in a list. The list has thousands of entries that are updated frequently, and the list should be sortable by these score and ratings columns.
My SQL for getting this data…
Ryall
- 11,272
- 11
- 47
- 74