Skip to main content
11 votes
Accepted

How to create recurrent weekdays as columns in a pivot?

The most complex part of this is just building the calendar in that format. Pivoting and surrounding it with HTML is pretty easy. First, let's start with this, your employee table with leave dates. ...
Aaron Bertrand's user avatar
9 votes
Accepted

MySQL: Why not create an index on every column? What is the cost of indexing a column?

An index literally stores a copy of the data, sorted on the fields covered by that index (and automatically includes the clustered index key), generally in a data structure called a B-Tree. Creating ...
J.D.'s user avatar
  • 38.3k
9 votes

What does it mean that PostgreSQL "offers" PHP?

Language extensions allow you to write user-defined functions in additional programming languages. Most people are aware of SQL and PL/pgSQL, but Postgres also ships with extensions for Perl, Python, ...
dwhitemv's user avatar
  • 1,567
6 votes
Accepted

Timeout error after SQL Server upgrade from 2008R2 to SQL Server 2014

I finally find out the problem. After days of straggling I checked the SQL Server report and noticed that there were very large occasions of autogrowth on temp db database. The cause of the problem ...
Hailegziabher Dechassa's user avatar
6 votes

Timeout error after SQL Server upgrade from 2008R2 to SQL Server 2014

The Cardinality Estimation logic was updated for SQL Server 2014 and could potentially be a reason. You would have to test the same queries with the old cardinality estimator and compare performance ...
Andy Jones's user avatar
  • 1,381
6 votes

Searching a text with space in mySql match against Query

FULLTEXT needs space to be space, not a "letter". That is, you cannot have spaces in a "word". I would write application code to ignore 'short' words (such as 'g') and end up with WHERE MATCH(...
Rick James's user avatar
  • 78.6k
6 votes
Accepted

Disadvantage of Changing database schema - Insert or delete new columns/tables in middle

The foundation of your system is your database. If you change your database schema then you also have to change your code. That means that keeping a stable data model helps to reduce the amount of ...
Joel Brown's user avatar
  • 12.5k
6 votes
Accepted

What happens if a delete query times out SQL Server for php?

When a timeout occurs, the client API sends an attention signal to SQL Server to cancel the executing query. Upon receipt, SQL Server cancels the query and rolls back the transaction. Data ...
Dan Guzman's user avatar
  • 28.3k
6 votes
Accepted

How to update "sorting_order" column of other rows when changing one?

Add new category : $t_cat = 'jeans';//new category to insert $so_to_set = 2;// so for new category First update all so greater than or equal to $so_to_set $query = "update categaries ...
DEarTh's user avatar
  • 757
6 votes
Accepted

Should I use VARCHAR or DATETIME?

You should always store dates using the DATE type. It should use less storage and will generally be faster when searching than if you stored the same date as a VARCHAR. Additionally, if you stored ...
Mr.Brownstone's user avatar
6 votes
Accepted

Entering Exact Values into a Table Using SQL

Just do not use string interpolation or concatenation to get values into SQL queries in PHP. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized ...
sticky bit's user avatar
  • 4,904
6 votes
Accepted

Is it secure to have database login in a PHP file? Other options?

What I have done for PHP projects is to define the database credentials (or any other credentials, for example web services) in files outside the http document root. In fact, most of your code can and ...
Bill Karwin's user avatar
  • 15.2k
5 votes

Cassandra "Operation timed out - received only 0 responses." during write

You will need to increase commitlog_segment_size_in_mb to a higher value. Try 64MB instead. It increases the size of the commit record segment. This property determines the maximum mutation size (...
3manuek's user avatar
  • 1,449
5 votes
Accepted

How to select records where NOW is between two dates in fields?

You can use the NOW() function in mysql. Note, this assumes that termDate is the later date: Select content, image, imageHeight, imageWidth, link, linkDescription From news Where appearanceDate &...
mendosi's user avatar
  • 2,087
5 votes
Accepted

Mysql select rows with 7 continuous dates

MySQL 8 MySQL 8 provides window functions... SELECT item_id FROM ( SELECT item_id, date, count(coalesce(diff, 1)=1 OR null) OVER (PARTITION BY item_id ORDER BY date) seq FROM ( ...
Evan Carroll's user avatar
  • 63.9k
5 votes

Order by numbers followed by alphabet and then null values

select * from t order by case when col regexp '^[0-9]' then 1 when col regexp '^[a-zA-Z]' then 2 when col = '' or col is null then 3 end , col * 1 /*this converts to number, so that 100 is not sorted ...
tombom's user avatar
  • 3,158
5 votes

Why is ON DUPLICATE KEY not working as expected?

Any idea why this query doesn't work with table B? Do you have to insert/update every table column when running the query? Yes, UserName is a NOT NULL column and doesn't have a DEFAULT value. ...
ypercubeᵀᴹ's user avatar
5 votes
Accepted

MySQL Query Failed: Expression #6 of SELECT list is not in GROUP BY clause and contains non aggregated column fixed bug reappear every restart

That's not a bug. That's 'move to standard'. Do not fix this problem by change server's SQL Mode. Fix it by altering the queries which uses partial GROUP BY expressions. Details can be read in ...
Akina's user avatar
  • 20.1k
5 votes

Does this kind of data/field in this table needs normalization in MYSQL?

I would vote yes to normalize it. Units of measure, i.e. a UOM column, should literally be units of measure, e.g. "meters", "yards", "pounds", "tons" etc. I don'...
J.D.'s user avatar
  • 38.3k
5 votes
Accepted

Converting large database with many tables from latin1 to utf8mb4

At my last job we used pt-online-schema-change for such changes, or any other ALTER TABLE changes, hundreds of times per week on tables much larger than yours. I worked on an internal service and ...
Bill Karwin's user avatar
  • 15.2k
5 votes
Accepted

Delete records in different tables

You can't ordinarily delete from more than one table in a single T-SQL DELETE statement. It is possible to achieve the same effect using triggers or cascading foreign keys with an ON DELETE CASCADE ...
Paul White's user avatar
  • 86.9k
4 votes
Accepted

High CPU Usage for single query

I would recommend adding indexes on the columns participating in the JOIN conditions and the WHERE clauses. Indexes are designed to help the query optimizer make better choices. Unless you enforce ...
Randolph West's user avatar
4 votes

How to speed up mysql query using cache

(1) Consider designing the schema so that you join on users.id, not users.email. That will lead to a better way to formulate the query -- by computing the aggregates before joining to users. (2) Don'...
Rick James's user avatar
  • 78.6k
4 votes

Create table if not exist each time API called: bad practice?

So let's think forward a little. The application goes live, the tables are created and everything's lovely. Then, you have a new requirement and the tables must change. How do you code that? There's ...
Michael Green's user avatar
4 votes

Create table if not exist each time API called: bad practice?

I was searching for overhead info the same reasons. There is probably no overhead because the table is usually cached and there will be no disk access. The bottleneck of databases is usually disk ...
NickSoft's user avatar
  • 141
4 votes
Accepted

How to join 2 tables in mysql but with different values in one of the tables

You can use LEFT JOIN instead of JOIN. With LEFT JOIN the query will return all records from the left table i.e. the one in the FROM, even if there is no matching record in the joint table. SELECT * ...
FloT's user avatar
  • 356
3 votes

MySQL PDO Cannot assign requested address

Using a persistent connection worked for me. The linked recommondation above shows how to create a persistent connection with mysqli. I am using PDO, too. So I had to add the following to my ...
n.r.'s user avatar
  • 131
3 votes

I am trying to run this code and I get an error at line 11

CREATE TABLE IF NOT EXISTS `tblproduct` ( `id` int(8) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `code` varchar(255) NOT NULL, `image` text NOT NULL, `price` double(10,2) NOT ...
Vérace's user avatar
  • 30.2k
3 votes
Accepted

What's the advantage of the inet datatype in Cassandra vs a plain text string?

It's a special datatype for storing ip addresses. It would be more compact that the string representation of the same information as well as type safe. inet An IP address. It can be either 4 bytes ...
RussS's user avatar
  • 246
3 votes
Accepted

Slow search by LIKE and other values (1 million rows+)

Not sure about mysql but in mssql like '%val' is a full scan Multiple keywords in a varchar(200) column is probably not the best design. Spit the keyswords into separate words into a separate table ...
paparazzo's user avatar
  • 5,065

Only top scored, non community-wiki answers of a minimum length are eligible